Data modeling: a schema from the access patterns

-- A lookup table: the business edits plans, and a plan has attributes.
CREATE TABLE plans (
  code        text   PRIMARY KEY,
  seats       int    NOT NULL,
  price_cents bigint NOT NULL CHECK (price_cents >= 0)1
);

CREATE TABLE tenants (
  id         bigint      GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  slug       text        NOT NULL UNIQUE,
  plan       text        NOT NULL REFERENCES plans (code),
  created_at timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE users (
  tenant_id  bigint      NOT NULL REFERENCES tenants (id),
  id         uuid        NOT NULL,
  email      text        NOT NULL,
  attrs      jsonb       NOT NULL DEFAULT '{}',
  created_at timestamptz NOT NULL DEFAULT now(),
  deleted_at timestamptz,
  PRIMARY KEY (tenant_id, id)2
);
-- One live account per email in a tenant. A deleted row does not block a new one.
CREATE UNIQUE INDEX users_email ON users (tenant_id, lower(email))
  WHERE deleted_at IS NULL3;
CREATE INDEX users_attrs ON users USING gin (attrs jsonb_path_ops4);

CREATE TABLE roles (
  tenant_id bigint NOT NULL REFERENCES tenants (id),
  id        bigint GENERATED ALWAYS AS IDENTITY,
  name      text   NOT NULL,
  PRIMARY KEY (tenant_id, id),
  UNIQUE (tenant_id, name)
);

-- Many-to-many. Both keys carry tenant_id, so a link can never cross tenants.
CREATE TABLE user_roles (
  tenant_id bigint NOT NULL,
  user_id   uuid   NOT NULL,
  role_id   bigint NOT NULL,
  PRIMARY KEY (tenant_id, user_id, role_id),
  FOREIGN KEY (tenant_id, user_id) REFERENCES users (tenant_id, id),
  FOREIGN KEY (tenant_id, role_id) REFERENCES roles (tenant_id, id)5
);
CREATE INDEX user_roles_role ON user_roles (tenant_id, role_id);

-- An enum: a short, fixed set that only a code change extends.
CREATE TYPE audit_action AS ENUM6 ('create', 'update', 'delete', 'login');

-- Append-only, one partition per month. Retention drops a partition.
CREATE TABLE audit_log (
  tenant_id bigint       NOT NULL,
  at        timestamptz  NOT NULL DEFAULT now(),
  id        bigint       GENERATED ALWAYS AS IDENTITY,
  actor_id  uuid,
  action    audit_action NOT NULL,
  target    text         NOT NULL,
  diff      jsonb        NOT NULL DEFAULT '{}',
  PRIMARY KEY (tenant_id, at, id)
) PARTITION BY RANGE (at)7;
CREATE TABLE audit_log_2026_10 PARTITION OF audit_log
  FOR VALUES FROM ('2026-10-01') TO ('2026-11-01');
CREATE TABLE audit_log_2026_11 PARTITION OF audit_log
  FOR VALUES FROM ('2026-11-01') TO ('2026-12-01');

1. 1Money as an integer count of cents. Exact sums, and the database refuses a negative price.
2. 2tenant_id first: every index range is one tenant, and other tables can reference the pair.
3. 3A partial unique index. Only live rows must be unique, so a deleted user can sign up again.
4. 4A GIN index for containment (@>). It answers "attrs contains this pair" without a scan.
5. 5Both sides carry tenant_id. A link to another tenant's role cannot exist.
6. 6An enum for a fixed set. Postgres can add a value later, but not remove one.
7. 7One partition per month. Retention drops a partition; no row-by-row DELETE.

post(author, body):
  BEGIN
    insert the post                               // the truth
    FOR EACH follower OF author:                  // one INSERT ... SELECT1
      add (follower, created_at, post) to timeline
  COMMIT                                          // rows written = followers2

read_feed(reader):
  ids = 50 newest timeline rows of reader         // one index range3
  RETURN the posts of ids                         // 50 lookups by primary key

trim(reader, keep):
  delete timeline rows of reader older than the newest keep4

1. 1The fan-out is one statement: read the followers from the reverse index, write one timeline row each.
2. 2The cost moves to the writer. 10,001 followers took 41 ms; 100,001 took 497 ms.
3. 3Constant work: 100 rows read, whether the reader follows 10 authors or 5,000.
4. 4A cap keeps each timeline bounded. Older pages fall back to the join, which is rare.

// Post writes a post and fans it out to every follower's timeline, in one transaction. It
// returns the number of timeline rows written.
func Post(ctx context.Context, db *pgxpool.Pool, id, author int64, at time.Time, body string) (int64, error) {
  tx, err := db.Begin(ctx)
  if err != nil {
    return 0, fmt.Errorf("begin: %w", err)
  }
  if _, err := tx.Exec(ctx, Q("feed", "post"), id, author, at, body); err != nil {
    return 0, fmt.Errorf("insert post: %w", rollback(ctx, tx, err))
  }
  tag, err := tx.Exec(ctx, Q("feed", "fan_out"), id, author, at)
  if err != nil {
    return 0, fmt.Errorf("fan out: %w", rollback(ctx, tx, err))
  }
  if err := tx.Commit(ctx); err != nil {
    return 0, fmt.Errorf("commit: %w", err)
  }
  return tag.RowsAffected(), nil
}

-- One row per follower of the author. The cost grows with the follower count.
INSERT INTO timeline (user_id, created_at, post_id, author_id)
SELECT f.follower_id, $3, $1, $2 FROM follows f WHERE f.followee_id = $2;

-- Denormalised: one range scan of the reader's timeline, then 50 lookups by primary key.
SELECT p.id, p.author_id, p.created_at, p.body
FROM timeline t
JOIN posts p ON p.id = t.post_id
WHERE t.user_id = $1
ORDER BY t.created_at DESC, t.post_id DESC
LIMIT 50;

-- Keep the newest $2 rows per reader, so a timeline never grows without bound.
DELETE FROM timeline t
WHERE t.user_id = $1
  AND (t.created_at, t.post_id) < (
    SELECT created_at, post_id FROM timeline
    WHERE user_id = $1
    ORDER BY created_at DESC, post_id DESC
    OFFSET $2 - 1 LIMIT 1);

subtree(n), adjacency list:
  rows = {n}
  REPEAT: rows += children of the last level   // one index scan per level1
  UNTIL a level adds nothing

subtree(n), materialised path:
  p = path of n                                // '/1/2/'
  RETURN rows WHERE p <= path < p + '~'2        // one index range

subtree(n), closure table:
  RETURN descendants WHERE ancestor = n3        // one index range, too

move(n, under m):
  adjacency: set parent of n to m              // 1 row
  path:      rewrite the prefix of every row in the subtree4
  closure:   delete (old ancestor, node) pairs
             insert (new ancestor, node) pairs5

1. 1A recursive query: one round per level, each an index scan on parent_id.
2. 2In byte order, every path with this prefix sits in one contiguous index range.
3. 3The table stores every ancestor pair, so the subtree is already listed.
4. 44,681 rows for a subtree of 4,681 nodes.
5. 5Subtree size × (old + new ancestors): 18,724 rows in the lab.

-- adjacency list
-- Walk down one level per step.
WITH RECURSIVE sub AS (
  SELECT id FROM cat_adjacency WHERE id = $1
  UNION ALL
  SELECT c.id FROM cat_adjacency c JOIN sub ON c.parent_id = sub.id
)
SELECT id FROM sub ORDER BY id;

-- materialised path
-- One range scan: every path that starts with the node's path. '~' sorts after '/' and digits.
SELECT c.id
FROM cat_path c, (SELECT path FROM cat_path WHERE id = $1) s
WHERE c.path >= s.path AND c.path < s.path || '~'
ORDER BY c.id;

-- closure table
-- One index range: every descendant of $1.
SELECT descendant_id AS id FROM cat_closure WHERE ancestor_id = $1 ORDER BY id;

-- Move node $1 under $2: one row changes.
UPDATE cat_adjacency SET parent_id = $2 WHERE id = $1;

-- Every row in the subtree gets a new prefix.
UPDATE cat_path c
SET path = (SELECT path FROM cat_path WHERE id = $2) || substr(c.path, length(m.parent_path) + 1)
FROM (SELECT path, left(path, length(path) - length(id::text) - 1) AS parent_path
      FROM cat_path WHERE id = $1) m
WHERE c.path >= m.path AND c.path < m.path || '~';

-- Remove the links from the old ancestors to every node in the subtree.
DELETE FROM cat_closure c
USING cat_closure sub, cat_closure up
WHERE sub.ancestor_id = $1
  AND up.descendant_id = $1 AND up.ancestor_id <> $1
  AND c.ancestor_id = up.ancestor_id AND c.descendant_id = sub.descendant_id;

-- Link every new ancestor to every node in the subtree.
INSERT INTO cat_closure (ancestor_id, descendant_id, depth)
SELECT up.ancestor_id, sub.descendant_id, up.depth + sub.depth + 1
FROM cat_closure up, cat_closure sub
WHERE up.descendant_id = $2 AND sub.ancestor_id = $1;