Database & migrations

ADMINISTRATOR ::: danger Restricted

:::

D1 instances

Binding	DB name	ID	Purpose
DB	pms-db	4e00e088-b574-4f81-9b0c-bd41c6264034	Primary application DB (~83 tables as of v2.12.9)
RAG_DB	rag-corpus	(separate)	RAG chunks, FTS5 index, mandatory-class column

R2

Binding	Bucket	Purpose
BACKUPS	pms	Daily D1 export, Vectorize export, FLUX images (since v2.12.15)

Vectorize

Binding	Index	Model
VEC	(account-scoped)	EMBEDDING_MODEL env var

Queue

Queue	DLQ	Purpose
chain-c-steps	chain-c-steps-dlq	Deep-extract Chain C

Migration cadence

• All migrations live in migrations/ (pms-db) and migrations/rag-corpus/ (rag-corpus)
• Numbered NNNN_description.sql. Current head: 0104_audit_log.sql (pms-db)
• Every migration writes a sentinel row in kv_state so wrangler d1 migrations apply is idempotent
• npm run db:migrate runs the apply. Deploy script runs it before wrangler deploy (sentinel-gated)

Critical tables

Foundation

• ucs_foundation_versions — (id, label, is_active, created_at). Exactly one row has is_active=1 at any time.
• ucs_foundation_files — chunked base64 of imported Master XLSX, keyed by (version_id, kind). Kinds: master_full, master_slim, manifest, l4, source, plus the new hierarchy_atlas (v2.31.0.23 task per project_hierarchy_atlas memory).
• ucs_master_list — leaves of the active foundation. (version_id, code, component_name, dept, ...).
• ucs_codes — flat code registry.
• code_history — audit trail of cascade renames/moves/splits/merges/deletes for backwards-compatible reads.

Vessel data

• vessels — (id, name, imo, dwt, flag, class, type, particulars_pdf_id).
• vessel_particulars — JSON blob for general particulars + equipment + tanks (nested). source_doc_ids[] JSON array (added by migration 0100).
• vessel_particulars_provenance — (vessel_id, field_path, source_document_id, provenance_quality) per field.
• vessel_components — per-vessel CL rows.
• vessel_assignments — superintendent ↔ vessel.
• field_overrides_log — diff log when a doc-prevails override flips a populated field.

Source documents

• source_documents — (id, vessel_id, wizard_slot, g1_ucs_code, status, file_key, ...). Statuses: uploaded | processed | archived.
• source_documents_archive — parallel snapshot. superseded_by_source_document_id (added by migration 0099).

Knowledge / RAG

• cl_knowledge_base — corrective entries. quarantined column added by migration 0089.
• rag_chunks (in RAG_DB) — (id, vessel_id, source_document_id, doc_type, content, embedding_id, mandatory_class, ...).

CL skeleton

• cl_skeleton_runs — (id, vessel_id, status, master_source, master_descriptor_id, master_version, options_json). master_list_id relaxed NOT NULL → NULL by migration 0097.
• cl_skeleton_wizard_state — wizard step state.
• cl_skeleton_audit — per-run audit trail.

Audit / messenger

• audit_log — generic (entity_type, entity_id, action, before, after, by_user_id) (migration 0104).
• message_threads, messages, message_recipients — in-app messenger (migration 0069).
• ai_calls — per-AI-call telemetry.
• users — (id, username, password_hash, role, ...). Password: PBKDF2-SHA256 100K iters + salt.

Recent migration highlights

#	What
0064	Drop WhatsApp tables (deploy_notifications, pending_digest, users.notify_on_deploy)
0069	Messenger threads + recipients
0082	Partial unique idx on (vessel_id, source_document_id) WHERE edited_by='system:source-doc-bridge'
0083	field_overrides_log
0084	Rename 'admin' → 'administrator'
0085	vessels.particulars_pdf_id (separate, idempotent)
0086	components.drawing_refs JSON-array
0087	Drawing-refs rerun sentinel
0089	cl_knowledge_base.quarantined
0090–0095	CL skeleton tables
0097	cl_skeleton_runs.master_list_id NOT NULL → NULL
0098 (rag-corpus)	rag_chunks.mandatory_class
0098 (pms-db)	vessel_particulars_provenance
0099	source_documents_archive.superseded_by_source_document_id
0100	vessel_particulars.source_doc_ids
0104	audit_log

Troubleshooting

• Phantom column errors (D1_ERROR: no such column: X) — search the source for SELECT X references; add a migration if the column should exist, or drop the SELECT if the column was never created. See v2.31.0.30 (source_documents.classification).
• Migration applied but app still 500s — check the sentinel row in kv_state. The migration file may have been edited after first apply.
• rag-corpus drift — RAG_DB migrations live in migrations/rag-corpus/, applied via a separate wrangler d1 migrations apply rag-corpus --remote step.