Common Schema Patterns for Web Applications

Why Schema Patterns Matter

Every web application eventually needs to solve the same set of data modeling problems: how to store users and permissions, how to track changes, how to implement tagging, how to handle soft deletes. These are solved problems, and using proven patterns saves weeks of trial-and-error. This guide covers the patterns you will reach for most often, with SQL examples you can paste directly into our Schema Designer or adapt to your codebase.

Users and Authentication

The most fundamental pattern in any web application. Separate the authentication concern (credentials) from the identity concern (profile). This allows you to add OAuth providers, magic links, or passkeys without restructuring the user table.

CREATE TABLE users (
    id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    email       VARCHAR(255) NOT NULL UNIQUE,
    name        VARCHAR(255),
    avatar_url  TEXT,
    created_at  TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at  TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE credentials (
    user_id        UUID PRIMARY KEY REFERENCES users(id) ON DELETE CASCADE,
    password_hash  VARCHAR(255) NOT NULL,
    last_login     TIMESTAMPTZ
);

CREATE TABLE oauth_accounts (
    id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id     UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    provider    VARCHAR(50) NOT NULL,   -- 'google', 'github', 'azure'
    provider_id VARCHAR(255) NOT NULL,
    UNIQUE (provider, provider_id)
);

This separation means adding a new auth method is just adding a new table, not modifying the users table. The credentials table uses a 1:1 relationship with users, while oauth_accounts is 1:N because a user may link multiple providers.

Role-Based Access Control (RBAC)

RBAC assigns permissions to roles, then assigns roles to users. This is more maintainable than assigning permissions directly to users because changing a role's permissions automatically updates all users with that role.

CREATE TABLE roles (
    id    UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name  VARCHAR(100) NOT NULL UNIQUE,
    description TEXT
);

CREATE TABLE permissions (
    id       UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    action   VARCHAR(100) NOT NULL,   -- 'read', 'write', 'delete'
    resource VARCHAR(100) NOT NULL,   -- 'posts', 'users', 'settings'
    UNIQUE (action, resource)
);

CREATE TABLE role_permissions (
    role_id       UUID REFERENCES roles(id) ON DELETE CASCADE,
    permission_id UUID REFERENCES permissions(id) ON DELETE CASCADE,
    PRIMARY KEY (role_id, permission_id)
);

CREATE TABLE user_roles (
    user_id UUID REFERENCES users(id) ON DELETE CASCADE,
    role_id UUID REFERENCES roles(id) ON DELETE CASCADE,
    PRIMARY KEY (user_id, role_id)
);

Authorization checks become a single query: "Does this user have a role that includes the permission (action=write, resource=posts)?"

Multi-Tenancy

Multi-tenant applications serve multiple organizations from a single database. The simplest and most common approach is a shared database with a tenant_id column on every tenant-scoped table. Row-level security (RLS) in PostgreSQL can enforce tenant isolation at the database level.

CREATE TABLE tenants (
    id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name        VARCHAR(255) NOT NULL,
    slug        VARCHAR(100) NOT NULL UNIQUE,
    plan        VARCHAR(50) NOT NULL DEFAULT 'free',
    created_at  TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE tenant_users (
    tenant_id UUID REFERENCES tenants(id) ON DELETE CASCADE,
    user_id   UUID REFERENCES users(id) ON DELETE CASCADE,
    role      VARCHAR(50) NOT NULL DEFAULT 'member',
    PRIMARY KEY (tenant_id, user_id)
);

-- Every tenant-scoped table includes tenant_id
CREATE TABLE projects (
    id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id   UUID NOT NULL REFERENCES tenants(id) ON DELETE CASCADE,
    name        VARCHAR(255) NOT NULL,
    created_at  TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- PostgreSQL RLS example
ALTER TABLE projects ENABLE ROW LEVEL SECURITY;
CREATE POLICY tenant_isolation ON projects
    USING (tenant_id = current_setting('app.current_tenant')::uuid);

Audit Logging

Audit logs track who changed what and when. This is essential for compliance, debugging, and undo functionality. The pattern uses an append-only table that records the entity, the action, the user, and a JSON snapshot of the changes.

CREATE TABLE audit_logs (
    id          BIGSERIAL PRIMARY KEY,
    entity_type VARCHAR(100) NOT NULL,   -- 'user', 'project', 'document'
    entity_id   UUID NOT NULL,
    action      VARCHAR(50) NOT NULL,    -- 'create', 'update', 'delete'
    actor_id    UUID REFERENCES users(id),
    changes     JSONB,                   -- { "name": { "old": "A", "new": "B" } }
    metadata    JSONB,                   -- IP address, user agent, etc.
    created_at  TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- Index for querying by entity
CREATE INDEX idx_audit_entity ON audit_logs (entity_type, entity_id);
-- Index for querying by actor
CREATE INDEX idx_audit_actor ON audit_logs (actor_id, created_at DESC);

The changes column stores a diff of the old and new values as JSON. This is more flexible than storing the full row snapshot, especially for tables with many columns.

Tagging (Many-to-Many)

Tags are the canonical many-to-many relationship. A post can have many tags, and a tag can apply to many posts. The junction table post_tags connects them.

CREATE TABLE tags (
    id    UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name  VARCHAR(100) NOT NULL UNIQUE,
    slug  VARCHAR(100) NOT NULL UNIQUE,
    color VARCHAR(7)   -- hex color like '#ef4444'
);

CREATE TABLE post_tags (
    post_id UUID REFERENCES posts(id) ON DELETE CASCADE,
    tag_id  UUID REFERENCES tags(id) ON DELETE CASCADE,
    PRIMARY KEY (post_id, tag_id)
);

-- Query: find all posts with a specific tag
-- SELECT p.* FROM posts p
--   JOIN post_tags pt ON p.id = pt.post_id
--   JOIN tags t ON t.id = pt.tag_id
--   WHERE t.slug = 'javascript';

To make tags polymorphic (applicable to posts, comments, and documents), replace the specific foreign key with entity_type and entity_id columns. However, this sacrifices referential integrity, so use it only when the flexibility is genuinely needed.

Polymorphic Associations

Sometimes a single table needs to reference multiple other tables. Comments that can belong to posts, videos, or images are a classic example. There are two approaches:

Approach 1: Separate Foreign Keys

CREATE TABLE comments (
    id         UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    body       TEXT NOT NULL,
    author_id  UUID NOT NULL REFERENCES users(id),
    post_id    UUID REFERENCES posts(id) ON DELETE CASCADE,
    video_id   UUID REFERENCES videos(id) ON DELETE CASCADE,
    image_id   UUID REFERENCES images(id) ON DELETE CASCADE,
    created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    -- Ensure exactly one FK is set
    CHECK (
        (post_id IS NOT NULL)::int +
        (video_id IS NOT NULL)::int +
        (image_id IS NOT NULL)::int = 1
    )
);

Approach 2: Entity Type + Entity ID

CREATE TABLE comments (
    id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    body        TEXT NOT NULL,
    author_id   UUID NOT NULL REFERENCES users(id),
    entity_type VARCHAR(50) NOT NULL,  -- 'post', 'video', 'image'
    entity_id   UUID NOT NULL,
    created_at  TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE INDEX idx_comments_entity ON comments (entity_type, entity_id);

Approach 1 preserves referential integrity but requires schema changes when new entity types are added. Approach 2 is more flexible but loses FK constraints. Choose based on whether you add new entity types frequently.

Soft Deletes

Soft deletes mark records as deleted without actually removing them. This allows undo, compliance holds, and audit trails. The pattern adds a deleted_at timestamp that is NULL for active records and set to the deletion time for deleted records.

ALTER TABLE posts ADD COLUMN deleted_at TIMESTAMPTZ;

-- Active records: WHERE deleted_at IS NULL
-- Deleted records: WHERE deleted_at IS NOT NULL

-- Partial unique index to prevent duplicate titles among active posts
CREATE UNIQUE INDEX idx_posts_title_active
    ON posts (title) WHERE deleted_at IS NULL;

The partial unique index is a key detail: it enforces uniqueness only among active records, allowing a title to be "reused" after the original is soft-deleted.

Tree / Hierarchical Data

Storing tree-structured data (categories, org charts, comment threads) in a relational database requires choosing a strategy. The three most common approaches are adjacency list, materialized path, and closure table.

Adjacency List (Simplest)

CREATE TABLE categories (
    id         UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name       VARCHAR(255) NOT NULL,
    parent_id  UUID REFERENCES categories(id) ON DELETE CASCADE,
    sort_order INT NOT NULL DEFAULT 0
);

Materialized Path (Best for Reads)

CREATE TABLE categories (
    id    UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name  VARCHAR(255) NOT NULL,
    path  TEXT NOT NULL  -- '/root/electronics/phones/iphone'
);

-- Find all descendants
-- SELECT * FROM categories WHERE path LIKE '/root/electronics/%';

The adjacency list is simplest but requires recursive queries for subtrees. Materialized paths make reads fast with a simple LIKE query but require updating all descendant paths when a node moves. Choose based on your read/write ratio.

Putting It All Together

Most web applications combine several of these patterns. A SaaS application might use multi-tenancy + RBAC + audit logging + soft deletes simultaneously. The key is understanding each pattern independently so you can compose them without conflicts.

Try building these patterns in our Schema Designer to see how the tables relate visually. Export the DDL for your preferred SQL dialect and use it as a starting point for your migration files.