SQL migrations 001-013

The registry is the PostgreSQL database ingest, owned by the role ingest_app. Its tables are created by the files in infra/sql/, applied in name order by install step 14. Every file is safe to re-run (IF NOT EXISTS, ADD COLUMN IF NOT EXISTS).

To add a migration: create infra/sql/0NN-what-it-does.sql with a header comment saying what and why, run bash infra/deploy.sh, then on the server bash /opt/lake/scripts/install-all.sh 14. Never change an applied migration; add a new one.

#	File	Adds	Why
001	001-registry.sql	vendors, vendor_users	Who the vendors are; which Keycloak login belongs to which vendor. Passwords live only in Keycloak. vendors.code is the future table-name prefix
002	002-projects.sql	projects, folders	Projects per vendor and folders (= datasets) per project. The table name is derived, never stored
003	003-files.sql	files, file_events	Every upload and its audit trail. Moves the old FastAPI files table aside as legacy_files if present
004	004-processing.sql	files.stage, files.status_message	What the worker is doing now, shown live to users
005	005-analysts.sql	analysts, analyst_projects, sql_audit	Registered analysts, the projects each may read, and a log of every query
006	006-export-audit.sql	sql_audit.kind, sql_audit.bytes	The SQL log also records CSV exports and their size
007	007-views.sql	views, view_inputs, view_runs, view_apis, api_keys, api_requests	Saved views, their lineage and builds, published APIs, per-view keys and the request log
008	008-audit.sql	audit_log and indexes	Who changed what, plus the indexes that keep every audit search to one index range
009	009-analyst-vendors.sql	analyst_vendors	Give an analyst a whole vendor, including projects created later
010	010-access-keys.sql	access_keys, api_requests.access_key_id	Reusable API keys with scope owner, vendor, project or global
011	011-file-removal.sql	new files.status values	Archive, delete and restore: archiving, archived, deleting, deleted (the worker’s removal queue)
012	012-push-keys.sql	push_keys	Keys that let a vendor’s system create projects and push files; separate from read keys on purpose
013	013-push-requests.sql	push_requests	A log of every push API call, including refused ones; no key material

Tables by purpose

Purpose	Tables
Who	vendors, vendor_users, analysts, analyst_projects, analyst_vendors
What is stored	projects, folders, files, file_events
Views and APIs	views, view_inputs, view_runs, view_apis
Keys	api_keys (per view), access_keys (reusable, read), push_keys (write)
Logs	audit_log, sql_audit, api_requests, push_requests, file_events
Retired (still present)	legacy_files, events, published_apis, api_audit from the old intake

Log retention

audit_log and sql_audit are purged after 365 days, api_requests after 90 days, in small batches by the API (AUDIT_RETENTION_DAYS, API_LOG_RETENTION_DAYS). file_events is never purged: it is a file’s history and the base for tracing a row back to its source.

Look inside

On the server:

sudo -u postgres psql -d ingest -c '\dt'
sudo -u postgres psql -d ingest -c '\d files'
sudo -u postgres psql -d ingest -c "select status, count(*) from files group by status"