---
module: packages/database-pg/drizzle
date: 2026-04-21
last_updated: 2026-04-26
category: workflow-issues
problem_type: workflow_issue
component: database
severity: high
related_components:
  - development_workflow
  - tooling
applies_when:
  - "A PR adds or modifies a .sql file under packages/database-pg/drizzle/ that is not registered in meta/_journal.json"
  - "The migration header uses the 'Apply manually (no CI migration runner)' convention"
  - "A subsequent migration's pre-flight invariant assumes a prior hand-rolled migration has already been applied to the target database"
  - "A developer runs pnpm db:push or db:generate expecting drizzle-kit to reconcile all .sql files in the drizzle/ directory"
  - "A Lambda deploys application code that depends on a table, column, or index introduced by an unindexed .sql file"
tags:
  - drizzle
  - migrations
  - database-pg
  - db-push
  - journal-drift
  - composable-skills
  - manual-migration
  - deploy-workflow
  - developer-workflow
---

# Manually-applied drizzle migrations silently drift from dev

## Context

The ThinkWork monorepo runs **two parallel migration tracks** in
`packages/database-pg/drizzle/`, and only one has drift detection:

1. **Drizzle-kit auto-tracked migrations.** Generated by `pnpm db:generate`,
   registered in `packages/database-pg/drizzle/meta/_journal.json`, and applied
   by `pnpm db:push` (which calls `drizzle-kit push --force` — see
   `scripts/db-push.sh`). Each indexed file has a corresponding
   `meta/NNNN_snapshot.json`. The journal is drizzle-kit's source of truth.
2. **Hand-rolled "apply manually" migrations.** Plain `.sql` files in the same
   directory but deliberately omitted from `meta/_journal.json`. The convention
   exists because drizzle-kit doesn't cleanly emit certain DDL constructs the
   schema actually needs: partial unique indices, named CHECK constraints with
   cross-column predicates, surgical follow-ups to existing tables, precise
   step ordering with FKs + seed data.

The unindexed-files inventory as of 2026-04-21:

| File | In `_journal.json`? | Header declares "Apply manually"? |
|---|---|---|
| `0012_eval_seed_unique.sql` | No | No (no header at all) |
| `0018_skill_runs.sql` | No | Yes, with `to_regclass` pre-flight |
| `0019_webhook_skill_runs.sql` | No | Yes, with pre-flight invariants in prose |

A numbering collision is also visible: `0018_agent_workspace_overlay.sql` IS
in the journal (idx 17); `0018_skill_runs.sql` is NOT. Same ordinal, two
tracks, no coordination.

**`scripts/db-push.sh` doesn't run the manual track.** It calls
`drizzle-kit push --force` which only knows about journal-tracked files. It
also hangs against the live cluster's schema introspection (session history:
documented failure in sessions `3fdbdb0c` Apr 17, `6326e70b` Apr 18), so in
practice even journal-tracked migrations are applied manually via direct
`psql`.

**`deploy.yml` has no migration step at all.** Named explicitly in session
`3fdbdb0c` on 2026-04-17: *"Confirmed — `deploy.yml` has no migration step at
all. That's a real gap, not a deliberate choice."* The proposed fix (a
`drizzle-kit migrate` step gated on schema file changes) was documented in
that session and never opened as a PR. (session history)

### Prior drift incidents — this has happened before

This is the **third** drift incident in five days:

