Skip to main content

Database Schema Operations

ProBeya uses Drizzle ORM with PostgreSQL 16. Every fresh V2 installation, canonical local setup, and forward upgrade of a database that already carries the V2 migration ledger uses the same ordered contract:

  1. db:migrate applies the immutable, journaled PostgreSQL migration plan.
  2. apply-rls installs the reviewed security overlay.
  3. check-rls audits the resulting catalog and fails closed on drift.

Production runs all three steps through the dedicated schema image. The minimal Next.js web image is not a migration image, and neither web nor ws ever receives DATABASE_ADMIN_URL or DATABASE_ADMIN_URL_DOCKER.

Source Layout​

packages/db/
├── src/schema/                 # Drizzle table and index definitions
├── drizzle/                    # Immutable SQL journal, manifest, and migrations
│   └── meta/_probeya_rollback_contract.json # Production-loaded open-window classifications
├── security-overlay/           # Reviewed RLS/functions/grants applied after migrations
├── migration-proposals/        # Explicitly non-executable future changes
├── migrations-archive/         # Historical evidence, never an executable source
├── src/migrations/
│   └── postgresql.ts           # Hash-verified, locked, transactional runner
├── src/scripts/
│   ├── migrate-postgresql.ts   # Non-interactive db:migrate CLI
│   ├── apply-rls-coverage.ts   # Functions, SQL artifacts, grants, canonical RLS
│   └── check-rls-coverage.ts   # Fail-closed catalog verification
└── drizzle.config.ts           # Uses DATABASE_ADMIN_URL when available

Local Development​

Start PostgreSQL 16 and copy the environment template first:

docker compose -f docker/docker-compose.yml up -d postgres
cp .env.example .env

The local template separates the two database identities:

  • DATABASE_URL uses probeya_app, the RLS-enforced runtime role.
  • DATABASE_ADMIN_URL uses probeya_admin, the non-superuser schema owner with BYPASSRLS for controlled administration and seeding.

Apply the complete local schema contract:

pnpm db:migrate
pnpm --filter @probeya/db apply-rls
pnpm --filter @probeya/db check-rls

Seed demo data only after the check passes:

pnpm db:seed

db:migrate requires DATABASE_ADMIN_URL and never falls back to the RLS-subject DATABASE_URL. It verifies the Drizzle journal, SQL, and snapshot hashes before execution. The database ledger must be an exact immutable prefix of that plan. One PostgreSQL advisory lock serializes operators, and one outer transaction commits all pending migrations and ledger rows together or rolls them all back.

The Phase 143 V2 baseline is intentionally a fresh-database cutover. An MVP-v0 database has application objects but no immutable V2 ledger and is not an in-place upgrade target. Preserve any required v0 backup, provision an empty PostgreSQL 16 database or volume, and run the V2 sequence there. The migrator detects and rejects a non-empty public schema without the ledger rather than colliding with old objects. After the V2 baseline is recorded, future reviewed forward migrations use the normal in-place sequence.

db:push remains available only for explicit schema introspection on a disposable development database. It is not the canonical local, shared, or production deployment path, and it does not replace either RLS step.

Developing a Schema Change​

  1. Edit the appropriate file in packages/db/src/schema/.

  2. Generate SQL when a reviewable Drizzle artifact is required:

    pnpm db:generate

  3. Review the generated SQL and any destructive-change warning. Add the migration to the ordered journal and immutable SHA-256 manifest.

  4. Add or update a hand-authored SQL artifact only when the change cannot be represented safely in the Drizzle schema (for example a security-definer function or a guarded data invariant).

  5. Ensure the apply script installs security-overlay artifacts in a deterministic order.

  6. Test the full path against a disposable PostgreSQL 16 database:

    pnpm db:migrate
    pnpm --filter @probeya/db apply-rls
    pnpm --filter @probeya/db check-rls
    DATABASE_APP_URL="$DATABASE_URL" \
      PROBEYA_RLS_ACCEPTANCE_DISPOSABLE=1 \
      pnpm --filter @probeya/db test:rls

The RLS acceptance lane is destructive and must target a disposable database. It connects directly as exact probeya_app and probeya_admin, verifies both role contracts before discovery, and refuses to run without the explicit disposable marker.

Always review generated SQL. Never edit the journal, manifest, or a migration that may already have been applied. Hash or ledger drift is an integrity failure; create a new forward migration instead. The runner also rejects transaction control and PostgreSQL operations that cannot safely execute in its atomic transaction.

Release-Artifact Rollback Window​

After the V2 baseline, rollback deploys the exact previous bound web, WebSocket, runtime-configuration, and Nginx release artifacts against the forward database schema and security overlay. Both release records bind their exact source commit and schema image digest. Only the candidate schema image runs during forward migration; the previous schema image remains bound as evidence but is never run, and rollback does not run a down migration. Until a separate reviewed release formally closes the rollback window, every journaled upgrade must follow an expand/contract design:

  • add tables, nullable columns, or columns with compatible defaults;
  • preserve every read and write required by the previous approved artifact;
  • do not drop or rename objects, truncate data, rewrite a shared type incompatibly, clear previous-artifact data, or add a constraint that rejects an old required operation; and
  • classify every post-baseline journal entry with its exact SQL and snapshot hashes, replay behavior, compatibility probe, and rollback impact.

The active Phase 143 plan classifies 0000_v2_ateliers_mvp_baseline as the fresh-install anchor and 0001_organization_default_view_mode as a statically additive candidate pending exact dual-version rehearsal. The sole pre-approved irreversible data rewrite is 0002_activity_log_prompt_redaction: it removes raw prompts and an untrusted model-reported version only from exact prompt-bearing legacy AI-route audit events, preserves event identity and safe telemetry, and changes no schema. Its pinned digests are:

