@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();
createPostgresMediaStores({ connectionString | pool, ensureSchema?, readOnly?, onError? })→{ catalog, jobs, audit, usage, pool, ensureSchema(), hasSchema(), close() }. All four stores share one pool. A pool it opened is ended byclose(). An idle connection dropped by the server emits'error'on that pool: it is ignored by default (the next query reconnects) instead of crashing the process, or passed toonErrorto log it. An injected pool’s events are its owner’s business.ensureSchema(defaulttrue) creates the tables on first use.readOnly: truenever creates or writes anything: writes throwPostgres media catalog is read-only, and a database without the media tables reads as an empty catalog. Dry runs use it.createPostgresMediaLibrary({ connectionString | pool, blobs, transformers?, hooks?, onError? })→MediaLibraryHandle(close()ends the pool in the background). The library gets the usage store, sogetAssetUsage,replacePageUsage,rebuildUsageand the in-use refusal ofdeleteAssetwork on Postgres (media § Usage list and delete safeguards).listAssetspages like SQLite:limitdefaults to 100, and a negative (or non-finite)limitmeans no limit; a negativeoffsetreads as 0. Fractional values are floored, which differs from SQLite: there a fractionallimitoroffsetis rejected (datatype mismatch).- Logging.
maskConnectionString(url)replaces the password (userinfo and?password=) with***. Log that, anderr.message, never a rawpgerror or client object: they carry the connection config, password included.
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:
-
Isolation. Every store transaction starts with
BEGIN ISOLATION LEVEL READ COMMITTED, whatever the server, database or role default (default_transaction_isolation). The invariants below rely on it: each statement after a row lock takes a new snapshot. Under REPEATABLE READ the cascade’s usage read would use the snapshot of itsFOR UPDATEstatement and miss a usage row committed while it waited, deleting an asset a page uses. It is pinned by a unit test on theBEGINtext and a live test that sets the session default to REPEATABLE READ. - Names.
holdNameandinsertAssettake a transaction-scoped advisory lock on the name (pg_advisory_xact_lock(hashtextextended('media-name:<tenant>/<kind>/<name>', 0))), drop expired holds, then check the name: held by another id, or registered to another asset, is aMediaNameConflictErrorwith the path. The checks run before theINSERT, so a conflict never aborts the transaction; the unique constraint stays as the backstop (a23505onmedia_assets_name_keymaps to the same error, another23505toMediaConflictError).insertAssetconsumes the asset’s own hold in the same transaction;releaseNameonly removes the caller’s own hold. - Children and delete. Every write that needs its asset locks the parent row first, so a child can never land on an asset being deleted.
deleteAssetCascadeandfinalizeRunningJobAndUpdateChildIfAssetExiststakeSELECT … FOR UPDATE; withholdNameUntilthe cascade then takes the name’s advisory lock (asset row first, then the name, the same order as a rename) and writes the<id>#deletediscarding claim in the same transaction.insertChildIfAssetExistsandinsertChildAndJobIfAssetExiststakeFOR KEY SHARE, which conflicts with the cascade’sFOR UPDATEand itsDELETE(so either the insert waits and then sees the asset gone, or the cascade waits for the insert and deletes its child too) but not with other child inserts or meta updates on the same asset. Both are create-only, like SQLite and DynamoDB (catalog child ids are deterministic, so two runners for one variant pick the same id): under the lock, an existing child id is aMediaConflictErrorbefore anyINSERT(so the transaction is never aborted), and a23505from a racing insert maps to the same error; nothing is overwritten and no job is written.updateChildIfAssetExistsis a singleUPDATE … WHERE EXISTS(with an expected asset path, the asset must still be at it). Finalize returns'asset-missing','asset-moved'(the asset is no longer at the expected path) or'lost-lease'like SQLite. - Rename.
renameAssetis one transaction that locks in this order: the asset row (SELECT … FOR UPDATE), then the advisory locks of both names, sorted by name so two renames sharing a name cannot deadlock. It then checks, before the first write: the asset is still atfrom, the new name is free (or the asset’s own hold), every planned child is still at itsfromkey, no stored child is missing from the plan, and no job is queued or running with a lease started at or afterleaseCutoff(aMediaConflictErrorotherwise). Child inserts wait on the row lock (FOR KEY SHARE); a job enqueued for an existing child does not lock the asset, but its final child write is path-conditioned, so it reports'asset-moved'and the worker redoes the variant under the new key. - Jobs.
claimQueuedJob,failQueuedJob,claimStaleRunningJobandfinalizeRunningJobare one conditionalUPDATE … RETURNING; exactly one concurrent caller wins. - Usage.
replacePageUsageis one transaction that takes all its locks before its first write: first the page’s advisory lock (pg_advisory_xact_lock(hashtextextended('media-usage-page:<tenant>/<pageType>/<pageId>', 0))), which serialises writers of the same page (each reads the page’s rows after the previous one committed), thenSELECT id FROM media_assets WHERE id = $1 AND tenant = $2 FOR KEY SHAREfor each asset it adds or keeps, in id order. An asset with no row in the page’s tenant is reported inmissingand gets no row (a kept row whose asset is gone is deleted).deleteAssetCascadereads the asset’smedia_usagerows right after itsSELECT … FOR UPDATEand before anyDELETE; a row refuses the delete withMediaInUseError(rolled back), unlessusage: 'ignore', which deletes anyway and returns the rows it read as.usage.FOR KEY SHAREconflicts withFOR UPDATE, so one of the two always waits for the other to commit, and under READ COMMITTED every statement takes a new snapshot: if the write holds the asset row first, the cascade’s lock waits for its commit and the cascade’s next statement, the usage read, sees the new row and refuses; if the cascade holds it first, the write’sFOR KEY SHAREwaits, then finds the row deleted and returns nothing, so the asset ismissing. Never both. (SQLite gets the same result from its single writer: one transaction runs whole before the other.)rebuildUsagereads which pages of the listed types have rows, takes the advisory lock of every page it may clear or write (those plus the listed pages), in the order of the lock ids themselves: one query sorts the keys byhashtextextended(key, 0)(ties by key,COLLATE "C"), so any two overlapping lock sets are taken in the same order and two rebuilds cannot deadlock, reads the rows again under the locks (a page that first appears in between is not locked and is left alone), then takesFOR KEY SHAREon every asset it rewrites or inserts, all before the first write. A row younger thanPENDING_USAGE_GRACE_MSis never deleted or rewritten (ruling H1).removeAssetUsageis oneDELETE … RETURNING(ordered likelistUsageForAsset).KEY SHAREdoes not conflict with itself, child inserts or meta updates, so usage writes of different pages for one asset run in parallel.
Tests
npm test --workspace=@tgmc/media-postgresruns unit tests against a fake pool (statements, parameters, transactions, error mapping, factories) with no database.- The shared catalog contract (
packages/media/tests/contract/catalog-contract.ts, see media § Catalog contract tests) runs against a real server whenMEDIA_PG_TEST_URLis set, and is skipped otherwise. Each test gets its ownmedia_contract_<hex>schema, dropped afterwards; nothing outside it is touched.MEDIA_PG_TEST_POOL_MAXcaps the pool (default 4;1for single-session servers such as@electric-sql/pglite-socket’spglite-server, which also runs the contract).nx.jsonlistsMEDIA_PG_TEST_URLas a test input so a skipped run is never replayed from the Nx cache.
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.