Database (Aurora DSQL)

The AWS backend (apps/backend/aws) stores platform data in Amazon Aurora DSQL. DSQL speaks the PostgreSQL wire protocol but differs from PostgreSQL in ways that shape the setup: there is no password (every connection uses a short-lived IAM token), one database (postgres), one DDL statement per transaction, asynchronous index builds, and no enums or array columns. The Prisma schema in packages/api/prisma/schema/ stays the schema of record for every target; DSQL gets its own apply pipeline.

Two components talk to the database at runtime: the API (apps/backend/aws/api on Lambda, or apps/backend/aws/api-ecs on ECS) and the file tracker Lambda (apps/backend/aws/file-tracker). A third component, the migration job (apps/backend/aws/migration), is a one-off ECS Fargate task that applies the committed migrations, maps and grants the runtime role and applies the audit privilege boundary.

Environment contract

The presence of DSQL_CLUSTER_ENDPOINT selects DSQL; without it the API and file tracker keep reading DATABASE_URL from SSM under SECRET_PREFIX as before. All three components share the same validation (packages/aws-data/src/dsql.rs, mirrored in apps/backend/aws/migration/shared.ts).

Variable	API / file tracker	Migration job
DSQL_CLUSTER_ENDPOINT	required, public or PrivateLink cluster hostname	required, same formats
DSQL_REGION	optional, must match the endpoint	optional, must match the endpoint
DSQL_USER	database role, default admin; production uses flow_like_api	always admin
DSQL_TOKEN_DURATION_SECS	token lifetime, default 3600 (1800–604800)	fixed 900, minted per connection
DSQL_MAX_CONNECTIONS	pool size, default 4 (32 for the ECS API)	n/a
DSQL_RUNTIME_ROLE_ARNS	n/a	optional, comma-separated IAM role ARNs of the API and file-tracker Lambdas, all mapped to DSQL_RUNTIME_DB_ROLE in one run; unset skips the mapping with a warning
DSQL_RUNTIME_DB_ROLE	n/a	optional, default flow_like_api
DSQL_AUDIT_ROLE_ARN	n/a	optional, the audit worker Lambda’s IAM role ARN; must not appear in DSQL_RUNTIME_ROLE_ARNS
DSQL_AUDIT_DB_ROLE	n/a	optional, default flow_like_audit_worker; must differ from DSQL_RUNTIME_DB_ROLE
DSQL_MIGRATIONS_DIR	n/a	optional, default prisma/migrations-dsql (what the image ships)
DSQL_SCHEMA_DIR	n/a	optional, default prisma/schema; a checkout requires the generated PostgreSQL mirror
DSQL_JOB_WAIT_TIMEOUT_SECS	n/a	optional, default 7200 (60–86400), budget per wait on sys.jobs

Public endpoints have the form <id>.dsql.<region>.on.aws; PrivateLink connection endpoints use <id>.dsql-<service-id>.<region>.on.aws. Use the private endpoint for all three components when the cluster blocks public access, and run the migration task inside the connected VPC. The public hostname keeps its public route even when the task runs in that VPC.

Forbidden alongside a DSQL endpoint, even when empty: DATABASE_URL, PGPASSWORD, PGPASSFILE, PGSERVICE, PGSERVICEFILE, PGHOST, PGHOSTADDR, PGPORT, PGUSER, PGDATABASE, PGSSLMODE, PGSSLROOTCERT, PGSSLCERT, PGSSLKEY, PGOPTIONS. Every process refuses to start when one is present, so a static credential can never replace the token.

Credentials come from the default AWS chain (the Lambda execution role, the ECS task role). Tokens are checked only when a connection is opened; the Rust pool mints one for DSQL_TOKEN_DURATION_SECS (one hour by default; the 1800-second floor keeps the half-life rotation ahead of the longest Lambda invocation), swaps it at half-life, and retires connections after 25 minutes, well inside DSQL’s 60-minute connection limit. TLS is always verify-full against the public CA chain; never put a TLS-terminating proxy or PgBouncer in front of the cluster.

IAM policies

Runtime role (API and file tracker Lambdas) - connect as a non-admin role only:

{
  "Version": "2012-10-17",
  "Statement": [
    {
      "Effect": "Allow",
      "Action": "dsql:DbConnect",
      "Resource": "arn:aws:dsql:<region>:<account>:cluster/<cluster-id>"
    },
    {
      "Effect": "Allow",
      "Action": "kms:Decrypt",
      "Resource": "arn:aws:kms:<region>:<account>:key/<key-id>",
      "Condition": {
        "StringEquals": {
          "kms:ViaService": "dsql.<region>.amazonaws.com",
          "kms:EncryptionContext:aws:dsql:ClusterId": "<cluster-id>"
        }
      }
    }
  ]
}

