Skip to main content

Add a migration

Schema changes are new, numbered SQL files. Never edit a migration that has been applied anywhere.

1. Write the SQL​

Create apps/api/migrations/NNNN_short_description.sql, numbered after the last one (0007_… follows 0006_fte_attributes_report_notes.sql). Files are applied in name order, each in its own transaction, so don't add BEGIN/COMMIT.

Start with a comment saying what the migration does and why. For a new engagement-scoped table:

-- 0003 — Saved explorer views per dataset.

CREATE TABLE explorer_views (
  id            uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  dataset_id    uuid NOT NULL REFERENCES datasets(id) ON DELETE CASCADE,
  engagement_id uuid NOT NULL REFERENCES engagements(id) ON DELETE CASCADE,
  name          text NOT NULL CHECK (length(name) BETWEEN 1 AND 160),
  filters       jsonb NOT NULL,
  created_by    uuid REFERENCES users(id) ON DELETE SET NULL,
  created_at    timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX explorer_views_dataset ON explorer_views (dataset_id);

-- Engagement-scoped: members read; leads and analysts write while active.
ALTER TABLE explorer_views ENABLE ROW LEVEL SECURITY;
CREATE POLICY explorer_views_read ON explorer_views FOR SELECT
  USING (engagement_id IN (SELECT app.readable_engagement_ids()));
CREATE POLICY explorer_views_insert ON explorer_views FOR INSERT
  WITH CHECK (engagement_id IN (SELECT app.writable_engagement_ids()));
CREATE POLICY explorer_views_update ON explorer_views FOR UPDATE
  USING (engagement_id IN (SELECT app.writable_engagement_ids()))
  WITH CHECK (engagement_id IN (SELECT app.writable_engagement_ids()));
CREATE POLICY explorer_views_delete ON explorer_views FOR DELETE
  USING (engagement_id IN (SELECT app.writable_engagement_ids()));

GRANT SELECT, INSERT, UPDATE, DELETE ON explorer_views TO workbench_app;

Rules:

  • Every new table gets RLS enabled and policies in the same migration, and explicit grants to workbench_app. Without a grant, the app role can't touch it; without policies, RLS denies everything.
  • Engagement-scoped tables carry engagement_id (even if it's derivable) so policies can use the app.*_engagement_ids() helpers with IN (SELECT …).
  • Use CHECK constraints for enums and bounds, mirroring the zod schemas.
  • Anything holding personal data: decide how the retention purge handles it (services/retention.ts) and whether it references employee IDs.
  • Append-only data (like audit_log) gets no UPDATE or DELETE grant or policy.
  • Extending a CHECK on an enum (for example prompt_versions.prompt_key) means dropping and re-adding the constraint.

2. Mirror it in Drizzle​

Add the table or column to apps/api/src/db/schema.ts with matching types, so queries are typed. Drizzle doesn't generate or run migrations here: the SQL is the source of truth, because policies, grants and helper functions can't be expressed in Drizzle.

3. Test it​

Every API test file runs all migrations on a fresh PGlite, so the new migration is exercised automatically. Add tests in apps/api/test that:

  • the intended roles can read and write;
  • outsiders, viewers (for writes) and closed engagements are refused by the database (call the route, and also try the query directly as the app role if the route has its own checks);
  • the purge removes or keeps the data as designed.

4. Run it​

  • Locally: restart npm run dev; the Node server applies pending migrations on start.
  • Staging, then production: DATABASE_URL=… npm run db:migrate, deploy, test. Migrate before deploying code that depends on the change; the Worker never migrates.

5. Update the docs​

Update docs/DATA_MODEL.md in the repository and the Data model page here.