Skip to the content.

@tgmc/media-postgres

PostgreSQL catalog, job, audit and usage stores for @tgmc/media.

Package README: packages/media-postgres/README.md.

Role

Concern Owner
Catalog / jobs / audit / usage This package (PostgresCatalogStore, PostgresJobStore, PostgresAuditStore, PostgresUsageStore), on pg
Schema bootstrap ensureMediaSchema(pool) (this package)
Objects Not here: S3BlobStore (@tgmc/media-aws, S3 / MinIO) or FsBlobStore (@tgmc/media)
Transfer CLI endpoint npm run media:transfer postgres:<url> (scripts/media-transfer.js)
Admin / @tgmc/media-nuxt wiring getMediaLibrary uses it on DATA_BACKEND=postgres (DATABASE_URL, filesystem blobs under MEDIA_LOCAL_ROOT)

pg is a dependency of this package only; @tgmc/media never loads it. A pool the package opens (connectionString) imports pg on its first query, not with the module, so a bundle that includes this package does not evaluate pg at boot (createLazyPgPool). maskConnectionString masks the userinfo password and every query parameter whose name contains password (sslpassword).

API surface

import { FsBlobStore, transferMedia } from '@tgmc/media';
import { S3BlobStore } from '@tgmc/media-aws';
import { createPostgresMediaLibrary, createPostgresMediaStores } from '@tgmc/media-postgres';

// MediaLibrary on Postgres + S3 (or MinIO with endpoint / forcePathStyle).
const { library, close } = createPostgresMediaLibrary({
  connectionString: process.env.MEDIA_DATABASE_URL,
  blobs: new S3BlobStore({ bucket: 'portfolio-media', region: 'us-west-2' }),
});

// The bare stores (transferMedia, scripts). `pool` instead of `connectionString` reuses an
// existing pg.Pool; close() then leaves it open.
const stores = createPostgresMediaStores({ connectionString: process.env.MEDIA_DATABASE_URL });
await stores.ensureSchema();
const endpoint = { catalog: stores.catalog, audit: stores.audit, blobs: new FsBlobStore('./pg-blobs') };
await stores.close();

Schema

ensureMediaSchema(pool) creates the SQLite catalog’s tables (@tgmc/media adapters/sqlite/db.ts) with CREATE … IF NOT EXISTS, inside a transaction holding pg_advisory_xact_lock(MEDIA_SCHEMA_LOCK_KEY), so concurrent processes bootstrapping one database do not race. It runs once per pool; a failed bootstrap is retried on the next call. Tables live on the connection’s search_path (normally public).

Table Notes
media_assets meta jsonb, byte_size bigint, CONSTRAINT media_assets_name_key UNIQUE (tenant, kind, name); list indexes on (tenant[, kind], created_at DESC, id DESC)
media_children FK to media_assets, recipe / meta jsonb
media_jobs indexes on (status, created_at) and (asset_id, created_at)
media_audit_events payload jsonb, index on (entity_id, occurred_at, seq)
media_name_reservations primary key (tenant, kind, name): a name held for an asset id while its upload is in flight
media_usage primary key (asset_id, tenant, page_type, page_id), index media_usage_page_idx on (tenant, page_type, page_id); no foreign key, like media_jobs

media_usage records which published page uses which asset (UsageStore). It has no foreign key to media_assets, like media_jobs: a plain REFERENCES would block a forced delete of an in-use asset, and ON DELETE CASCADE would drop the rows inside the cascade, while a forced delete keeps them until it has audited them (removeAssetUsage). Existence is checked under a row lock instead (see Concurrency). asset_id, page_type, page_id and updated_at are COLLATE "C", so listUsageForAsset (ORDER BY page_type, page_id), listUsageForPage (ORDER BY asset_id) and the rebuild’s age check (updated_at compared as text) follow byte order, like SQLite. Rows round-trip as text.

Timestamps stay ISO-8601 UTC text, as in SQLite, so rows round-trip exactly; ids and timestamps use COLLATE "C", so ordering is byte order whatever the database locale. seq (identity) breaks ties between equal timestamps in insertion order, like SQLite’s rowid. Listing order matches SQLite: assets created_at DESC, id DESC; children, audit events oldest first.

jsonb differs from SQLite’s JSON text. Meta, recipe and payload come back with their object keys reordered (jsonb sorts by key length, then bytes) and duplicate keys collapsed to the last one, so compare them as objects, not strings. A string holding \u0000 is rejected with error 22P05 (unsupported Unicode escape sequence), which SQLite accepts; strip NUL characters from user-supplied meta before writing.

Least privilege and cold starts. The bootstrap needs CREATE on the schema, and on every first use per pool it runs the CREATE TABLE / CREATE INDEX IF NOT EXISTS script; even when everything exists, CREATE INDEX IF NOT EXISTS takes a SHARE lock on its table, which waits for (and blocks) writers for that moment. For an app role without DDL rights, or a Lambda where each cold start would repeat it, pass ensureSchema: false and create the schema once in a separate migration step run by a privileged role (MEDIA_SCHEMA_SQL is exported for that). The admin / media-nuxt wiring (plan PR 3c) should do this.

Concurrency

The semantics are the SQLite adapter’s, enforced with Postgres locks instead of SQLite’s single writer:

Tests

MEDIA_PG_TEST_URL=postgres://postgres:<password>@localhost:5432/postgres npm test --workspace=@tgmc/media-postgres

The same file runs the usage contract (describeUsageContract, media § Catalog contract tests) through postgresUsageHarness(url): a fresh schema per case, so each case starts with no usage rows. Its race case passes against Postgres, but there the cascade, with fewer round trips, usually takes the asset row lock first whatever the start order. So the file adds Postgres usage locks cases that hold one transaction open at its COMMIT (CommitGatePool) and prove each lock order: a write holding FOR KEY SHARE makes the cascade wait and then refuse (also with the cascade’s session defaulting to REPEATABLE READ); a cascade holding FOR UPDATE makes the write wait and then report the asset missing; two writers of one page are serialised; a rebuild holds the lock of every page it clears. Those need two connections at once and are skipped when MEDIA_PG_TEST_POOL_MAX is 1.

A throwaway server works as well as the docker:admin one (postgres://portfolio:portfolio@localhost:5433/portfolio_admin with that stack’s defaults):

docker run -d --name media-pg-test -e POSTGRES_PASSWORD=<password> -p 127.0.0.1:55432:5432 postgres:16
MEDIA_PG_TEST_URL=postgres://postgres:<password>@127.0.0.1:55432/postgres npm test --workspace=@tgmc/media-postgres
docker rm -f media-pg-test

CI does not run a Postgres service yet, so the live contract runs locally only.

Transfer endpoint

npm run media:transfer accepts postgres:<postgres://… or postgresql://… URL> with the objects in --blobs fs:<dir> or --blobs s3:<bucket> (--from-blobs / --to-blobs when both sides are Postgres; --s3-endpoint / --path-style / --region apply to s3: blobs). A Postgres source is opened read-only and must already have the media tables (exit 2 otherwise); a dry-run target is read-only (never created); a real target gets its tables before anything is copied, so a bad URL fails fast. Passwords are masked (***) in every line the CLI prints; prefer PGPASSWORD to a password in the URL so it stays out of shell history.

npm run -s media:transfer -- --from sqlite:./data/admin-media \
  --to postgres:postgres://media@db.internal:5432/portfolio --blobs s3:portfolio-media --dry-run

Runbook: cicd.md § Promote media to production. Transfer rules: media § Transfer between backends.