Data stores & environment keys
Backend selection (DATA_BACKEND)
DATA_BACKEND=sqlite|postgres|dynamodb chooses the data store. When it is unset, SYS_ENV picks the default (a non-blank E2E_STORE_SYS_ENV replaces it for this lookup, for stores and media alike); an invalid value throws. The resolver is resolveDataBackend(env) in the Node entry of @tgmc/utilities; the message store, blog post store and getMediaLibrary (@tgmc/media-nuxt) all call it, so they always agree.
| SYS_ENV | Default backend (DATA_BACKEND unset) |
Required connection keys |
|---|---|---|
local |
sqlite |
DATABASE_URL / NUXT_DATABASE_URL (file:./data/local.sqlite) |
development (legacy remote) |
dynamodb |
DYNAMO_TABLE, DYNAMO_POSTS_TABLE, AWS_REGION + a DynamoDB endpoint (below); Docker supplies all of them |
test |
dynamodb |
DYNAMO_TABLE / NUXT_DYNAMO_TABLE, DYNAMO_POSTS_TABLE / NUXT_DYNAMO_POSTS_TABLE, AWS_REGION / NUXT_AWS_REGION |
production |
dynamodb |
same as test |
Overrides in use: docker:local and docker:dev set DATA_BACKEND=dynamodb against DynamoDB Local (docker:local has SYS_ENV=local); DATA_BACKEND=sqlite runs them on SQLite instead; DATA_BACKEND=postgres (with COMPOSE_PROFILES=postgres) brings back the old dev Postgres. scripts/e2e-regression.mjs pins DATA_BACKEND=sqlite so the CI regression job (SYS_ENV=test, no Docker) stays on SQLite.
| Backend | Messages / posts | Media (getMediaLibrary) |
|---|---|---|
sqlite |
SQLite file (DATABASE_URL) |
Local SQLite + filesystem under MEDIA_LOCAL_ROOT |
postgres |
Postgres (DATABASE_URL) |
Postgres catalog and usage list (media-postgres, DATABASE_URL) + local filesystem blobs under MEDIA_LOCAL_ROOT |
dynamodb |
DynamoDB tables | DynamoDB MEDIA_TABLE + S3 ASSETS_BUCKET; throws when either is missing (no silent fallback to local files) |
| Backend | Media slots (MediaSlotStore, server/db/media-slot-store.ts) |
|---|---|
sqlite |
media_slots table in the posts’ SQLite file (migrations/003_media_slots.sql, created on open) |
postgres |
media_slots table in the posts’ database (decorative BOOLEAN, created on first use) |
dynamodb |
Items in the Posts table (DYNAMO_POSTS_TABLE): id: 'SLOT#<tenantCode>#<slotId>', entityType: 'mediaSlot' |
Media slots. The slot records (site media § media slots) use the posts’ backend and connection keys, with no new table or AWS resource. The key is (tenant, id); on DynamoDB the tenant code is TENANT_CODES[tenant] from @tgmc/media (pf for the portfolio). Slot items have no slug, status or publishedAt, so they are in neither posts GSI, and the post store skips entityType: 'mediaSlot' items (as it skips slug claims): listAll, listPublished, getBySlug and getById never return them. list on DynamoDB is a paged Scan (allPages) filtered on entityType and tenant; insertIfAbsent is a conditional put (attribute_not_exists(id)), and on SQL an INSERT … ON CONFLICT DO NOTHING. content/media-slots.json seeds the store lazily (listMediaSlots). core/web/tests/server/db/media-slot-store.spec.ts runs the slot contract (contract/media-slot-contract.ts) on SQLite always, on DynamoDB Local with DYNAMODB_TEST_ENDPOINT and on Postgres with WEB_PG_TEST_URL.
On postgres the catalog, jobs, audit and usage list share the posts’ DATABASE_URL (NUXT_DATABASE_URL wins; no URL throws), in the media tables of @tgmc/media-postgres. It is Postgres, not SQLite, so the in-use check and the delete are one transaction (spec § 3). Blobs stay on disk. On sqlite and postgres the local media library mimics S3: presigned uploads go to a signed PUT /api/media/_local-s3/<key> and objects are served read-only at /m/** (media-nuxt upload modes).
DynamoDB endpoint. The DynamoDB adapters (messages, posts, media) read the endpoint from, in order: the explicit endpoint option, DYNAMODB_ENDPOINT, AWS_ENDPOINT_URL_DYNAMODB, AWS_ENDPOINT_URL. Unset means real AWS. With an endpoint set they pass AWS_ACCESS_KEY_ID / AWS_SECRET_ACCESS_KEY (and AWS_SESSION_TOKEN) as static credentials when both keys are present (DynamoDB Local accepts any value; the Docker stacks set local).
Rule: SQLite and Postgres are for local development only (host or Docker). test and production run on DynamoDB for MessageStore / BlogPostStore — never SQLite or Postgres on Lambda; do not set DATA_BACKEND there.
Contract tests. core/web/tests/server/db/store-contract.spec.ts runs one message / post store contract (contract/store-contract.ts) on SQLite always, on DynamoDB Local when DYNAMODB_TEST_ENDPOINT is set, and on a live Postgres when WEB_PG_TEST_URL is set (a server URL; each store gets a fresh throwaway database). On DynamoDB its many-rows cases write more than 1 MB (12 × 100 KB), so listAll (Scan), listPublished (Query) and the message list (Scan) must follow LastEvaluatedKey (allPages in server/db/dynamo-pages.ts); reading only the first page used to drop rows. core/web/tests/server/db/dynamodb.contract.spec.ts and packages/media-aws/tests/dynamo-local.contract.spec.ts run the real adapters against DynamoDB Local when DYNAMODB_TEST_ENDPOINT is set and skip otherwise. CI’s local-parity job provides it with a services: dynamodb container (amazon/dynamodb-local:2.5.2 on http://localhost:8000); the specs stub their own dummy AWS credentials and region, so the job sets none; locally, point it at a running stack (DYNAMODB_TEST_ENDPOINT=http://localhost:8010 for docker:dev/docker:local). The suites create and delete their own uniquely named tables. nx.json lists the variables (DYNAMODB_TEST_ENDPOINT, MEDIA_PG_TEST_URL, WEB_PG_TEST_URL) as test inputs so a skipped run is never replayed from the Nx cache. The media catalog has a Postgres counterpart: packages/media-postgres/tests/postgres-contract.spec.ts runs when MEDIA_PG_TEST_URL is set (also an Nx test input; not in CI yet), each test in its own throwaway schema (media-postgres § Tests).
Post slugs on DynamoDB. SQLite and Postgres enforce slug uniqueness with a UNIQUE column (a race is mapped to BlogPostSlugConflictError, 409). DynamoDB has no unique index, and the slug-index GSI read is eventually consistent, so blog-dynamodb.ts keeps a claim item per slug in the posts table ({ id: 'SLUG#<slug>', entityType: 'slugClaim', postId }, no slug / status, so it is in neither GSI and never listed as a post) and writes it in one TransactWriteItems with the post: two concurrent creates or renames to one slug cannot both win. An update or delete is conditioned on the post still existing with the slug it read (attribute_exists(id) AND slug = :readSlug, so a content edit that read before a rename re-reads instead of writing the old slug back), so an update that read before a delete returns null instead of resurrecting the post, and its slug is free again. Posts written before the claims have none: the GSI check still guards their slug, and their next update claims it. Legacy posts may even share a slug; then the claim belongs to one of them, and the other is updated (slug unchanged) or deleted without touching it (a ConditionCheck that the claim is not its own) and without a false slug conflict. A transaction that loses a race re-reads and retries at most 3 times, then fails with BlogPostWriteConflictError (409) instead of retrying forever. The contract suite (store-contract.spec.ts) races six creates and two renames on every backend; dynamodb.contract.spec.ts races an update with a delete. The Postgres stores also run their migration once per store (shared by concurrent first requests, which otherwise raced on the catalog with a raw 23505).
Post covers. A post carries coverAssetId (a media library asset id, MEDIA_ASSET_ID) and coverAlt (up to 300 characters), both null by default. An update that leaves a field out (undefined) keeps it; null clears it. Request bodies are validated by blogPostCreateSchema / blogPostUpdateSchema (coverAssetId: 'Must be a media asset id', coverAlt: 'Must be 300 characters or fewer'). DynamoDB stores the attributes only when set and reads a missing one as null. Posts written before the fields existed read them as null on every backend. There is no ALTER migration file: 002_posts.sql is unchanged, because scripts/db-migrate-local.mjs re-runs every .sql file with no tracking and an ALTER TABLE ... ADD COLUMN would fail on the second run. Instead the SQLite adapter reads PRAGMA table_info(posts) on open and adds cover_asset_id / cover_alt when missing (a duplicate column name error is ignored: the web and the admin share data/local.sqlite, so another process may add the column first), and the Postgres migration appends ADD COLUMN IF NOT EXISTS for both. The store contract covers the old-schema case on all three backends (openLegacyPosts).
Shared connections. The message, blog and media-slot stores share one connection per backend (server/db/connections.ts): one Postgres pool per database URL (leasePgPool), one SQLite handle per file (leaseSqliteDatabase) and one DynamoDB document client per region and endpoint (sharedDynamoDocClient). Each store leases its connection and releases it on close(); the last release closes it, so resetting one store never cuts another off. A pool passed in as options.pool (tests) still belongs to its store. databaseUrlFromEnv(override) is the one NUXT_DATABASE_URL / DATABASE_URL lookup, and pgSchemaOnce(pool, sql) the one migration memo. With DATA_BACKEND=postgres the web process keeps one pool for these stores plus the media library’s own. The admin reuses the same stores (admin-database.ts), so it shares them too.
Nuxt Content (separate from MessageStore / BlogPostStore)
| SYS_ENV | Content database |
|---|---|
local / development |
Default Nuxt Content SQLite (dev filesystem) |
test / production |
In-memory SQLite (content.database.filename: ':memory:') restored from the build dump on cold start — no writable Content DB file on Lambda |
Content is not stored in DynamoDB; the in-memory dump is the serverless-safe path. Do not point Content at /tmp/*.sqlite or Postgres on test/prod.
The in-app /docs hub parses markdown from the repo docs/ directory in place (see gallery-and-docs.md). Those files are not copied into core/web/content/. Slim Dockerfile.app copies host .output/<SYS_ENV> (docs are already dumped into the Nitro build); SAM/Lambda only ships the dump.
Factories: server/db/index.ts (messages), server/db/blog-store.ts (posts), server/db/media-slot-store.ts (media slots).
Runtime wiring: server/api/{messages,posts}/store-options.ts.
Empty baked runtimeConfig strings are treated as unset (nonEmpty / firstNonEmptyEnv) so Docker and Lambda env can supply real values. Prefer NUXT_* when both plain and Nuxt names are set.
Keys by environment (templates)
| Key | local | development | test | production |
|---|---|---|---|---|
SYS_ENV |
local |
development |
test |
production |
HOST |
tgmc-portfolio.local |
tgmc-portfolio.test |
localhost (local proxy) |
unused on Lambda |
PORT |
4200 |
4200 |
4300 (local proxy) |
unused on Lambda |
DATA_BACKEND |
unset (SQLite); Docker dynamodb |
unset (dynamodb); postgres opt-in |
unset (dynamodb) |
unset (dynamodb) |
DATABASE_URL / NUXT_DATABASE_URL |
SQLite file URL | Postgres URL (opt-in) | unused | unused |
DYNAMO_TABLE / NUXT_DYNAMO_TABLE |
Docker (DynamoDB Local) | required (Docker sets it) | required | required |
DYNAMO_POSTS_TABLE / NUXT_DYNAMO_POSTS_TABLE |
Docker (DynamoDB Local) | required (Docker sets it) | required | required |
AWS_REGION / NUXT_AWS_REGION |
— | — | required (us-west-2) |
required |
ADMIN_TOKEN / NUXT_ADMIN_TOKEN |
Docker admin only | Docker admin only | Docker admin only | — |
SITE_URL / NUXT_PUBLIC_SITE_URL |
yes | yes | yes | HTTPS required |
OPENAI_MODEL / NUXT_OPENAI_MODEL |
yes | yes | yes | yes |
OPENAI_API_KEY / NUXT_OPENAI_API_KEY |
secret if live | secret if live | secret if live | secret if live |
AI_LAB_* / NUXT_* |
optional | optional | optional | gated in CD |
CORS_ALLOW_ORIGIN |
— | — | * (test) |
https://example.com (must match SITE_URL) |
NUXT_APP_CDN_URL |
optional | optional | CloudFront | CloudFront |
Templates (no secrets): .env.example, .env.development.example, .env.test.example, .env.production.example.
Where values are injected
| Env | Source |
|---|---|
| local | .env.example (+ optional .env) via scripts/docker-compose.mjs → ${VAR} in docker-compose.local.yml; ./data volume for the SQLite fallback |
| development | .env.development.example (+ optional .env.development) via scripts/docker-compose.mjs → ${VAR} in docker-compose.dev.yml |
| test (local parity) | .env.test.example (+ optional .env.test) via scripts/docker-compose.mjs → ${VAR} in docker-compose.test.yml |
| test / production (AWS) | SAM NitroFunction env (infra/sam/template.yaml) + CD parameter overrides |
Verify
npm run db:migrate:local
cd core/web && npx vitest run tests/server/db tests/resolve-process-env.spec.ts
npm run docker:dev # DynamoDB Local + MinIO (COMPOSE_PROFILES=postgres DATA_BACKEND=postgres for Postgres)
npm run docker:test # Dynamo Local in memory (ensureNitroOutput first)