Technical design — MOD-103 Neon database platform bootstrap¶

Module: MOD-103 — Neon database platform bootstrap System: SD07 — Data Platform & Governance Infrastructure Repo: bank-platform FR scope: FR-413, FR-414, FR-415, FR-416 (partial — Flyway pipeline is per-repo) Policies satisfied: DT-001 (GATE), PRI-001 (GATE), PRI-003 (AUTO) Author: AI agent (Claude Opus 4.7) Date: 2026-04-21 Dependencies: MOD-104 (Built)

Objective¶

MOD-103 provisions all Neon Postgres infrastructure for the bank platform: - One Neon project (bank) in aws-ap-southeast-2 - Three persistent branches: prod, uat, dev (ephemeral pr-* preview branches created per PR by each system-domain repo's CI) - Six per-domain databases on each branch (bank_core, bank_kyc, bank_aml, bank_payments, bank_credit, bank_app) - Three roles per database (<db>_app_user, <db>_migrate_user, <db>_readonly), with SQL-level GRANTs enforcing least privilege and DT-001 cross-domain isolation - Connection-string secrets in AWS Secrets Manager under bank-neon/<branch>/<db>/<role> (54 secrets per deploy) - SSM Parameter Store outputs under /bank/<env>/neon/* so downstream Lambdas can resolve Neon endpoints by environment

Unlike the other SD07 modules, MOD-103 is not an SST app. Per the MOD-103 spec ("plain CI steps that call the Neon REST API directly. No Terraform state file or provider"), provisioning is handled by an idempotent bash script (scripts/provision-neon.sh). The AWS side — Secrets Manager entries and SSM parameters — is written by the same script via aws CLI, using the tag scheme required by the MOD-104 tag-enforcement SCP.

Execution model¶

Aspect	Decision
Provisioning tool	Bash script calling Neon REST API + AWS CLI (per spec — "plain CI steps")
AWS IaC	None. Secrets + SSM params created by the script; IAM read permissions already exist via MOD-104's `bank-secrets-read` customer-managed policy
Idempotency	Create-if-not-exists semantics throughout. Re-running the script is safe
Stage / env mapping	`AWS env` ⇌ `Neon branch` 1:1 for dev/uat/prod; `env=local` maps to Neon branch `dev`
Tenant model	Single-tenant. All Neon resources live under one project `bank` (id `jolly-recipe-53815583`)
Region	`aws-ap-southeast-2` (Sydney) — Neon's nearest available region for NZ/AU data residency
Tagging	Secrets carry `tenant_id=totara-bank, module_id=MOD-103, environment=<branch>, system_id=SD07, cost_center=sd07-bank-platform, managed_by=sst` (sst value retained for consistency with platform tagging scheme even though MOD-103 itself doesn't use SST)

Layout¶

MOD-103-neon-bootstrap/
├── package.json                # name: @bank-platform/neon-bootstrap; deps: @aws-sdk/*, pg, jest, ts-jest
├── tsconfig.json
├── jest.config.js
├── scripts/
│   └── provision-neon.sh       # idempotent full-stack Neon + Secrets + SSM provisioning (runs from CI or operator shell)
└── __tests__/integration/
    ├── helpers.ts
    ├── fr-413-project-branches.test.ts
    ├── fr-414-databases-roles.test.ts
    ├── fr-415-pooler-config.test.ts
    ├── fr-416-migration-role.test.ts      # partial — Flyway CI is per-repo, MOD-103 asserts DDL works for migrate_user only
    ├── pol-dt-001-cross-domain.test.ts    # GATE negative: bank_core_app_user → bank_kyc → must fail
    ├── pol-pri-001-minimum-perms.test.ts  # GATE negative: bank_core_app_user CREATE TABLE → must fail
    └── pol-pri-003-region.test.ts         # AUTO: project region verified

Resources provisioned¶

Neon (via REST API)¶

Resource	Count	Detail
Project	1	`bank` (id `jolly-recipe-53815583`), region `aws-ap-southeast-2`, Postgres 17
Persistent branches	3	`prod` (renamed from Neon default `production`), `uat`, `dev` (branched off prod). Each gets a read-write endpoint with pooler_mode=transaction
Databases	18	6 per branch: `bank_core`, `bank_kyc`, `bank_aml`, `bank_payments`, `bank_credit`, `bank_app`
Roles	54	3 per database × 18 DBs = 54. Names: `<db>_app_user`, `<db>_migrate_user`, `<db>_readonly`
Endpoints	3	One read-write endpoint per persistent branch (PgBouncer transaction mode)

AWS Secrets Manager¶

54 secrets under the bank-neon/<branch>/<db>/<role> path. Each stores a JSON object:

{
  "host": "ep-...ap-southeast-2.aws.neon.tech",
  "pooler_host": "ep-...ap-southeast-2.aws-pooler.neon.tech",
  "database": "bank_core",
  "username": "bank_core_app_user",
  "password": "...",
  "pooled_url": "postgresql://...@pooler-host/bank_core?sslmode=require",
  "direct_url": "postgresql://...@host/bank_core?sslmode=require"
}

Each secret carries the required SCP tags (tenant_id, module_id=MOD-103, environment, system_id=SD07).

SQL-level GRANTs (DT-001 + PRI-001 enforcement)¶

Applied per database per branch via psql:

-- Cross-domain isolation (DT-001 GATE):
REVOKE CONNECT ON DATABASE <db> FROM PUBLIC;
REVOKE CONNECT ON DATABASE <db> FROM neon_superuser;
-- (Neon roles inherit from neon_superuser; this revoke is the Neon-compatible
-- way to block cross-domain access.)
GRANT  CONNECT ON DATABASE <db> TO <db>_app_user, <db>_migrate_user, <db>_readonly;

-- Least-privilege grants per role:
GRANT USAGE ON SCHEMA public TO <db>_app_user, <db>_readonly;
GRANT USAGE, CREATE ON SCHEMA public TO <db>_migrate_user;

GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO <db>_app_user;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO <db>_readonly;

ALTER DEFAULT PRIVILEGES IN SCHEMA public
  GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO <db>_app_user;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO <db>_readonly;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON TABLES TO <db>_migrate_user;

SSM outputs table (consumer contract)¶

All paths under arn:aws:ssm:ap-southeast-2:647751526084:parameter/bank/{env}/neon/.... Per-branch values because Neon branch IDs and endpoint hosts differ between prod / uat / dev.

SSM path	Value	Consumed by
`/bank/{env}/neon/project-id`	Neon project id (`jolly-recipe-53815583`)	All modules needing Neon API admin (preview-branch CI, MOD-042 CDC)
`/bank/{env}/neon/branch-id`	Neon branch id for this environment	MOD-042 CDC (source branch), MOD-046 PAM (branch-scoped access grants)
`/bank/{env}/neon/pooler-host`	PgBouncer-fronted host (transaction pooling mode)	Every domain's application Lambdas — use for runtime queries
`/bank/{env}/neon/direct-host`	Non-pooled host	Flyway migrations (each repo's CI), CDC logical replication source, PAM DBA sessions

Connection-string secrets are not in SSM (they rotate). Consumers read the Secrets Manager path pattern bank-neon/<branch>/<db>/<role> directly using their MOD-104-provided Lambda role (which has bank-secrets-read attached).

Resolution example¶

// In a downstream module's Lambda handler
const ssm = new SSMClient({ region });
const sm  = new SecretsManagerClient({ region });

const env = process.env.STAGE!;          // "dev" | "uat" | "prod"
const domain = "bank_core";              // owning system-domain

const { Parameter: { Value: poolerHost } } = await ssm.send(
  new GetParameterCommand({ Name: `/bank/${env}/neon/pooler-host` }),
);
const { SecretString } = await sm.send(
  new GetSecretValueCommand({ SecretId: `bank-neon/${env}/${domain}/app_user` }),
);
const { pooled_url } = JSON.parse(SecretString);
const client = new Pool({ connectionString: pooled_url });

Acceptance criteria status (dev branch, 2026-04-21)¶

Run: BANK_NEON_API_KEY=... AWS_PROFILE=bank-dev STAGE=dev pnpm test (from MOD-103-neon-bootstrap/)

FR / Policy	Mode	Tests	Pass	Status
FR-413 — Neon project + 3 persistent branches + preview branch lifecycle	—	5	5	PASS
FR-414 — 6 DBs × 3 roles per branch + Secrets Manager conn strings	—	126	126	PASS (3 branches × 42 assertions each)
FR-415 — PgBouncer transaction pooling on every branch	—	3	3	PASS
FR-416 — migrate_user DDL (Flyway pipeline per-repo, out of MOD-103 scope)	—	6	6	PASS (partial) — migrate_user DDL verified; Flyway integration is each system-domain repo's responsibility
DT-001 — cross-domain direct DB blocked	GATE	1	1	PASS (negative test)
PRI-001 — app roles cannot DDL	GATE	1	1	PASS (negative test)
PRI-003 — project region is ap-southeast-2	AUTO	1	1	PASS
Total		143	143	100%

Test approach¶

Jest + AWS SDK v3 (Secrets Manager, SSM) + pg + native fetch for Neon REST. No mocks; every test hits live AWS + live Neon.

For the GATE negative tests (DT-001, PRI-001), the test opens a real Postgres connection using bank_core_app_user credentials to a database the role is not authorised on, or attempts a DDL, and asserts the server returns permission denied.

Operational notes¶

Prerequisites (one-time, not in this module): - Neon account with a project-scoped API key (stored as env var BANK_NEON_API_KEY) - psql installed locally or on the CI runner (for the SQL grants step) - AWS CLI configured with the bank-dev profile (for Secrets Manager + SSM writes)

Provisioning (idempotent — safe to re-run):

cd neon-bootstrap
export BANK_NEON_API_KEY=napi_...
export AWS_PROFILE=bank-dev
./scripts/provision-neon.sh

First run takes ~5 min; subsequent runs ~1 min (most operations skip-on-exists).

Teardown (if needed): - Delete each branch via Neon API (cascades to DBs and roles) - Delete 54 secrets in Secrets Manager - Delete SSM params under /bank/{env}/neon/*

Preview branch lifecycle (per-repo CI workflow template, not in this module):

# .github/workflows/neon-pr-branch.yml (in each system-domain repo)
on: [pull_request]
jobs:
  manage-branch:
    steps:
      - run: curl -X POST -H "Authorization: Bearer $BANK_NEON_API_KEY" \
          https://console.neon.tech/api/v2/projects/jolly-recipe-53815583/branches \
          -d '{"branch":{"name":"pr-${{ github.event.number }}","parent_id":"<dev-id-from-ssm>"}}'
        if: github.event.action == 'opened'
      # Similar for 'closed' with DELETE

Flyway migrations then run against the PR branch using the <db>_migrate_user connection string.

Wiki spec: bank-wiki/source/entities/modules/MOD-103.{yaml,md}
Handoff: docs/handoffs/MOD-103-complete.handoff.md
ADRs in effect: ADR-023, ADR-024 (Neon hosting), ADR-025, ADR-030 (Secrets Manager), ADR-064 (consolidated single bank database)
SD07 data model (references Neon databases): bank-wiki/source/pages/design/system/data-models/SD07-data-platform.md

ADR-064 — consolidated `bank` database (additive, dev-first)¶

Per ADR-064 (Accepted 2026-05-15, supersedes ADR-052), MOD-103 now also provisions a single database named bank with schema-per- domain isolation. The legacy bank_* databases above remain in place during the migration wave; once every consumer has cut over to bank, a follow-up MOD-103 commit decommissions the legacy databases.

This block is gated by BANK_DB_STAGES (default: dev). UAT and prod waves require a pg_dump/pg_restore + maintenance window — see the bank-wiki migration-wave handoff.

What it provisions¶

On each branch in BANK_DB_STAGES:

One database bank (alongside the eight legacy bank_* databases — both exist in parallel until decommission). 1.5. pg_uuidv7 extension + public.gen_uuidv7() wrapper — installed by neondb_owner before schema creation. Every consumer Flyway migration uses public.gen_uuidv7() for UUID-v7 primary keys; schema-scoped migrate_user roles correctly lack CREATE EXTENSION privilege, so MOD-103's bootstrap owns this. Requires pg_uuidv7 to be enabled at the Neon project level (Project Settings → Extensions) — if not, the script aborts with a clear hint pointing to the UI step.

23 schemas owned by neondb_owner:

Domain	Schemas
SD01 — Core	`core`, `accounts`, `treasury`, `contexts`, `assets`, `loans`
SD02 — KYC	`party`, `banking`, `kyc`, `regulatory`
SD03 — AML	`aml`
SD04 — Payments	`payments`
SD05 — Credit	`credit`
SD07 — Platform	`platform`, `decision_inbox`, `notifications`, `auth`, `audit`, `decision_log`, `external_assets`
SD08 — App	`app`, `access`
Shared	`ftp`

Three roles per schema + one consolidated replicator (bank_replicator). Naming follows ADR-064:

Role	Privileges	Connection
`{schema}_app_user`	DML on schema, sequence USAGE, default privileges	Pooled
`{schema}_migrate_user`	DDL + DML on schema, default privileges	Direct
`{schema}_readonly`	USAGE on schema only — per-view SELECT lands in the producing module's Flyway migration	Pooled
`bank_replicator`	USAGE + SELECT on every schema, default privileges, CREATE on database	Direct

Cross-schema bootstrap grants (the seed set; additional per-view grants land in each producer module's Flyway migration):

Grantee	Target	Privilege	Why
`notifications_app_user`	`app`	USAGE	MOD-063 reads MOD-072's published preferences view
`app_app_user`	`auth`	USAGE + SELECT ALL TABLES + default privileges	MOD-044 authorizer reads `auth.revoked_tokens`
`app_migrate_user`	`access`	USAGE + CREATE (DDL)	bank-app owns both `app` and `access` per SD08; Flyway runs as `app_migrate_user` against both
`app_app_user`	`access`	USAGE + DML on existing + future tables/sequences	runtime DML for SD08 handlers reading `access.user_identities` etc.

AWS Secrets Manager entries at the ADR-064 path convention (one secret per role per env, JSON payload with the assembled connection URL):
```
bank-neon/{env}/{schema}_app_user
bank-neon/{env}/{schema}_migrate_user
bank-neon/{env}/{schema}_readonly
bank-neon/{env}/replicator
```
Each secret contains: host, pooler_host, database='bank', username, password, pooled_url, direct_url. Consumers read once at deploy time and inject pooled_url (or direct_url for Flyway) as DATABASE_URL in the Lambda env.
DT-001 GATE — REVOKE CONNECT ON DATABASE bank FROM neon_superuser. Without this, API-created roles inherit CONNECT via the neon_superuser membership and the per-schema USAGE/DML restrictions become advisory.
SSM contract value:

SSM path Value Consumed by

/bank/{env}/neon/replicator/role-name bank_replicator MOD-042 CDC

The existing pooler-host and direct-host SSM paths are unchanged and serve the bank database too (the endpoint is per-branch, not per-database).

Consumer wiring¶

Every bank-platform Lambda that needs Postgres pulls in the @bank-platform/db workspace package and reads DATABASE_URL from env. The URL is injected at deploy time by the consuming module's SST stack:

// Example consuming-module Pulumi stack (sketch):
const secret = aws.secretsmanager.getSecretVersion({
  secretId: `bank-neon/${stage}/${schema}_app_user`,
});
const dbUrl = secret.secretString.apply(s => JSON.parse(s).pooled_url);

new sst.aws.Function("MyFunc", {
  handler: "src/handler.ts",
  environment: { DATABASE_URL: dbUrl },
});

In the handler:

import { getPool, withTransaction } from "@bank-platform/db";

const { rows } = await getPool().query(
  "SELECT * FROM auth.revoked_tokens WHERE session_id = $1",
  [sessionId],
);

See packages/db/src/index.ts for the full surface (~60 LOC).

Migration sequencing¶

Dev — BANK_DB_STAGES=dev pnpm provision. No live data; clean. Each consumer module then ships a PR updating its DATABASE_URL injection to the new bank-neon/dev/{schema}_app_user secret and redeploys. Old bank_dev_* databases stay running until all consumers are cut over.
UAT — once dev is stable for ≥ 1 week, file a UAT wave handoff with pg_dump/pg_restore + maintenance window. BANK_DB_STAGES=uat.
Prod — same shape as UAT, after UAT proves stable.
Decommission — follow-up MOD-103 commit DROP DATABASE bank_* per branch once consumers have confirmed health for 48h.

Re-running locally¶

cd MOD-103-neon-bootstrap
# Default — dev only:
BANK_NEON_API_KEY=... AWS_PROFILE=bank-dev ./scripts/provision-neon.sh

# Once UAT is in scope:
BANK_DB_STAGES="dev uat" BANK_NEON_API_KEY=... AWS_PROFILE=bank-dev \
  ./scripts/provision-neon.sh

Fully idempotent — re-run as often as needed. Roles aren't re-created if they exist; passwords are re-revealed from Neon and the secret value rewritten so any drift self-heals.