Database schema and runtime state

ErmisAI persistence is a hybrid. Twenty-five Drizzle-defined Postgres tables hold durable relational data. Two of those tables — platform_state and platform_state_hash — are generic key-value stores that hold everything mutable that never earned a schema. Redis holds no application state at all.

Both halves live in the same Postgres database, so "runtime state" here does not mean "somewhere else". It means "a jsonb blob under a string key instead of columns".

The rule for deciding which side

In practice the split falls out like this:

Durable relational: Postgres and Drizzle

src/lib/db/schema.ts is the single schema source of truth — 25 pgTable definitions and 22 pgEnum declarations (18 of them in the block at src/lib/db/schema.ts:98-133, the rest declared next to the tables that use them). One repository module per domain sits beside it in src/lib/db/. There are no parallel Supabase SQL migrations; Supabase is only ever a possible host.

Billing

polar_modified_at is an event-ordering guard: a late webhook cannot resurrect a canceled subscription (schema.ts:246-248).

Identity

clerk_users.app_role is written but never used for authorization — the live JWT claim wins. See Tenant scoping and isolation.

Source catalog

Editorial

Integrations

AI accounting

Compliance

data_erasure_requests (schema.ts:911) drives GDPR erasure through a grace window. Subject type is tenant or user; status is pending, failed, purged, cancelled, or purging. notification_targets and member_user_ids are captured at request time, because Clerk hard-deletes the org membership snapshot immediately and a purge-time lookup two weeks later would find nothing (schema.ts:936-944). A CHECK constraint requires the matching subject id, and two partial unique indexes make "at most one unresolved request per subject" race-safe (schema.ts:950-970).

Stories, clusters and articles are not tables