- **2026-04-17, migration 0008** (`0008_external_task_id_column.sql`, PR #146).
  Lambda deployed with code expecting `external_task_id`; migration never ran;
  every `threads` query failed with `column does not exist`. User: *"did you
  not run the database migration?"* Session `d1432838` confirmed the gap and
  applied manually. Proposed deploy-gate fix was deferred. (session history)
- **2026-04-18, migration 0012 collision** (`0012_eval_seed_unique.sql`).
  `pnpm db:generate` auto-assigned `0012` while an existing `0012_...sql` from
  a concurrent PR already occupied that slot. Session `6326e70b` detected and
  renamed to `0013`. Different failure mode (collision vs. drift) but same
  root — no tooling coordinates hand-rolled `.sql` files with the journal.
  (session history)
- **2026-04-21, migrations 0018 + 0019.** Unit 8 of the composable-skills
  plan shipped `0019_webhook_skill_runs.sql` (PR #363). Operator applied to
  dev and got `ERROR: relation "skill_runs" does not exist` — because
  `0018_skill_runs.sql` (PR #334, Unit 4) had silently never been applied.
  Both ran cleanly once applied in order.
- **2026-04-26, migrations 0036 through 0039.** A main deploy failed its
  `Migration Drift Check` after `0036_user_scoped_memory_wiki.sql` had already
  been applied to dev because the migration declared FK constraints with
  `-- creates:` markers. The reporter treats `-- creates:` as a relation lookup
  for tables/indexes, so it reported the constraints as missing. The source fix
  changed those rows to `-- creates-constraint: public.<table>.<constraint>`,
  hardened the migration for re-runs, and passed deploy. The next main deploy
  then failed on newer unapplied manual migrations
  `0037_user_profile_operating_model.sql`, `0038_activation_sessions.sql`, and
  `0039_activation_apply_outbox.sql`; applying those additive migrations to dev
  and rerunning the failed jobs cleared the deploy.

Three incidents, same root, same named-but-unshipped fix. The compound-
engineering failure mode: the April 17 learning wasn't captured in
`docs/solutions/`, so it wasn't available when the April 18 session hit a
related symptom, nor when the April 21 session shipped its own "apply
manually" migration without registering the convention's risks.

## Guidance

For any `.sql` file added to `packages/database-pg/drizzle/` that is **not**
registered in `meta/_journal.json`, adopt all three of the practices below.
They're complementary: each catches the failure at a different stage.

### 1. Make the header template required

The `0019_webhook_skill_runs.sql` header pattern is the right shape and
should be required for every unindexed `.sql` file:

- Purpose statement (one line).
- Reference to the owning plan/PR.
- Literal `psql` invocation the operator should run.
- Pre-flight invariant expressed in SQL — one `SELECT to_regclass(...)` (or
  `pg_get_indexdef`, `information_schema.columns` lookup) per object the
  migration creates or requires, with the expected result.
- Drift markers using the correct marker kind for each object:
  - `-- creates: public.<table_or_index_name>` for tables and indexes probed
    through `to_regclass`.
  - `-- creates-column: public.<table>.<column>` for columns.
  - `-- creates-constraint: public.<table>.<constraint>` for CHECK, FK, and
    other table constraints.
  - `-- creates-extension: <extension>` for extensions.

`0012_eval_seed_unique.sql` violates this (no header at all), which is why
it could escape both the journal and any mental model of the manual track.

#### 2026-04-26 update: constraints are not `creates`

The drift reporter has distinct marker probes. Use them literally. A foreign
key named `public.threads_user_id_users_id_fk` is not a relation, so declaring
it as `-- creates: public.threads_user_id_users_id_fk` will always look missing
even when the FK exists. The correct marker includes both the table and
constraint:

```sql
-- creates-constraint: public.threads.threads_user_id_users_id_fk
```

The same applies to CHECK constraints. `0035_workspace_review_decisions.sql` is
the local example to copy:

```sql
-- creates-constraint: public.agent_workspace_events.agent_workspace_events_type_check
```

When a drift failure reports only constraint-looking names as missing, inspect
the marker kind before assuming the DB object is absent.

### 2. PR checklist item: apply to dev before merging

Every PR that adds or modifies a file in `packages/database-pg/drizzle/`
that isn't referenced by `meta/_journal.json` requires:

- [ ] An `Apply manually:` header comment with the exact psql command.
- [ ] A `to_regclass` (or equivalent) pre-flight check naming every object
      the migration creates or depends on.
- [ ] Correct drift marker kinds: tables/indexes use `creates`, columns use
      `creates-column`, constraints use `creates-constraint`, extensions use
      `creates-extension`.
- [ ] Application against dev **before merging** — paste the resulting
      `\d+ <table>` or equivalent into the PR description.
- [ ] Confirmation that `bash scripts/db-migrate-manual.sh` against dev
      reports no MISSING objects for the new migration.

Timing matters. Apply-before-merge means the consumer (another session, a
different feature, a Lambda deploy) never hits a `relation does not exist`
error traced to an unapplied PR. Apply-after-merge means the window between
merge and apply is a production-shaped risk. (session history: incidents
0008, 0018/0019 both showed post-merge discovery.)

#### 2026-04-25 update: post-merge drift gate behavior

PR #594 added `0033_agent_operation_leases.sql` for Phase D folder-bundle
imports. The code, Lambda route, and Terraform changes merged cleanly, and
Terraform Apply succeeded. The deploy still finished red the first time because
`migration-drift-check` correctly reported the new hand-rolled migration as
missing from dev.

The recovery sequence was:

```bash
psql "$DATABASE_URL" -f packages/database-pg/drizzle/0033_agent_operation_leases.sql
DATABASE_URL="$DATABASE_URL" bash scripts/db-migrate-manual.sh
gh run rerun <deploy-run-id> --failed
```

The rerun passed once the migration created `agent_operation_leases`,
`folder_bundle_import_rate_limits`, and the declared indexes. This is the
intended fail-closed behavior: the gate caught that the database had not caught
up to `main`, but it also means the best operator flow is still apply manual
migrations to dev before merging when the PR introduces unindexed SQL.

#### 2026-04-26 update: chained manual migrations after a green deploy

A deploy can pass the drift gate for one source revision and the next deploy can
fail minutes later if a newer PR adds additional hand-rolled migrations without
applying them to dev. This happened after the `0036` marker fix deployed
successfully: the following deploy failed because `0037`, `0038`, and `0039`
were present on `main` but their objects were still missing in dev.

The recovery runbook was:

```bash
source scripts/smoke/_env.sh >/dev/null
psql "$DATABASE_URL" -v ON_ERROR_STOP=1 -f packages/database-pg/drizzle/0037_user_profile_operating_model.sql
psql "$DATABASE_URL" -v ON_ERROR_STOP=1 -f packages/database-pg/drizzle/0038_activation_sessions.sql
psql "$DATABASE_URL" -v ON_ERROR_STOP=1 -f packages/database-pg/drizzle/0039_activation_apply_outbox.sql
pnpm db:migrate-manual
gh run rerun <deploy-run-id> --failed
```

If `gh run rerun --failed` says the workflow is already running, check the run
attempt: GitHub may already have started the failed-job rerun. Keep watching
until `Migration Drift Check` and `Deploy Summary` both complete successfully.

### 3. Ship a drift reporter: `scripts/db-migrate-manual.sh`

A small read-only script that walks `drizzle/*.sql`, excludes files in
`meta/_journal.json`, and probes each file's declared object markers against
`$DATABASE_URL`, reporting APPLIED / MISSING per object. It should understand
different object families rather than treating every marker like a relation:
tables/indexes via `to_regclass`, columns via `information_schema.columns`,
constraints via `pg_constraint`, and extensions via `pg_extension`.

See Examples below for a minimal implementation. A fancier variant adds a
`_manual_migrations(filename, applied_at, sha256)` bookkeeping table and
turns the script into an idempotent applier. But even the read-only
reporter closes the loop that bit us three times.

### 4. (Structural fix) Add a migration step to `deploy.yml`

Named in session `3fdbdb0c` on 2026-04-17 and never shipped. The same fix
applies to both tracks: after a PR merges that touches schema files or
unindexed `.sql`, run the manual-migration reporter (and, separately, a
`drizzle-kit migrate` step for journal-tracked files) against the target
environment before deploying Lambda code that depends on the schema
changes. This is Phase 2 relative to practices 1–3 — shipping this alone
without the header/pre-flight discipline would just move drift discovery
from runtime to CI without explaining what to do about it. (session
history)

## Why This Matters

**Concrete cost of the 2026-04-21 drift.** One plan unit's worth of
cross-session debugging: operator hit a runtime error, re-opened the prior
unit's PR, located the unapplied migration, confirmed ordering against
0019, then applied both. During that window, Unit 8's webhook Lambdas
could not function end-to-end in dev. Unit 8's verification was blocked on
an artifact from Unit 4.

**Compound cost across incidents.** Three drifts in five days. Each
individually is small (minutes to diagnose, seconds to fix). Aggregated,
the pattern eats a compounding amount of attention because each incident
looks like a new bug until the pattern is named.

**Scaling cost.** With 4 enterprises × 100+ agents × ~5 templates imminent
(auto memory `enterprise_onboarding_scale`), the dev database is about to
be inspected by many concurrent sessions. Silent drift between dev and
main's migration tree will produce one-off `relation does not exist`
errors in unrelated features' stack traces, making attribution expensive.
Manual migrations are the only class of schema change where the
tree-to-DB delta isn't self-evident — everything else (drizzle-kit
journal, app code, Terraform) has a plan/diff/apply cycle.

**Why capture this now, on the third incident.** The April 17 session
identified the exact fix (deploy.yml migration step) and deferred it. The
April 18 session hit an adjacent drift vector and didn't re-surface the
April 17 fix. The April 21 session shipped a new migration under the same
convention without knowing it was the third time. Capturing the pattern
here means the next author sees it before authoring migration #4.

## When to Apply

Applies when all of these are true:

- A PR modifies `packages/database-pg/drizzle/**`.
- The modification adds or changes a `.sql` file that is **not** in
  `meta/_journal.json` (i.e., `pnpm db:generate` was not the source).
- The migration needs to be applied to an environment the author doesn't
  own locally (dev, staging, prod).

Does **not** apply to:

- Drizzle-kit auto-generated migrations (journal + `db:push` handle these,
  modulo the `db:push` hang — a separate problem).
- Schema-only changes that don't need DDL (editing `src/schema/index.ts`
  without running the generator).
- One-off data-fix scripts under `scripts/migration/`.

## Examples

### The header pattern (good)

`packages/database-pg/drizzle/0019_webhook_skill_runs.sql`:

```sql
-- Unit 8 — webhook ingress pattern + tenant system-user actor.
--
-- See docs/plans/2026-04-21-003-feat-composable-skills-with-learnings-plan.md
-- (Unit 8). Two changes:
--   1. skill_runs.triggered_by_run_id — optional link from one reconciler
--      tick back to the run that spawned the completed task.
--   2. tenant_system_users — one stable uuid per tenant.
--
-- Apply manually (matches 0018_skill_runs.sql convention):
--   psql "$DATABASE_URL" -f packages/database-pg/drizzle/0019_webhook_skill_runs.sql
--
-- Pre-migration invariants:
--   - skill_runs exists (applied via 0018_skill_runs.sql).
--   - tenant_system_users does not exist yet.
```

### The header pattern (bad — what to avoid)

`packages/database-pg/drizzle/0012_eval_seed_unique.sql`:

```sql
-- Idempotency for the seedEvalTestCases mutation. Without this, calling
-- seedEvalTestCases twice would duplicate every yaml-seed row.
```

No "Apply manually" marker, no pre-flight check, no signal that this file
bypasses drizzle-kit. A reader encountering it cold has no way to know
whether it's been applied.

### Pre-flight as executable SQL (what 0019 should have included)

Instead of describing the invariant in prose (`skill_runs exists (applied
via 0018_skill_runs.sql)`), express it in SQL that fails-closed:

```sql
-- Pre-flight: enforce invariants before touching anything.
DO $$
BEGIN
  IF to_regclass('public.skill_runs') IS NULL THEN
    RAISE EXCEPTION 'pre-flight: skill_runs missing — apply 0018_skill_runs.sql first';
  END IF;
  IF to_regclass('public.tenant_system_users') IS NOT NULL THEN
    RAISE EXCEPTION 'pre-flight: tenant_system_users already exists — is this a re-apply?';
  END IF;
END $$;
--> statement-breakpoint
```

The error on 2026-04-21 would then have been *"apply 0018_skill_runs.sql
first"* — not a CREATE INDEX mid-statement `relation does not exist` six
statements into the migration.

### `scripts/db-migrate-manual.sh` (proposed)

Minimal read-only drift reporter. Does not apply anything; reports which
unindexed files exist and which of their declared objects are missing from
the target DB.

```bash
#!/usr/bin/env bash
# Report which manual-apply drizzle migrations have NOT landed in $DATABASE_URL.
# Complements scripts/db-push.sh (which only runs drizzle-kit-tracked migrations).
set -euo pipefail

REPO_ROOT="$(cd "$(dirname "$0")/.." && pwd)"
DRIZZLE_DIR="$REPO_ROOT/packages/database-pg/drizzle"
JOURNAL="$DRIZZLE_DIR/meta/_journal.json"

: "${DATABASE_URL:?set DATABASE_URL or source scripts/db-push.sh env}"

mapfile -t indexed < <(python3 -c "
import json
j = json.load(open('$JOURNAL'))
for e in j['entries']:
    print(e['tag'] + '.sql')
")

echo "Unindexed .sql files in $DRIZZLE_DIR:"
for f in "$DRIZZLE_DIR"/*.sql; do
  base="$(basename "$f")"
  if printf '%s\n' "${indexed[@]}" | grep -qx "$base"; then
    continue
  fi
  echo "  $base"
  grep -oE "to_regclass\('public\.[a-z_]+'\)" "$f" | sort -u | while read -r call; do
    result=$(psql "$DATABASE_URL" -tAc "SELECT COALESCE($call::text, 'MISSING')")
    echo "    $call -> $result"
  done
done
```

On the drifted dev DB at 2026-04-21, sample output would have been:

```
Unindexed .sql files in .../drizzle:
  0012_eval_seed_unique.sql
  0018_skill_runs.sql
    to_regclass('public.skill_runs') -> MISSING
  0019_webhook_skill_runs.sql
    to_regclass('public.tenant_system_users') -> MISSING
```

That single signal, wired into a daily cron or a pre-unit-kickoff
checklist, would have caught 0018 before 0019's author typed `psql`.

## Related

- `docs/solutions/workflow-issues/skill-catalog-slug-collision-execution-mode-transitions-2026-04-21.md`
  — Unit 8's other migration-adjacent learning (about slug renames + agent_skills rows, not DDL).
  Same Unit 8 surface, orthogonal concern.
- `docs/plans/2026-04-21-003-feat-composable-skills-with-learnings-plan.md`
  — Unit 4 (line 679) + Unit 8 (line 863) introduced `0018_skill_runs.sql`
  and `0019_webhook_skill_runs.sql` respectively.
- `packages/database-pg/drizzle/meta/_journal.json` — authoritative list of
  drizzle-kit-tracked migrations; does not mention `0012_eval_seed_unique`,
  `0018_skill_runs`, or `0019_webhook_skill_runs`.
- `scripts/db-push.sh` — journal-track applier; hangs on `drizzle-kit push`
  schema introspection against live cluster (session history: Apr 17 + Apr 18).
- auto memory `feedback_avoid_fire_and_forget_lambda_invokes` — adjacent
  pattern: surface errors at author time, not consumer time. Same spirit
  applied to migrations: apply before merge, not after.
