Database Tutorial #18: Database Design Patterns

ALTER TABLE users ADD COLUMN deleted_at TIMESTAMPTZ;

-- Soft delete
UPDATE users SET deleted_at = NOW() WHERE id = 42;

-- Query active users only
SELECT * FROM users WHERE deleted_at IS NULL;

-- Include deleted users
SELECT * FROM users;

-- Restore
UPDATE users SET deleted_at = NULL WHERE id = 42;

CREATE INDEX idx_users_active ON users (email) WHERE deleted_at IS NULL;

CREATE VIEW active_users AS
  SELECT * FROM users WHERE deleted_at IS NULL;

CREATE TABLE audit_log (
  id          BIGSERIAL PRIMARY KEY,
  table_name  TEXT NOT NULL,
  record_id   BIGINT NOT NULL,
  action      TEXT NOT NULL CHECK (action IN ('INSERT', 'UPDATE', 'DELETE')),
  old_data    JSONB,
  new_data    JSONB,
  changed_by  BIGINT REFERENCES users (id),
  changed_at  TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_audit_log_record ON audit_log (table_name, record_id);
CREATE INDEX idx_audit_log_time ON audit_log (changed_at DESC);

CREATE OR REPLACE FUNCTION log_changes() RETURNS TRIGGER AS $$
BEGIN
  INSERT INTO audit_log (table_name, record_id, action, old_data, new_data)
  VALUES (
    TG_TABLE_NAME,
    COALESCE(NEW.id, OLD.id),
    TG_OP,
    CASE WHEN TG_OP = 'INSERT' THEN NULL ELSE to_jsonb(OLD) END,
    CASE WHEN TG_OP = 'DELETE' THEN NULL ELSE to_jsonb(NEW) END
  );
  RETURN COALESCE(NEW, OLD);
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER users_audit
  AFTER INSERT OR UPDATE OR DELETE ON users
  FOR EACH ROW EXECUTE FUNCTION log_changes();

ALTER TABLE users ADD COLUMN tenant_id BIGINT NOT NULL REFERENCES tenants (id);
ALTER TABLE orders ADD COLUMN tenant_id BIGINT NOT NULL REFERENCES tenants (id);

-- All queries filter by tenant
SELECT * FROM users WHERE tenant_id = :current_tenant;

ALTER TABLE users ENABLE ROW LEVEL SECURITY;

CREATE POLICY tenant_isolation ON users
  USING (tenant_id = current_setting('app.tenant_id')::BIGINT);

-- Set the tenant for each connection
SET app.tenant_id = '42';

CREATE SCHEMA tenant_42;
CREATE TABLE tenant_42.users (...);

-- PostgreSQL: use gen_random_uuid() (UUID v4)
CREATE TABLE orders (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  ...
);

-- Or install pg_ulid extension for ULIDs
CREATE EXTENSION IF NOT EXISTS pg_ulid;
CREATE TABLE orders (
  id TEXT PRIMARY KEY DEFAULT gen_ulid(),
  ...
);

npm install ulid

import { ulid } from "ulid";

const id = ulid(); // "01ARZ3NDEKTSV4RRFFQ69G5FAV"

ALTER TABLE products ADD COLUMN version INTEGER NOT NULL DEFAULT 1;

-- Read
SELECT id, name, price, stock, version FROM products WHERE id = 5;
-- Returns: version = 3

-- Update — only succeeds if version hasn't changed
UPDATE products
SET price = 99.99, version = version + 1
WHERE id = 5 AND version = 3;

-- Check affected rows in your application
-- 0 rows affected = concurrent update detected → retry

CREATE TABLE comments (
  id         BIGSERIAL PRIMARY KEY,
  text       TEXT NOT NULL,
  post_id    BIGINT REFERENCES posts (id),
  video_id   BIGINT REFERENCES videos (id),
  product_id BIGINT REFERENCES products (id),
  -- Only one should be non-null
  CONSTRAINT chk_one_parent CHECK (
    (post_id IS NOT NULL)::int +
    (video_id IS NOT NULL)::int +
    (product_id IS NOT NULL)::int = 1
  )
);

CREATE TABLE comments (
  id            BIGSERIAL PRIMARY KEY,
  target_type   TEXT NOT NULL CHECK (target_type IN ('post', 'video', 'product')),
  target_id     BIGINT NOT NULL,
  text          TEXT NOT NULL,
  created_at    TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_comments_target ON comments (target_type, target_id);

SELECT * FROM posts ORDER BY created_at DESC LIMIT 20 OFFSET 200;
-- Gets slower as offset increases (reads 220 rows, discards 200)

-- First page
SELECT * FROM posts ORDER BY id DESC LIMIT 20;
-- Last id returned: 580

-- Next page (use last id as cursor)
SELECT * FROM posts WHERE id < 580 ORDER BY id DESC LIMIT 20;