Migration task role - admin access, used only by the migration job:

{
  "Version": "2012-10-17",
  "Statement": [
    {
      "Effect": "Allow",
      "Action": "dsql:DbConnectAdmin",
      "Resource": "arn:aws:dsql:<region>:<account>:cluster/<cluster-id>"
    }
  ]
}

The kms:Decrypt statement is required when the cluster uses a customer-managed key. Add it to the migration identity’s policy too, and repeat it for each regional cluster/key pair the identity needs. Missing this permission produces unable to accept connection, access denied; CloudTrail shows the underlying KMS denial. The cluster key’s service policy does not replace the caller’s IAM permission. See the AWS DSQL KMS policy example.

Keep dsql:DbConnectAdmin off the runtime role. The admin database role owns public; the Lambdas need only the grants below.

Runtime grants

The migration job performs this step itself, idempotently, on every run that has DSQL_RUNTIME_ROLE_ARNS set: a comma-separated list of every IAM role that logs in as the runtime database role (DSQL_RUNTIME_DB_ROLE), which is the API Lambda’s role and the file tracker’s when the two differ. One run creates the database role, maps each listed ARN with AWS IAM GRANT, then reconciles the grants once. Without the list - a development cluster that has no runtime role yet - the job applies the schema and logs a warning instead; when the role already exists it still reconciles the grants. The role mapping uses:

CREATE ROLE flow_like_api WITH LOGIN;
AWS IAM GRANT flow_like_api TO 'arn:aws:iam::<account>:role/<api-role>';
AWS IAM GRANT flow_like_api TO 'arn:aws:iam::<account>:role/<file-tracker-role>';

Run the migration job to reconcile table and sequence access. It grants CRUD access to application tables individually and applies the separate audit boundary below. Tables whose names start with _prisma, and _flow_migration_lock, are withheld from the runtime role. The job revokes existing runtime grants on those metadata tables and verifies that the runtime role no longer has access.

Before applying pending migrations, the job removes the runtime role’s old default table grants, both globally and in public. A schema-specific revoke cannot remove a global default grant; see PostgreSQL’s default privilege rules. New application tables receive access during grant reconciliation after the migrations finish. If a run stops early, rerun it to complete those grants. Sequence grants remain available.

The job probes privileges before changing them, so a rerun on an up-to-date cluster emits no grant or revoke. Every GRANT and REVOKE on DSQL is a catalog change, and a warm API session sees one OC001 on its next statement after each one.

The role inherits USAGE on public through PUBLIC. DSQL rejects GRANT USAGE ON SCHEMA public because public is a system schema. The migration job checks inherited access with SELECT has_schema_privilege('flow_like_api', 'public', 'USAGE'); and fails if it is missing.

Check the mapping with SELECT * FROM sys.iam_pg_role_mappings;. The Lambdas then run with DSQL_USER=flow_like_api.

Audit worker role

The audit worker Lambda connects as its own database role with its own IAM role. Run the migration job once with DSQL_AUDIT_ROLE_ARN set to that IAM role: it creates flow_like_audit_worker and maps it. The job refuses an IAM role that is already mapped to flow_like_api, and an ARN that also appears in DSQL_RUNTIME_ROLE_ARNS, because that identity could log in as either role. The worker then runs with DSQL_USER=flow_like_audit_worker.

Once that role exists, every migration run applies the audit privilege boundary, with or without a role ARN. The worker’s grants are reconciled first, then the API’s. The job also removes excess grants left by older deployments. A run that finds the catalog already correct emits no statement.

Role	Audit tables
flow_like_audit_worker	SELECT, INSERT, UPDATE, DELETE on the evidence tables (AuditEntry, AuditRecord, AuditSeal, AuditEpoch, AuditWatermark, AuditArchive, AuditHeldChain, AuditWorkerLease), SELECT, UPDATE on AuditExportTarget, SELECT on AiActAssessment
flow_like_api	SELECT on the evidence tables except AuditWorkerLease (none), plus INSERT on AuditRecord

The job checks the result with has_table_privilege and fails if flow_like_api can still write evidence or if flow_like_audit_worker holds any privilege on a table outside the audit tables. Only the privileges DSQL knows are ever named (SELECT, INSERT, UPDATE, DELETE, REFERENCES); DSQL rejects a REVOKE that names TRUNCATE or TRIGGER. PostgreSQL deployments also withhold the sealId column from the API’s INSERT. On DSQL every GRANT and REVOKE is its own DDL transaction, and swapping the table grant for a column grant would reject record inserts in between, so the API keeps table-level INSERT. A record inserted with an invented sealId fails verification.

When the check passes, the job prints one stable line after its descriptive summary:

[aws-migration] AUDIT_BOUNDARY_VERIFIED api=flow_like_api worker=flow_like_audit_worker

