Schema Design with Supabase: Partitioning and Normalization

Source: Dev.to

Day 6 – Schema Design with Supabase

PostgreSQL supports schemas, which act as namespaces for grouping tables.
Typical uses include:

• Multi‑tenancy – separate schemas per customer for full data isolation.
• Access control – set permissions per schema.
• Extension isolation – keep extensions (e.g., pgvector) in dedicated schemas.

In many projects all tables live in the default public schema:

-- Default is public schema
SELECT * FROM public.users;

-- Create a separate schema
CREATE SCHEMA app_auth;
SELECT * FROM app_auth.users;

Problems with a single public schema

• Visibility drops as the number of tables grows.
• It becomes hard to tell which table belongs to which feature.
• Permission management gets complicated.

To address this, I split schemas by feature and added an app_ prefix.

The app_ Prefix

Supabase reserves system schemas such as auth, storage, and public.
By prefixing custom schemas with app_ (short for application), they:

• Are clearly distinguished from Supabase system schemas.
• Appear first alphabetically in tools like pgAdmin, making them easy to spot.

Supabase system schemas

├── auth        # Supabase authentication
├── storage     # Supabase storage
├── public      # Default

Application schemas

├── app_auth      # Custom authentication
├── app_billing   # Billing
├── app_content   # Content management
├── app_admin     # Admin functions
├── app_ai        # AI features
├── app_social    # Social features
├── app_system    # System logs, etc.

I avoid Supabase’s system schemas altogether, keeping everything in custom schemas to reduce vendor lock‑in. For small services the overhead of many schemas may not be worth it, but it pays off as a SaaS grows.

Current Schema Overview

Partitioning criteria

1. Feature cohesion – keep related tables together (e.g., app_auth).
2. Change frequency – isolate rapidly changing tables.
3. Permission boundaries – separate admin‑only data (app_admin) from general user data (app_content).

Defining Schemas with Drizzle ORM

// database/app_auth/schema.ts
import { pgSchema } from 'drizzle-orm/pg-core';

export const appAuth = pgSchema('app_auth');

// database/app_auth/users.ts
import { appAuth } from './schema';
import { text, timestamp, uuid } from 'drizzle-orm/pg-core';

export const users = appAuth.table('users', {
  id: uuid('id').primaryKey().defaultRandom(),
  name: text('name').notNull(),
  email: text('email').notNull(),
  createdAt: timestamp('created_at').defaultNow(),
  updatedAt: timestamp('updated_at').defaultNow(),
});

Directory layout matching schemas

src/database/
├── app_auth/
│   ├── schema.ts      # Schema definition
│   ├── users.ts       # Table definition
│   ├── sessions.ts
│   └── index.ts       # Exports
├── app_billing/
│   ├── schema.ts
│   ├── subscriptions.ts
│   └── index.ts
├── index.ts           # Aggregate exports
└── relations.ts       # Relation definitions

Normalization & Denormalization

Even in indie projects, basic normalization (1NF‑3NF) is valuable:

• 1NF – Eliminate repeating groups; use junction tables instead of CSV fields.
• 2NF – Remove partial dependencies; separate columns that depend only on part of a composite key.
• 3NF – Remove transitive dependencies; store derived values only when needed.

Practical tips

• Junction tables – handle many‑to‑many relationships (e.g., content_tags).
• Composite primary keys – (tenant_id, id) for multi‑tenant tables.
• Denormalization – store aggregate counts (e.g., bookmark count) for read‑heavy queries.

Row‑Level Security (RLS)

RLS provides per‑row access control. While I currently enforce permissions in the application layer, a partitioned schema layout simplifies future RLS policies because each schema can have its own set of policies.

Using Custom Schemas with Supabase

When you add custom schemas, you must configure three things; otherwise you’ll encounter “table not found” errors.

1. Expose schemas in the Supabase dashboard

Project Settings → Data API → Exposed schemas → add your schemas (e.g., public, app_admin, app_ai, …).

2. Include schemas in DATABASE_URL

# .env.local
DATABASE_URL=postgresql://user:password@host:5432/postgres?schema=public,app_admin,app_ai,app_auth,app_billing,app_content,app_social,app_system

3. Configure Drizzle ORM’s schemaFilter

// drizzle.config.ts
import type { Config } from 'drizzle-orm';

export default {
  schema: [
    './src/database/app_admin/index.ts',
    './src/database/app_auth/index.ts',
    './src/database/app_billing/index.ts',
    // …
  ],
  dialect: 'postgresql',
  dbCredentials: {
    url: process.env.DATABASE_URL!,
  },
  // Multiple schema support
  schemaFilter: [
    'public',
    'app_admin',
    'app_ai',
    'app_auth',
    'app_billing',
    'app_content',
    'app_social',
    'app_system',
  ],
} satisfies Config;

Takeaways

What works well

• Clear responsibility separation by feature‑based schemas.
• Directory structure mirrors schema names, improving readability.
• Basic normalization with selective denormalization for performance.

Cautions

• Too many schemas can add complexity.
• Remember to expose custom schemas in Supabase settings.
• Even small projects benefit from thoughtful schema design for future scalability.

What’s next

Tomorrow’s post will cover Database ID Design – choosing between UUID, CUID2, sequential IDs, and more.

Related posts in this series

• 12/5 – Git Branch Strategy: A Practical Workflow for Indie Development
• 12/7 – Database ID Design: Choosing Between UUID, CUID2, Sequential IDs, and More