There is no stories table, no clusters table, no articles table. The only editorial table is editorial_story_drafts, and it embeds the story it was composed from as a story_snapshot jsonb column. Aggregated stories live in the feed cache (platform:story-feed-cache:v1:<scopeKey>, TTL'd) and in process memory. Anything that needs to query across stories queries drafts or rebuilds the feed.

Schema-enforced editorial invariants

editorial_story_drafts does not trust application code for two things.

Optimistic concurrency. version integer NOT NULL DEFAULT 0 (schema.ts:544-547) is bumped on every persisted write, and the transition mutators pass the version they read as a compare-and-set guard. When a caller supplies expectedVersion — the version the client loaded into its edit session — the lock spans the whole session, so a save built on stale content is rejected even after minutes (src/lib/platform/drafts/tenant-draft-store.ts:481-491). A conflict is a precondition failure, not an infrastructure fault: it is re-thrown rather than masked by the fallback path (tenant-draft-store.ts:493-497) and surfaces to the editor as "Someone else updated this draft. The latest version was loaded - review and retry."

State/timestamp consistency. Three CHECK constraints (schema.ts:596-607):

editorial_state != 'in_review'  OR submitted_for_review_at IS NOT NULL
editorial_state != 'published'  OR published_at           IS NOT NULL
editorial_state != 'rejected'   OR reviewed_at            IS NOT NULL

A row that violates one is contradictory audit data, and the CMS and WordPress export gates read published_at. The migration that added them backfills legacy rows from updated_at first (drizzle/20260717002329_editorial-state-timestamp-checks/).

One deliberate oddity: editorial_story_drafts.content_locale and tenant_newsrooms.content_locale both default to the literal 'en', not to the env-derived platform default. A column DEFAULT baked from ERMIS_CONTENT_LOCALE would make the generated schema drift with whichever environment generated it (schema.ts:566-569). Application code always writes contentLocale explicitly.

The database client

src/lib/db/client.ts is plain postgres (postgres-js) plus drizzle-orm/postgres-js. No vendor transport.

• Connection string resolution order is SUPABASE_DATABASE_URL → DATABASE_URL (client.ts:66-69). Both must use the postgres: or postgresql: protocol. If the value contains REDIS_URL= or UPSTASH_REDIS_ the error adds "Check for a missing newline in your env file" (client.ts:82-89).
• Pool size is POSTGRES_POOL_MAX, default 3 per instance, with connect_timeout: 10, idle_timeout: 20, prepare: false (client.ts:19, :135-149).
• The client is memoized on globalThis.__ermisDbClient only outside production (client.ts:154-162).

Statement timeout

POSTGRES_STATEMENT_TIMEOUT_MS defaults to 10000 ms and is sent as a wire startup parameter — but only for non-pooled URLs. isCapyDbPooledUrl (*.db.capydb.dev on port 6432) omits it entirely (client.ts:58-63, :146-148).

The reason is an incident. CapyDB's PgBouncer pooler hard-rejected statement_timeout with 08P01 unsupported startup parameter, which took down every production Postgres connection on 2026-07-22. CapyDB then added it to ignore_startup_parameters — so it is now silently dropped rather than rejected. Sending it through the pooler achieves nothing either way.

Pooled connections get their timeout from a role-level default instead, installed by drizzle/20260722020726_statement_timeout_role_default/:

ALTER ROLE <current_user> IN DATABASE <current_database> SET statement_timeout = '10s';

Runtime state: platform_state and platform_state_hash

Two generic tables, both in schema.ts:

They replace an earlier Upstash blob keyspace. The doc comment states the tradeoff: these access patterns are low-frequency or fronted by an in-process cache, so Postgres latency matches Redis while gaining durability and removing a hard Redis dependency (schema.ts:861-868). The composite primary key on the hash table is what gives back the atomic insert-if-absent that Redis HSETNX provided (schema.ts:883-887).

The store API

The factory is createPlatformStateStore (src/lib/platform/shared/runtime-state.ts:60). Exactly one instance is built, in src/lib/platform/shared/state.ts:25-37, and it exports:

The Drizzle context is dynamically imported and lazily cached, so the in-memory path never instantiates a DB client and a missing DATABASE_URL only matters once the store is actually used (runtime-state.ts:42-58).

Unlike the Redis store it replaced, this store does not latch a disabled flag. Postgres is the system of record, so a transient error surfaces in production or falls back per call in dev (runtime-state.ts:70-73).

Key naming

buildScopedPlatformKey(domain, id) produces platform:<domain>:<normalized-id> (runtime-state.ts:18-20). Ids are trimmed and lowercased; null or undefined becomes 'anonymous' (runtime-state.ts:10-12). The id is almost always a tenant scope id — user:<clerkUserId> or org:<clerkOrgId> — which is also how the GDPR purge finds a tenant's KV slice.

A handful of keys use dedicated builders for composite ids: buildEditorQueueStateKey, buildStoryNotesStateKey, buildStoryChatSessionsKey, buildWordPressConnectSessionsStateKey, buildWordPressConnectionsStateKey (runtime-state.ts:22-39).

Validation

Every payload is zod-validated on read and on write. resolveLocalPlatformStateSchemaByKey (src/lib/platform/shared/state-schemas.ts:274-382) matches a key against an ordered list of exact-match and prefix matchers and returns the schema.

• A failing read treats the value as absent and writes one line to stderr: [local-platform-data] invalid postgres state for <key>: … (runtime-state.ts:112-114).
• A failing write or mutate throws [local-platform-data] write validation failed for <key> (runtime-state.ts:513); the hash variant throws [local-platform-data] hash write validation failed for <key> (runtime-state.ts:377).

Known key families

Expiry and cleanup

writeState and mutateState always write expires_at: null. Exactly one writer sets an expiry: the persisted feed cache, using ERMIS_FEED_CACHE_PERSIST_RETENTION_MS (src/lib/services/rss-aggregation.ts:794-802).

Reads filter expired rows, but nothing deleted them, so abandoned scope keys — old multi-KB per-source-set feed caches — accumulated indefinitely. src/app/api/admin/state/cleanup/route.ts runs daily at 15 3 * * * and deletes:

1. platform_state rows with a non-null expires_at in the past;
2. platform_state_hash rows under platform:ai-envelope-threshold-firings:% older than 90 days (THRESHOLD_FIRING_RETENTION_DAYS).

It returns { deleted, deletedHashRecords, processedAt }. GET requires CRON_SECRET; POST requires an operator session with queues admin access.

Email-suppression hash rows are deliberately never pruned — the route prunes only explicitly listed key families, never the whole hash store (cleanup/route.ts:18-25).

Array caps

KV arrays are bounded, which means they are not counting sources:

The delivery-log module documents the failure this caused. Delivery records used to live in one global 500-entry array written read-then-write: concurrent appends lost entries, and once platform-wide volume passed 500 entries per month the tenant usage page silently undercounted. The fix was to separate the three concerns — a bounded per-tenant raw log for dedup and inspection, exact per-UTC-day counters as the usage page's source of truth, and a bounded global mirror for the admin dashboard — and to route every write through mutateState (src/lib/platform/shared/delivery-log.ts:1-18).

Hybrid domains: which side is authoritative

Several domains have a table and a KV key, selected at runtime by a repository-availability gate. The table is primary; the KV key is a mirror or fallback.

Two behaviours are worth knowing before you touch these:

• Successful DB writes are still mirrored to KV. The draft upsert writes the DB under a compare-and-set, then reflects the post-write version into the KV copy so the cache and the DB do not disagree by one (tenant-draft-store.ts:506-522). The newsroom profile does the same.
• The integrations gateway returns { ok: false } rather than throwing. runWithIntegrationsRepository hands back a discriminated result and the caller uses the KV records (integrations-gateway.ts:62-81).

Availability is not permanent. createRepositoryAvailability (src/lib/runtime/repository-availability.ts:20-67) disables a repository for a 60 second cooldown (DEFAULT_REPOSITORY_DISABLE_COOLDOWN_MS = 60_000), logs the first disable, and re-logs once per cooldown window so an ongoing outage stays visible instead of being swallowed.

Production has no in-memory fallback

src/lib/platform/shared/persistence.ts decides this in two functions:

export function isInMemoryStoreForced() {
  if (process.env.VERCEL_ENV) {
    return false
  }

  return process.env.ERMIS_FORCE_INMEMORY_STORE === 'true'
}

export function isLocalStateFallbackAllowed() {
  if (isInMemoryStoreForced()) {
    return true
  }

  if (process.env.VERCEL_ENV) {
    return false
  }

  return process.env.NODE_ENV !== 'production'
}

When fallback is not allowed, a failure throws [local-platform-data] <surface> is required in production after <stage>: <error> (persistence.ts:36-46).

ERMIS_FORCE_INMEMORY_STORE=true is a local-only switch. The presence of VERCEL_ENV — production or preview — wins over the flag, so a stray value copied into a deployment environment can never route tenant state to memory.

The Redis boundary

src/lib/redis/client.ts:17-39 returns a cached singleton, or null when UPSTASH_REDIS_REST_URL/UPSTASH_REDIS_REST_TOKEN are absent or construction fails (logged [redis] Upstash client disabled: …). Every consumer must tolerate null.

That is the complete list of consumers:

The ledger retry queue claims work with LMOVE into a processing list rather than RPOP, so a crash strands at most one entry, and a reclaim loop drains the processing list at the start of each redrive (ledger-retry-queue.ts:166-189). Redrive is safe to race because ledger persistence is idempotent — a stable client-supplied row id makes re-insert a PK no-op. MAX_ATTEMPTS is 10.

The feed build lock TTL is 7 minutes because a 4-minute TTL expired while minute-five builds were still running and a concurrent trigger started a duplicate whole-catalog AI build. It must exceed the 300 s consumer maxDuration.

Object storage

src/lib/storage/object-storage.ts supports two providers: 'supabase' and 'vercel-blob'. resolvePreferredProvider() (object-storage.ts:101-117) takes an explicit OBJECT_STORAGE_PROVIDER first; otherwise Supabase if SUPABASE_URL/NEXT_PUBLIC_SUPABASE_URL plus SUPABASE_SERVICE_ROLE_KEY are set; otherwise Vercel Blob if BLOB_READ_WRITE_TOKEN or BLOB_STORE_ID (the OIDC path) is set; otherwise null.

Path normalization is security-relevant. Leading slashes are stripped and empty, . and .. segments dropped, so a caller-supplied path cannot traverse out of its prefix in a shared public bucket; an empty result throws "Invalid object storage path" (object-storage.ts:67-83).

Public objects

uploadPublicObject defaults to cache-control: 31536000. Two callers:

• src/app/api/files/upload/route.ts:68 — key uploads/<userId>/<uuid>.<png|jpg>, derived server-side and never from the client filename (route.ts:59-63).
• src/lib/sources/source-icon-resolver.ts:451 — key source-icons/<host>.

getPublicObjectUrl(path) builds <url>/storage/v1/object/public/<bucket>/<path> on Supabase. Vercel Blob has no path-to-URL helper, so it needs BLOB_PUBLIC_BASE_URL and returns null without it (object-storage.ts:251-279).

The private archive split

Webhook payload archives carry PII — Clerk and Polar webhook bodies contain emails, names and billing ids — so they use a true access: 'private' ACL.

uploadPrivateArchiveObject (object-storage.ts:281-319) prefixes the path with archive/, sets addRandomSuffix: true, stamps bucket with the marker 'private-archive', and nulls the returned url so no fetchable location is ever persisted. Deletion works from the pathname alone.

Without the archive token the archive is not written at all and the function returns null; the caller then inlines the payload in its DB row (object-storage.ts:301-305). That is still non-public, but see the retention trap below.

deletePrivateArchiveObject(locator) (object-storage.ts:337-381) routes by the recorded provider and bucket, not the currently configured one, so post-cutover deletes still find pre-cutover objects. bucket === 'private-archive' means the private store and requires the archive token — it throws a descriptive error if the token is missing. bucket === null means a pre-split row on the shared public store.

removePublicObjectsByPrefix(prefix) (object-storage.ts:470-492) is what GDPR erasure uses to purge uploads/<userId>/. The Supabase path walks the folder tree with 1000-item pagination and removes in batches; the Blob path pages list() and deletes each page before fetching the next cursor.

Payload archive and retention

src/lib/storage/payload-archive.ts computes a SHA-256 checksum, a preview capped at 2048 characters, and a path archives/<domain>/<YYYY>/<MM>/<DD>/<uuid>.json. If the upload returns null, the record keeps payload inline and leaves every storage column null (payload-archive.ts:53-63). Callers are src/lib/db/clerk-user-repository.ts:781 (domain clerk-webhooks) and src/lib/db/billing-repository.ts:926 (domain billing-webhooks).

Both repositories prune webhook rows older than 30 days (WEBHOOK_EVENT_RETENTION_DAYS) at most every 6 hours (WEBHOOK_EVENT_PRUNE_INTERVAL_MS) and delete the corresponding blobs.

Where a tenant's data actually lives

Useful when answering "what is stored for this workspace". The GDPR purge is the authoritative enumeration — one transaction deletes, in order: ai_usage_metering_outbox, ai_usage_ledger, editor_queue_items, editorial_story_drafts, story_chat_session_payloads, story_chat_sessions, tenant_webhook_logs, tenant_webhook_configs, tenant_wordpress_connections, tenant_integration_api_keys, tenant_newsrooms, billing_checkout_sessions, billing_portal_sessions, billing_invoices, tenant_subscriptions, tenant_memberships, then the tenant's slice of platform_state and platform_state_hash matched by LIKE '%<escaped scope id>%' (purgeTenantData, src/lib/db/data-erasure-repository.ts:103). After commit it purges uploads/<userId>/ and drains the Redis ledger retry queue for the scope. Per-table counts land in data_erasure_requests.purge_report.

That LIKE '%<scope>%' match is only possible because every KV key embeds the scope id. A key that does not carry the scope id is invisible to erasure. The function refuses a blank scope id outright, because a blank one would turn the scoped delete into LIKE '%%' and wipe every tenant's state (data-erasure-repository.ts:115-120).

Secrets at rest

src/lib/security/secret-encryption.ts wraps tenant outbound-webhook secrets in an AES-256-GCM envelope, enc:v1:<iv>:<authTag>:<ciphertext>, keyed by WEBHOOK_SECRET_ENCRYPTION_KEY (32 bytes, base64 or hex).

When the key is unset, encryptSecret is a no-op returning plaintext and decryptSecret passes through anything without the enc:v1: prefix. That makes the feature switchable on later with no migration — and means an unset key stores webhook secrets in the clear, silently.

Migration layout

Migrations use the drizzle-kit v1 rc layout: one directory per migration containing migration.sql and snapshot.json. There is deliberately no drizzle/meta/ directory and no _journal.json; applied state lives in the drizzle.__drizzle_migrations table in the database. Nineteen migration directories exist, from 20260507070419_fearless_praxagora (initial schema) to 20260722020726_statement_timeout_role_default.

Full rules — the production migrate-then-build gate, enum ordering, and the direct-versus-pooled URL split — are in Migrations and schema changes.