P-4 — Schema Design for Real Applications

Rule of thumb: use BIGSERIAL for internal tables where sequential IDs are fine. Use UUID (v7 preferred) for public-facing IDs or distributed systems.

---

Standard Schema Patterns

Audit Fields — On Every Table

Every table in a production system should have these columns:

CREATE TABLE products (
  id         BIGSERIAL    PRIMARY KEY,
  -- ... your columns ...
  created_at TIMESTAMPTZ  NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMPTZ  NOT NULL DEFAULT NOW()
);

updated_at does not update automatically — you must update it in your queries or use a trigger:

-- Trigger to auto-update updated_at
CREATE OR REPLACE FUNCTION set_updated_at()
RETURNS TRIGGER AS $$
BEGIN
  NEW.updated_at = NOW();
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;
 
CREATE TRIGGER products_set_updated_at
BEFORE UPDATE ON products
FOR EACH ROW EXECUTE FUNCTION set_updated_at();
 
-- Now every UPDATE automatically sets updated_at = NOW()
UPDATE products SET price = 119.99 WHERE id = 1;
-- updated_at is automatically set

Soft Deletes — The deleted_at Pattern

Hard deletion (DELETE) permanently removes data and breaks audit trails. Soft deletion marks rows as deleted without removing them.

CREATE TABLE users (
  id         BIGSERIAL    PRIMARY KEY,
  email      TEXT         NOT NULL UNIQUE,
  -- ... other columns ...
  deleted_at TIMESTAMPTZ  -- NULL = active, non-NULL = soft-deleted
);
 
-- "Delete" a user (mark as deleted, not actually removed)
UPDATE users SET deleted_at = NOW() WHERE id = $user_id;
 
-- Query only active users (common — put this in all relevant queries)
SELECT * FROM users WHERE deleted_at IS NULL;
 
-- Or create a VIEW for convenience
CREATE VIEW active_users AS
  SELECT * FROM users WHERE deleted_at IS NULL;

The soft delete trade-off:

• Benefit: data is preserved for audit, recovery, and analytics
• Cost: every query must include WHERE deleted_at IS NULL — forgetting it returns deleted records
• Cost: unique constraints become complicated (two users with same email — one deleted, one active)

-- Handling UNIQUE constraint with soft deletes:
-- A simple UNIQUE on email blocks creating a new account for a deleted email
 
-- Solution 1: partial unique index (only enforce uniqueness for active users)
CREATE UNIQUE INDEX users_email_active_unique
ON users (email)
WHERE deleted_at IS NULL;
-- Two rows with same email allowed if at least one has deleted_at set
 
-- Solution 2: add a is_deleted boolean (less flexible)
-- Solution 3: move deleted records to a separate archive table

Multi-Tenancy — Isolating Customer Data

For SaaS applications serving multiple organisations:

-- Every table has a tenant/organisation reference
CREATE TABLE organisations (
  id   BIGSERIAL PRIMARY KEY,
  name TEXT NOT NULL
);
 
CREATE TABLE projects (
  id       BIGSERIAL PRIMARY KEY,
  org_id   BIGINT NOT NULL REFERENCES organisations(id) ON DELETE CASCADE,
  name     TEXT NOT NULL
);
 
CREATE TABLE tasks (
  id         BIGSERIAL PRIMARY KEY,
  org_id     BIGINT NOT NULL REFERENCES organisations(id) ON DELETE CASCADE,
  project_id BIGINT NOT NULL REFERENCES projects(id) ON DELETE CASCADE,
  title      TEXT NOT NULL
);
 
-- Every query filters by org_id — never show one org's data to another
SELECT * FROM tasks WHERE org_id = $current_org_id AND project_id = $project_id;
 
-- Index for multi-tenant performance
CREATE INDEX idx_tasks_org_project ON tasks (org_id, project_id);

Row-Level Security (covered in P-6) can enforce this automatically at the database level.

---

Schema Migration Tools

In Phase 1, we touched on migrations conceptually. Here is the practical toolkit for mid-level engineers.

What a migration file looks like

-- migrations/20260517_001_add_task_labels.sql
-- Up migration (apply change)
CREATE TABLE labels (
  id      BIGSERIAL PRIMARY KEY,
  org_id  BIGINT NOT NULL REFERENCES organisations(id),
  name    TEXT   NOT NULL,
  color   TEXT   NOT NULL DEFAULT '#3B82F6',
  UNIQUE (org_id, name)
);
 
CREATE TABLE task_labels (
  task_id  BIGINT NOT NULL REFERENCES tasks(id) ON DELETE CASCADE,
  label_id BIGINT NOT NULL REFERENCES labels(id) ON DELETE CASCADE,
  PRIMARY KEY (task_id, label_id)
);
 
CREATE INDEX idx_task_labels_task ON task_labels (task_id);
CREATE INDEX idx_task_labels_label ON task_labels (label_id);

node-pg-migrate (Node.js)

// migrations/20260517001-add-task-labels.js
exports.up = async (pgm) => {
  pgm.createTable('labels', {
    id: { type: 'bigserial', primaryKey: true },
    org_id: { type: 'bigint', notNull: true, references: 'organisations(id)' },
    name: { type: 'text', notNull: true },
    color: { type: 'text', notNull: true, default: "'#3B82F6'" },
  });
  pgm.addConstraint('labels', 'labels_org_name_unique', 'UNIQUE (org_id, name)');
};
 
