The constraints came first
The clinical data platform on this site runs on a synthetic study warehouse that lives in the browser, in DuckDB. Its cloud side had stayed on paper: an earlier decision record kept the AWS reference design synthesized with nothing deployed, because a live account costs money.
Two things changed on 26 October. The Vercel Marketplace now provisions some AWS products with short-lived OIDC credentials, and I wanted a version that uses only AWS's permanent monthly free quotas, not trials or credits. Of the seven products on offer, two have one: Aurora DSQL, with 100,000 DPUs and 1 GB of storage a month, and DynamoDB, with 25 GB of storage and 25 read and 25 write capacity units in provisioned mode.
The other constraint was compute. The site runs on Vercel's Hobby plan, which allows 4 hours of active CPU in 30 days, and it had used about 3 hours 15 minutes. Any design that added a server call per visitor was out.
Writes from CI, reads at build time
The decision, ADR 0070, gives each store one job. DSQL is the warehouse of record: the synthetic gold layer is loaded into it and checked against the DuckDB copy. DynamoDB is to be the run ledger, one item per pipeline or release run, holding versions, hashes and conformance results.
The rules about who touches them, and when, matter more than the products:
- Writes come only from GitHub Actions workflows on main, through an OIDC role. No access key exists anywhere. The role may connect to the one cluster, put, query and describe on the one table, and read one CloudWatch metric. A test pins that list so it cannot widen quietly. Pull requests, forks and other branches cannot assume the role.
- Reads happen only while the production site is built, through the Marketplace's read role. No route handler, server component, revalidation or middleware calls AWS. Visitors get static pages, and the compute budget does not move.
- A failed read fails the production build, the same rule the site applies to its database and cache. A build without the warehouse variables renders the page without that section. It never fills the gap with invented numbers.
I would want the same shape in any clinical platform: one automated path writes the system of record, a second one reads it, and nobody writes to it by hand.
Prove the copy matches
Loading a warehouse is easy. Knowing the load is right takes the effort. The load plan (#3004) is pure code. It emits the statements, batched inside DSQL's limit of 3,000 rows per transaction, and a fingerprint for each table from the rows it loads. The loader (#3013) reads every table back, fingerprints what DSQL returns the same way, and writes a load manifest only when every table matches. It empties the manifest before it drops the first table, so a load that dies halfway leaves no manifest at all, rather than an old one that still says everything matched.
Each fingerprint is a row count, a text length and a hash over the sorted rows, so the order DSQL returns rows in does not matter. It is a change detector, not a cryptographic hash, and the code says so. Making the two sides agree meant spelling out how PostgreSQL renders each type as text: dates as YYYY-MM-DD, booleans as words, and decimals sent as text so that no binary float reaches a NUMERIC column.
DSQL has limits of its own: no temporary tables, no triggers, no PL/pgSQL, and one DDL statement per transaction. So its DDL is generated on its own instead of being shared with the DuckDB and T-SQL dialects the warehouse already produces.
An exit plan before the first load
The AWS account is on the Free plan: no card, and it closes after six months, around 26 April 2027, unless it is upgraded. The always-free quotas continue only on the paid plan. So the way out is already written down (#3014), with a reminder set for 1 March.
Leaving is cheap because nothing in AWS is the only copy. The repository regenerates the gold layer, and the run ledger is history. The loader speaks plain PostgreSQL, so the same plan, parity check and manifest can point at a separate Neon schema instead. The plan also lists the clean-up: the integrations, the CloudFormation stack, the repository variables, and an amendment to the decision record that says what moved and why.
Where it stands
The plan, the loader, the role template and the workflow are merged. The first load has not run yet. It waits on a one-time step in the AWS console that creates the role, and then a manual run of the load workflow, which reports how many DPUs the load used. The workflow stays manual until that number is known. The decision record also commits to a monthly check that usage beyond the free quotas is $0.00.
What I would carry to any regulated cloud platform:
- Decide the write path and the read path before choosing the database.
- Store no keys. Use short-lived credentials scoped to one resource, with a test that fails if the policy grows.
- Fail the build when a source is unreachable, instead of publishing fallback data as if it were real.
- Prove each load with a read-back, and write the success record last.
- Write the exit plan while leaving is still cheap.