The two values are the configured DSQL_RUNTIME_DB_ROLE and DSQL_AUDIT_DB_ROLE. A pipeline greps the task log for AUDIT_BOUNDARY_VERIFIED as the machine-readable proof that the boundary is in effect. The line is never printed while the worker role does not exist, and a failed check exits 1 without it.

Applying migrations

Build the image from the repository root:

docker build -f apps/backend/aws/migration/Dockerfile -t flow-like-aws-migration .

Run it as a one-off ECS Fargate task under the migration task role. The environment goes in as a container override (the container is called migration in the task definition here):

aws ecs run-task \
  --cluster <ecs-cluster> \
  --launch-type FARGATE \
  --task-definition flow-like-aws-migration \
  --network-configuration 'awsvpcConfiguration={subnets=[<subnet-id>],securityGroups=[<security-group-id>],assignPublicIp=ENABLED}' \
  --overrides '{"containerOverrides":[{"name":"migration","environment":[{"name":"DSQL_CLUSTER_ENDPOINT","value":"<id>.dsql.<region>.on.aws"},{"name":"DSQL_RUNTIME_ROLE_ARNS","value":"arn:aws:iam::<account>:role/<api-role>,arn:aws:iam::<account>:role/<file-tracker-role>"}]}]}'

The same image runs from a workstation whose AWS credentials hold dsql:DbConnectAdmin on the cluster. The migrations are baked into the image under prisma/migrations-dsql, so DSQL_MIGRATIONS_DIR stays at its default:

docker run --rm \
  -e AWS_REGION=<region> \
  -e AWS_ACCESS_KEY_ID -e AWS_SECRET_ACCESS_KEY -e AWS_SESSION_TOKEN \
  -e DSQL_CLUSTER_ENDPOINT=<id>.dsql.<region>.on.aws \
  -e DSQL_RUNTIME_ROLE_ARNS=arn:aws:iam::<account>:role/<api-role>,arn:aws:iam::<account>:role/<file-tracker-role> \
  flow-like-aws-migration

Without the image, straight from the checkout, run the mise task from the repo root. It installs the job’s dependencies, builds the mirrored schema the final prisma migrate status check needs, points DSQL_MIGRATIONS_DIR and DSQL_SCHEMA_DIR at the checkout, and drops the forbidden variables (a workstation almost always has DATABASE_URL set, and the job refuses to start while it is present):

DSQL_CLUSTER_ENDPOINT=<id>.dsql.<region>.on.aws \
DSQL_RUNTIME_ROLE_ARNS=arn:aws:iam::<account>:role/<api-role>,arn:aws:iam::<account>:role/<file-tracker-role> \
mise run db:dsql:migrate

Leave DSQL_RUNTIME_ROLE_ARNS out on a development cluster that has no runtime role yet; the job applies the schema and skips the mapping with a warning. When the API and file tracker share one IAM role, the list has one entry.

The job takes a 30-minute lease in _flow_migration_lock (a second concurrent run exits 3), first waits for any sys.jobs entry still running from an earlier run, then applies every pending migration.sql statement by statement. CREATE TABLE and CREATE INDEX ASYNC overlap with the index builds already running; every other statement - the ADD CONSTRAINT … FOREIGN KEYs, the VALIDATE CONSTRAINTs - first waits for the jobs the migration has submitted so far, because a foreign key cannot reference a unique index that is still building. A statement that hits an OCC conflict is retried, and an “already exists” on the retry counts as applied. Once every statement is committed the row gets applied_steps_count = 1; finished_at is set only after the migration’s async jobs have completed. Before verifying pg_index and pg_constraint the job drains sys.jobs once more, then maps and grants the runtime role, applies the audit privilege boundary and finishes with prisma migrate status against _prisma_migrations. A run that dies while waiting (SIGKILL, the DSQL_JOB_WAIT_TIMEOUT_SECS budget) leaves a row with applied_steps_count = 1 and no finished_at; the next run waits for the jobs and finishes it. Only a row with logs set - a failed statement or a failed job - needs a human. The job does not use prisma migrate deploy: Prisma sends a migration file as one batch, which PostgreSQL runs in one implicit transaction and DSQL rejects. See migration recovery if the run fails.

Run the job before deploying a Lambda revision that needs the new schema. Sessions opened before a schema change see one OC001 conflict on their next statement. Only two paths absorb it: work that runs through retry_transaction, and the audit record insert, which retries a stale catalog up to three times. Every other statement returns the conflict to its caller once, so a real schema migration against a live cluster costs a handful of failed requests from warm sessions; the drift-only grants of a routine run change no schema and cause none.

Migration recovery