exports.down = async (pgm) => {
  pgm.dropTable('labels');
};

# Run pending migrations
DATABASE_URL=postgresql://... node-pg-migrate up
 
# Roll back last migration
DATABASE_URL=postgresql://... node-pg-migrate down

Prisma Migrate (Node.js / TypeScript)

// schema.prisma
model Task {
  id        BigInt   @id @default(autoincrement())
  orgId     BigInt
  projectId BigInt
  title     String
  status    String   @default("todo")
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt
 
  org     Organisation @relation(fields: [orgId], references: [id])
  project Project      @relation(fields: [projectId], references: [id])
  labels  TaskLabel[]
}

# Generate and apply a migration from schema changes
npx prisma migrate dev --name add-task-labels
 
# Apply migrations in production
npx prisma migrate deploy

Safe schema changes in production

Not all ALTER TABLE operations are instant. Know which are dangerous:

-- ✅ Instant (no table rewrite) — PG11+, and only for a CONSTANT default:
ALTER TABLE tasks ADD COLUMN priority INTEGER DEFAULT 3;
ALTER TABLE tasks ADD COLUMN notes TEXT;
ALTER TABLE tasks ALTER COLUMN status SET DEFAULT 'todo';
CREATE INDEX CONCURRENTLY idx_tasks_status ON tasks (status);
 
-- ⚠️ Potentially slow (may lock or rewrite table):
ALTER TABLE tasks ALTER COLUMN price TYPE NUMERIC(15,2);  -- full rewrite
ALTER TABLE tasks ADD COLUMN required TEXT NOT NULL;       -- PG < 11: full rewrite
-- PG 11+ with a constant DEFAULT (e.g. DEFAULT 3): instant, metadata-only.
-- A VOLATILE default (e.g. DEFAULT now() or DEFAULT gen_random_uuid()) still
-- forces a full table rewrite even on PG 18 — the instant-default optimization
-- only applies when Postgres can prove the same value works for every existing row.
 
-- ✅ Safe pattern for adding a NOT NULL column to a live table:
-- Step 1: add nullable
ALTER TABLE tasks ADD COLUMN region TEXT;
-- Step 2: backfill (in batches for large tables)
UPDATE tasks SET region = 'us-east' WHERE region IS NULL;
-- Step 3: add NOT NULL constraint
ALTER TABLE tasks ALTER COLUMN region SET NOT NULL;

---

Practical Exercise: Redesign a Real Schema

Start with a poorly designed schema and improve it:

-- ❌ The original bad schema (a real example of common mistakes)
CREATE TABLE user_orders (
  id           SERIAL,              -- should be BIGSERIAL
  user_email   TEXT,                -- missing NOT NULL and UNIQUE
  user_name    TEXT,                -- should reference a users table
  product_name TEXT,                -- should reference a products table
  price        FLOAT,               -- should be NUMERIC
  order_date   TIMESTAMP,           -- should be TIMESTAMPTZ
  is_deleted   BOOLEAN              -- missing NOT NULL DEFAULT false
);

Redesign it:

-- ✅ Normalised, correctly typed schema
CREATE TABLE users (
  id         BIGSERIAL    PRIMARY KEY,
  name       TEXT         NOT NULL,
  email      TEXT         NOT NULL UNIQUE,
  created_at TIMESTAMPTZ  NOT NULL DEFAULT NOW(),
  deleted_at TIMESTAMPTZ
);
 
CREATE TABLE products (
  id    BIGSERIAL       PRIMARY KEY,
  name  TEXT            NOT NULL,
  price NUMERIC(10, 2)  NOT NULL CHECK (price >= 0)
);
 
CREATE TABLE orders (
  id          BIGSERIAL     PRIMARY KEY,
  user_id     BIGINT        NOT NULL REFERENCES users(id) ON DELETE RESTRICT,
  ordered_at  TIMESTAMPTZ   NOT NULL DEFAULT NOW(),
  deleted_at  TIMESTAMPTZ   -- soft delete
);
 
CREATE TABLE order_items (
  id         BIGSERIAL      PRIMARY KEY,
  order_id   BIGINT         NOT NULL REFERENCES orders(id) ON DELETE CASCADE,
  product_id BIGINT         NOT NULL REFERENCES products(id) ON DELETE RESTRICT,
  quantity   INTEGER        NOT NULL CHECK (quantity > 0),
  unit_price NUMERIC(10,2)  NOT NULL  -- snapshot of price at order time
);
 
-- Indexes
CREATE INDEX idx_orders_user    ON orders (user_id);
CREATE INDEX idx_order_items_order ON order_items (order_id);
CREATE INDEX idx_order_items_product ON order_items (product_id);
CREATE UNIQUE INDEX users_email_active ON users (email) WHERE deleted_at IS NULL;

---

Summary

---

Module P-5 covers JSON and JSONB — working with semi-structured data in PostgreSQL, when it is the right tool, and when it is a schema design shortcut you will regret.

Next: P-5 — JSON and JSONB — Working With Semi-Structured Data →