Postgres Schema Starter

-- Schema starter: [project name]
-- Conventions live here, in comments, next to the code they govern.
-- Your agent reads this file. Keep the reasoning in it.

-- Naming: tables plural snake_case (users, order_items). Columns snake_case.
-- Foreign keys named <singular>_id (user_id). Booleans read as facts (is_admin).

CREATE EXTENSION IF NOT EXISTS citext;  -- case-insensitive text, used for email

-- === QUESTION 1: identity ===============================================
-- UUID primary keys, generated by the database.
-- Why not serial integers: they leak information (user 12 knows there are
-- barely 12 users) and they collide the day you merge data or go multi-region.
-- gen_random_uuid() is built into Postgres 13+. No extension needed.

-- === QUESTION 2: time ====================================================
-- Every table gets created_at and updated_at, TIMESTAMPTZ, defaulted.
-- TIMESTAMPTZ, always. Plain TIMESTAMP has no zone and becomes a lie the
-- first time server and user disagree about where "now" is.
-- updated_at is maintained by a trigger so no app code can forget it:

CREATE OR REPLACE FUNCTION set_updated_at()
RETURNS trigger AS $$
BEGIN
  NEW.updated_at := now();
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- === QUESTION 3: deletion ================================================
-- Soft delete: a nullable deleted_at column. NULL = alive.
-- Why: users fat-finger deletes, support asks "can you undelete," and
-- accounting wants rows that referenced the deleted thing to still resolve.
-- A timestamp beats an is_deleted boolean: same query cost, and you also
-- learn WHEN. Hard-DELETE is reserved for privacy erasure (see §6.5 note).

-- === Example table: apply this shape to every table you add ==============

CREATE TABLE users (
  id          uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  email       citext NOT NULL UNIQUE,   -- citext: Bob@x.com == bob@x.com,
                                        -- which is what users believe anyway
  display_name text NOT NULL,
  is_admin    boolean NOT NULL DEFAULT false,  -- booleans get defaults;
                                               -- a NULL boolean is a riddle
  created_at  timestamptz NOT NULL DEFAULT now(),
  updated_at  timestamptz NOT NULL DEFAULT now(),
  deleted_at  timestamptz               -- NULL = active
);

CREATE TRIGGER users_set_updated_at
  BEFORE UPDATE ON users
  FOR EACH ROW EXECUTE FUNCTION set_updated_at();

-- Partial index: live-rows queries (the overwhelming majority) stay fast
-- and ignore the dead. Repeat this pattern per table.
CREATE INDEX users_active_idx ON users (created_at) WHERE deleted_at IS NULL;

-- A child table, showing foreign keys under the same conventions:

CREATE TABLE projects (
  id          uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id     uuid NOT NULL REFERENCES users (id),
  -- ON DELETE deliberately unset: with soft deletes, parent rows are not
  -- hard-deleted in normal life, and a surprising CASCADE is how whole
  -- subtrees vanish because someone deleted one user.
  name        text NOT NULL,
  status      text NOT NULL DEFAULT 'draft'
              CHECK (status IN ('draft', 'active', 'archived')),
              -- CHECK beats a bare text column: typo'd statuses die at
              -- INSERT time instead of at query time three weeks later
  created_at  timestamptz NOT NULL DEFAULT now(),
  updated_at  timestamptz NOT NULL DEFAULT now(),
  deleted_at  timestamptz
);

CREATE TRIGGER projects_set_updated_at
  BEFORE UPDATE ON projects
  FOR EACH ROW EXECUTE FUNCTION set_updated_at();

-- Index every foreign key. Postgres does not do this for you, and the
-- missing-FK-index is the most common "why is this join slow" answer.
CREATE INDEX projects_user_id_idx ON projects (user_id);
CREATE INDEX projects_active_idx ON projects (created_at) WHERE deleted_at IS NULL;

-- === Standing rules for the agent =======================================
-- 1. Every new table: uuid id, created_at, updated_at + trigger, deleted_at.
-- 2. Every query in app code filters deleted_at IS NULL unless the feature
--    is explicitly about deleted things. Centralize this in one query
--    helper/scope so it cannot be forgotten table by table.
-- 3. UNIQUE constraints must consider soft deletes: to allow a deleted
--    user's email to be reused, replace the plain UNIQUE with
--    CREATE UNIQUE INDEX ... ON users (email) WHERE deleted_at IS NULL;
-- 4. Schema changes go in migration files, in git, applied in order.
--    No live-editing production tables by hand, including by you.