SQL SHA-256
fae39a07fdcbd9d56db5fe4646689e4c2ab8addc60f0c04e57bbc0386307d01e

journal SHA-256 at freeze
fde1a5c4176bdc15c7b1e2405d585b4d46a0fb5882701eef7c3ae611c6352487

0002 snapshot SHA-256
d2343ce9a1ba8643ce28e4d4cb825f185346729615c7b38919df2dcd3313c42f

A reviewed append changes the full journal and manifest hashes. It must preserve the permanent idx, tag, when, SQL-hash, and snapshot-hash pins for the immutable three-entry prefix and add its own reviewed classification. During the open window, the production migration loader reads packages/db/drizzle/meta/_probeya_rollback_contract.json, whose controlled SHA-256 is 77997c8780834bfcde22207ca325204970bbb4beebb9eedd7333fb6fc016a4b2. Under the exact-role transaction and PostgreSQL advisory lock, it fails closed before any migration or ledger mutation on contract or journal/manifest drift, unclassified SQL, a statement outside the closed SQL allowlist, or an incompatible Drizzle snapshot transition. SQL inspection alone does not replace snapshot compatibility validation.

Replaying 0002 changes zero already-redacted rows. Rolling the application back never restores the deleted sensitive content; the compatibility gate instead proves that the previous artifact can read the event without those optional JSON keys and cannot expose or resume the old raw-prompt writer. A future irreversible rewrite requires its own controlled review.

Compatibility columns and values remain in place while the window is open. For example, new application code neither reads nor writes the legacy WorkflowDocument share columns. That logical access retirement does not clear physical state: the columns and every previous-artifact-required value remain unchanged throughout the open window. After formal closure, a later reviewed contract migration may clear or drop them only after an exact predicate proves zero usable previous-artifact share state.

The same window keeps MVP-v0 bookmark mappings cache-revocable: map-owned aliases use temporary 307 with Cache-Control: private, no-store only for eligible HTML document navigation. Status 307 alone is insufficient: reversal also needs observed no-store or purge evidence. Promoting one of those mappings to 308 is a separate later release after closure. This rule is scoped to the frozen bookmark map; it does not reclassify endpoint-owned protocol redirects, domain canonicalization, or unrelated historical redirects.

Production​

Always use both Compose files. The production sequence is:

For the one-time MVP-v0 to V2 cutover, stop v0 writers and retain the required backup, but do not run schema against the v0 database. Point the operations profile at a newly provisioned empty PostgreSQL 16 database or volume. The sequence below is an in-place upgrade only after that database contains the V2 ledger.

  1. Stop writes and create a restorable backup.
  2. Run the transactional ownership adoption operation.
  3. Run the dedicated schema/RLS operation.
  4. Start the application only after both operations exit successfully.
# 1. Backup while PostgreSQL is running and application writes are stopped
docker compose \
  -f docker/docker-compose.yml \
  -f docker/docker-compose.prod.yml \
  exec -T postgres sh -ec \
    'pg_dump --username "$POSTGRES_USER" --dbname "$POSTGRES_DB" --format=custom' \
  > probeya-before-upgrade.dump

# 2. Reconcile roles and transfer existing object ownership
docker compose \
  -f docker/docker-compose.yml \
  -f docker/docker-compose.prod.yml \
  --profile ownership run --rm ownership

# 3. Migrate, apply reviewed SQL/RLS, and verify the catalog
docker compose \
  -f docker/docker-compose.yml \
  -f docker/docker-compose.prod.yml \
  --profile operations run --build --rm schema

# 4. Start only after the schema command succeeds
docker compose \
  -f docker/docker-compose.yml \
  -f docker/docker-compose.prod.yml \
  up -d

The --build flag on schema is mandatory during post-baseline V2 upgrades; without it, Compose may reuse the previous release's cached schema image.

Run ownership even on a new database. It is idempotent and ensures probeya_admin, rather than the PostgreSQL superuser, owns all supported application objects. The runtime web service uses only DATABASE_URL_DOCKER (probeya_app), while the MVP ws service receives no database URL. Neither receives the admin URL.

Rollback and Failure Handling​

Drizzle Kit does not provide a safe automatic down migration for this workflow. During an open rollback window, return to the exact previous bound web, WebSocket, runtime-configuration, and Nginx artifacts on the compatible forward schema or prepare and review an additive forward correction. A destructive migration is not an eligible release-window change. If an out-of-contract production event has already damaged required state, keep application writers stopped and restore the controlled pre-upgrade backup only under the incident/change procedure. A source that predates 0002 must remain offline until the exact redaction migration is applied, replayed, and independently verified to contain no raw prompt or model-controlled version content. No application, export, replica, or rollback artifact may access that source before verification. Backup restoration is not the normal artifact-rollback path.

Never:

  • edit an applied journal entry, manifest hash, or migration SQL file;
  • run the V2 baseline against an MVP-v0 or other non-empty unledgered schema;
  • use pnpm db:push as an upgrade or shared-environment migration;
  • run schema commands inside the standalone web or ws container;
  • point web or ws at DATABASE_ADMIN_URL_DOCKER;
  • clear or drop previous-artifact compatibility state before formal rollback closure;
  • expose a pre-0002 or otherwise content-restoring backup to an application, export, replica, or rollback artifact before offline redaction verification;
  • start the application after apply-rls or check-rls has failed.

See Docker Deployment for the complete release sequence.