Schema drift between schema.sql and migrations causing recurring papercuts

[barrulus]

Problem

There is meaningful drift between database/schema.sql, database/migrations/001_llm_dm_pivot.sql, and database/migrations/005_action_status_extension.sql. They disagree on:

• Status enums for session_player_actions (different allowed values across files)
• Column names — e.g. action_data vs action_payload

This is the underlying source of several recurring bugs we keep patching one-off (status mismatches, INSERTs targeting the wrong column, etc.). Each fix unblocks the immediate symptom but leaves the foot-gun in place for the next person.

Proposal

Consolidate the schema so that:

1. schema.sql is regenerated from the live DB (or from the full migration chain) and becomes the single source of truth for new environments.
2. Migrations 001 and 005 are reconciled — pick one canonical column name (action_data vs action_payload) and one canonical status enum, then add a migration that normalises any remaining drift.
3. All server code (routes, services, interceptors) is audited to use the canonical names.

Related

• Overlaps with Regenerate database/schema.sql from live DB — drift from migrations #24 (Regenerate database/schema.sql from live DB — drift from migrations). This issue is the why behind Regenerate database/schema.sql from live DB — drift from migrations #24 — the recurring action-status / action-payload bugs are the concrete cost.

Not urgent

Filed as a follow-up — not blocking current session work, but worth doing before the next round of action-pipeline changes so we stop paying the same tax.

[barrulus]

Schema drift audit — session_player_actions

Examined database/schema.sql, database/migrations/001_llm_dm_pivot.sql, database/migrations/005_action_status_extension.sql, and the server code that touches the table. Here is the full inventory of drift on this one table.

1. Payload column name: action_data vs action_payload

Source	Column
database/schema.sql:1209	action_payload JSONB NOT NULL DEFAULT '{}'
database/migrations/001_llm_dm_pivot.sql:93	action_data JSONB NOT NULL DEFAULT '{}'::jsonb
server/routes/actions.routes.js:127	INSERTs into action_payload
server/routes/actions.routes.js:433	reads action.action_payload
server/services/chat/action-interceptor.js:294	INSERTs into action_data

Impact: depending on which DDL was applied first, one of these two INSERT paths is silently broken. The chat-action interceptor and the REST action route disagree about the column name — they cannot both be correct against the same live DB.

2. Status enum: three different sets

Source	Allowed values
database/migrations/001_llm_dm_pivot.sql:94-95	pending, awaiting_roll, resolved, cancelled
database/migrations/005_action_status_extension.sql:18	pending, processing, awaiting_roll, resolved, cancelled, failed
database/schema.sql:1213	pending, processing, awaiting_roll, **completed**, failed

Two distinct conflicts:

• schema.sql uses completed; both migrations use resolved. These are mutually exclusive — a check constraint cannot accept both.
• schema.sql is missing cancelled; both migrations include it.

The server code consistently writes 'resolved' (e.g. actions.routes.js:218,252,407, chat/action-interceptor.js:355). So schema.sql is the outlier — any environment built from schema.sql rather than the migration chain will reject every action resolution.

Migration 005 also drops/recreates the constraint by name session_player_actions_status_check, which assumes the 001 form exists. If the table was created from schema.sql (whose constraint may have a different generated name), the DROP CONSTRAINT IF EXISTS silently no-ops and the old completed-based constraint stays in place — leaving the live DB in a state that doesn't match either file.

3. Required FK columns: user_id / player_id

Source	Required FKs
database/schema.sql:1201-1202	user_id UUID NOT NULL, character_id UUID NOT NULL — no player_id
database/migrations/001_llm_dm_pivot.sql:90-91	player_id UUID NOT NULL (→ campaign_players), character_id UUID (nullable) — no user_id
server/routes/actions.routes.js:127	INSERTs (session_id, campaign_id, player_id, user_id, character_id, ...) — assumes BOTH exist
server/services/chat/action-interceptor.js:294	INSERTs (session_id, campaign_id, player_id, user_id, character_id, ...) — assumes BOTH exist

Impact: the server code expects a hybrid schema with both player_id and user_id, which neither file alone defines. The live DB must have been hand-patched, or one of the migrations is missing an ALTER TABLE ADD COLUMN step.

Related: character_id is NOT NULL in schema.sql but nullable (ON DELETE SET NULL) in 001. The ON DELETE behaviour also differs.

4. Other column drift on the same table

Column	schema.sql	001_llm_dm_pivot.sql
round_number	present	present
turn_number	absent	present (line 101)
narrative	absent	present (line 99)
roll_request	absent	present (line 96)
created_at type	TIMESTAMPTZ	TIMESTAMP WITH TIME ZONE (equivalent, but inconsistent style)

schema.sql also has an extra ALTER TABLE block at lines 1267-1275 that re-asserts the action_type check constraint — duplicating the inline CHECK at line 1204. This is dead-but-harmless, except it means the constraint name is fixed, which interacts badly with the rename pattern in #5.

5. Index drift

Index	schema.sql	001_llm_dm_pivot.sql
idx_spa_session_round (session_id, round_number, created_at DESC)	yes	no
idx_spa_user_session (user_id, session_id)	yes (depends on user_id)	no
idx_spa_session_id (session_id)	no	yes
idx_spa_player_id (player_id)	no	yes
idx_spa_status (...)	yes	yes (identical)
idx_spa_campaign_id	yes	yes (identical)

So depending on which file you build from, you get a different set of indexes — even setting aside the column-existence issues that would make some of them invalid.

---

Summary of root causes

1. schema.sql was never regenerated after migration 001 was authored — it's a parallel hand-maintained schema that has diverged on column names (action_payload vs action_data), status values (completed vs resolved), and which FKs are required.
2. Migration 005 patches the 001 form of the constraint and silently no-ops against the schema.sql form — so applying 005 to a schema.sql-built DB leaves drift in place.
3. The server code is inconsistent with itself: actions.routes.js uses action_payload, chat/action-interceptor.js uses action_data. One of these is broken in every environment.

Minimum fix to stop the bleeding

1. Pick canonical names — recommend action_payload (matches REST route, the older path) and resolved (matches all server code).
2. Write migration 006_normalise_action_columns.sql that:
   • ALTER TABLE ... RENAME COLUMN action_data TO action_payload (idempotent guard)
   • Drops + re-adds the status check constraint with the canonical six values
   • Adds any missing columns (turn_number, narrative, roll_request) so both shapes converge
3. Fix server/services/chat/action-interceptor.js:294 to use action_payload.
4. Regenerate database/schema.sql from the live DB (closes Regenerate database/schema.sql from live DB — drift from migrations #24) and delete the hand-maintained version.
5. Audit for the same pattern on other tables — session_player_actions is unlikely to be the only one.

[barrulus]

Closing as duplicate of #24. #26 explicitly links #24 as the what behind the recurring action-status / action-payload papercuts — same root cause (schema.sql is hand-edited and drifts from migrations), so consolidating the work there.

Anything specific to #26 (column name reconciliation action_data vs action_payload, status enum normalisation) will be picked up in the #24 audit pass.