Husariaalpha

Operating Husaria

Migrations

Migrations #

Husaria exposes two parallel migration surfaces, both targeting the same Postgres database. Migrations are distinct from the metadata import/export path — migrations apply schema DDL, metadata replaces the contents of the husaria_data.* configuration tables.

File-based migrations #

Numbered SQL files under migrations/ in the repo root, applied in order:

  • 001_create_husaria_schema.sql — the husaria_data schema + actions tables.
  • 002_authorization.sql — roles, role_inheritance, table_permissions.
  • 004_create_actions_tables.sql — action runtime tables (actions, action_permissions, action_logs, action_types). See Actions.
  • 006_enable_pg_stat_statements.sql — enables the pg_stat_statements extension required by the console Performance tab.
  • 007_create_delegations_tables.sql — delegations, delegation_endpoints, delegation_permissions. See Delegations.
  • 009_create_event_triggers_tables.sql — husaria_event_triggers, husaria_event_invocation_logs under the public schema. Both tables use CREATE IF NOT EXISTS + DO $$ ... ALTER ... $$ guards so a pod restart on an upgraded DB is silent. See Events.

Applied at server start when HUSARIA_AUTO_MIGRATE=true under Postgres advisory lock 9876543210. The default posture is operator-driven — run files manually or via husaria-cli migrate up.

Idempotency guarantees #

Every file is safe to re-run against an already-migrated database:

  • All CREATE INDEX statements use IF NOT EXISTS.
  • Triggers use DROP TRIGGER IF EXISTS followed by CREATE TRIGGER (Postgres does not accept CREATE TRIGGER IF NOT EXISTS).
  • INSERT statements that seed sample data carry ON CONFLICT … DO NOTHING.

A clean pod restart with HUSARIA_AUTO_MIGRATE=true records the first-time application in schema_migrations and is a silent no-op on subsequent boots.

Multi-statement file parsing #

The migration runner's statement splitter is a small state machine, not a naive ; split. It correctly handles:

  • $tag$ … $tag$ dollar-quoted strings — including CREATE FUNCTION bodies that contain ;.
  • Single and double-quoted strings, including doubled-quote escapes ('', "").
  • Line comments (-- …) and nested block comments (/* … */).

A CREATE FUNCTION … LANGUAGE plpgsql AS $$ BEGIN … END; $$ body now applies cleanly instead of being split into broken halves.

Sample fixtures live elsewhere #

The migrations/samples/ subdirectory holds the social-media demo schema (003_create_sample_schema.sql) and its seed inserts (005_insert_sample_data.sql). The filesystem loader does not scan subdirectories, so HUSARIA_AUTO_MIGRATE will never apply these by accident in production. Operators who want the demo dataset apply them manually:

psql -h $HUSARIA_DB_HOST -U $HUSARIA_DB_USER -d $HUSARIA_DB_NAME \
  -f migrations/samples/003_create_sample_schema.sql \
  -f migrations/samples/005_insert_sample_data.sql

Console-managed migrations #

Stored as rows in husaria_data.migrations. Each row carries up_sql and down_sql and is applied or rolled back through the console UI.

Apply path #

  1. The SQL runs in a transaction. Failure rolls the DB back and leaves the row pending with an error message.
  2. On success the row flips to applied with a timestamp.
  3. The console invokes SchemaReloader — the schema slot is rebuilt and atomically swapped.
  4. rls.Registry is reloaded from husaria_data.table_permissions.

GraphQL queries hitting /v1/graphql see the new schema on the next request; in-flight subscription cohorts see it on the next poll tick. No process restart, no client reconnect.

Rollback path #

Rollback intentionally does not delete the migration row. It marks it back to pending and re-opens it for editing — the operator can fix the SQL and re-apply without losing the audit trail.

Bootstrap #

The husaria_data schema is bootstrapped idempotently at every startup from embedded SQL — but only on the primary. When HUSARIA_READ_ONLY=true (see Read-only replicas) the bootstrap step is skipped: the standby's catalog is replicated from the primary via WAL, so CREATE SCHEMA / CREATE TABLE would only produce SQLSTATE 25006 (read-only transaction) and flood logs without changing state.

A fresh primary Postgres is therefore usable without an explicit migration step. A replica needs the schema already present on the primary before it can serve traffic.

Choosing an approach #

  • Use file-based migrations when migrations live in source control and should be reviewed in pull requests. They are forward-only — no down-migration file shipped.
  • Use console-managed migrations when an operator needs to ship a small fix without a code deploy. They are still recorded in Postgres and surfaced via the API.

What's intentionally missing #

  • No down-migrations file. Console-managed rollback is a state flip plus reopened SQL (the down_sql column holds the rollback statement). File-based migrations are forward-only — the model is "fix forward".
  • No squash / merge tooling. The migration list grows linearly with the project.
  • No remote-source migrations (S3, git URL, etc.). Files are read from HUSARIA_MIGRATIONS_PATH.
  • No subdirectory recursion by design — migrations/samples/ and similar directories stay out of the automatic apply path.