Exit code	Meaning and next step
0	Migrations, async jobs, grants, the audit boundary (once the worker role exists) and the final status check succeeded, or the schema was already up to date
1	Inspect the log for a token, statement, async job, catalog, grant, status-check, or wait-timeout failure
2	Correct the rejected environment setting before retrying
3	Another migration holds the lease; wait for that run to finish or for its lease to expire

A _prisma_migrations row with applied_steps_count = 1, no logs, and no finished_at is resumable. Every statement committed, but its asynchronous jobs were not confirmed. Re-run the job: it waits for sys.jobs, verifies that pg_index contains no invalid indexes and pg_constraint contains no unvalidated foreign keys, then finishes the row. Async jobs continue after a wait timeout.

A row with logs and no finished_at blocks later migrations. Read the error and repair the schema. Earlier statements remain committed because DSQL DDL is not transactional. For a failed unique index, repair duplicate data and rebuild the failed index before resolving the migration. Use prisma migrate resolve --applied <name> only after completing the migration by hand. --rolled-back <name> makes the runner apply the whole file again; first account for every statement that already committed. Preserve the checksums of published migrations rather than casually editing their history.

Run resolution commands with PRISMA_SCHEMA_DISABLE_ADVISORY_LOCK=1, the DSQL Prisma configuration, and a short-lived admin-token DATABASE_URL using sslmode=require&sslaccept=strict. These are settings for the manual Prisma process. The migration runner itself rejects DATABASE_URL in its environment.

From a checkout, prefer mise run db:dsql:migrate. A raw runner invocation needs both DSQL_MIGRATIONS_DIR and DSQL_SCHEMA_DIR pointed at the checkout’s committed migrations and generated mirror. The tracked schema still declares cockroachdb. Omitting the mirror path can let all statements succeed and then fail only the final status check. The migration runner is the reference for lease, checksum, and recovery handling.

For local runner checks, run bun install, bun test, bunx tsc --noEmit, and bunx biome check . from apps/backend/aws/migration. These checks do not apply a migration to a cluster.

Developer workflow: generating a migration

Every schema change lands in packages/api/prisma/schema/ first (the CockroachDB/PostgreSQL targets keep using prisma db push). For DSQL, generate and commit a migration:

mise run db:dsql:diff <name>

scripts/dsql-migration.ts:

1. derives the DSQL mirror with scripts/make-prisma-mirror.sh --target dsql, which fails on anything DSQL cannot create (enums, scalar lists, GIN indexes, native types other than @db.Date/@db.Timestamp);
2. runs prisma migrate diff --script from the base to the mirror;
3. rewrites the SQL with dsql-lint --fix (CREATE INDEX → CREATE INDEX ASYNC, foreign keys → NOT VALID); unfixable errors abort;
4. appends ALTER TABLE ASYNC "t" VALIDATE CONSTRAINT "c"; for every NOT VALID foreign key;
5. writes prisma/migrations-dsql/<timestamp>_<name>/migration.sql, migration_lock.toml and schema.snapshot.prisma, and lints the result (must be clean).

The diff base is chosen automatically: --from-empty for the first migration, otherwise the snapshot written by the previous run (prisma/migrations-dsql/schema.snapshot.prisma). The snapshot is the supported base; it is committed with every migration, and the sequence of committed files is what a cluster receives. Prisma’s --from-migrations with a local PostgreSQL shadow database is not an option: the committed files contain CREATE INDEX ASYNC and ALTER TABLE ASYNC, which plain PostgreSQL cannot replay.

--from-url (experimental) diffs against the live cluster instead. It is not a drift check to rely on: DSQL introspection renders every index as USING btree_index … INCLUDE (…), which Prisma cannot map back to the schema, so the diff is full of spurious index drops and recreations that have to be pruned by hand. The URL never goes on the command line (it carries the admin token); export it as DSQL_DIFF_URL or pipe it on stdin, and the script redacts it from every message:

TOKEN=$(aws dsql generate-db-connect-admin-auth-token --hostname <endpoint> --region <region> | jq -Rr @uri)
DSQL_DIFF_URL="postgresql://admin:${TOKEN}@<endpoint>:5432/postgres?sslmode=require&sslaccept=strict" \
  mise run db:dsql:diff <name> --from-url

dsql-lint is pinned to one version in one place, DSQL_LINT_VERSION in scripts/dsql-migration.ts (currently 0.2.17): the generator refuses any other version, and CI (.github/workflows/clippy.yml) reads the same constant to lint every committed migration.sql with the matching npm build. Install it with cargo install dsql-lint --version 0.2.17, or set DSQL_LINT="npx --yes --package=@aws/[email protected] dsql-lint".

DSQL cannot change a column’s type, add NOT NULL to an existing column, add a NOT NULL/DEFAULT column inline, or add a primary key later. Plan schema evolution accordingly: new required columns start nullable, or the table is recreated.