Skip to main content

Data design standard

The Data Architect reviews data choices before implementation when a chain meets a review trigger below. This standard applies to migrations, lo-database, lo-cache, lo-storage, and crawler, bot, and worker write paths. MUST rules block approval until satisfied. SHOULD rules are defaults; a plan can deviate with a one-line reason.

0. When this standard is applied (Trigger 1)​

A chain gets a "Data design review" child (assigned to the Data Architect, blockedBy edge to the first implementation child, ZAF-621) when it introduces any of:

  1. a new table or hypertable, or any store decision (the store decision tree);
  2. a schema change on a hot or large table — platform_chat_messages, platform_event_logs, platform_users, audit_events, channel_history*, public_channel_*, notifications, sessions, api_keys;
  3. a new Redis keyspace, TTL, invalidation pattern or pub/sub channel;
  4. a new kind of stored object (uploads, assets, exports);
  5. a new query on a hot path (per chat message, per event, per crawl sample, per authenticated request).

Exempt: permission/flag/plan-feature seeds, a nullable column on a small table, bug fixes with no schema change. When in doubt, request the review — it costs one heartbeat.

1. Store decision tree​

Answer in order; the first "yes" decides.

#QuestionStoreLumio examples
1Is it a binary/blob, or text > ~64 KB that is written once and read whole (image, audio, bundle, export)?Object storage (lo-storage) + a metadata row in Postgresuploads, sounds, extension bundles/versions, avatars
2Is it append-mostly, keyed by time, queried by time range, and does it grow with platform activity (per message / event / sample / request)?TimescaleDB hypertableplatform_chat_messages, platform_event_logs, audit_events, channel_history_stats, extension_events
3Is it a derived, time-bucketed read model of a hypertable?TimescaleDB continuous aggregatechannel_history_stats_5m, _1h
4Is it a read model that lives next to TSDB data and is joined with it (crawler/stats public read model), even if it is updated in place?TimescaleDB, plain table (ZAF-791 / ZAF-843)public_channel_*, channel_history, channel_history_streams, csp_violation_reports
5Is it tenant/user state with relations, constraints, or an RBAC/billing/auth meaning?PostgreSQL (primary)accounts, account_memberships, roles, channel_connections, plans, overlays, automations, platform_users
6Can it be lost at any moment (Redis flush, failover, eviction) and rebuilt from a source of truth or simply expire?Redis (lo-cache) with TTLpermission cache, profile cache (4 h), rate limits, OAuth state, emote sets, channel status
7Must it be delivered to other processes/replicas now and is loss acceptable?Redis pub/subpermission invalidation, overlay/widget fan-out, WS relay
8Must it be delivered at least once across a restart/deploy, with replay?Broker — not available yet (ZAF-673). Until then: write to Postgres/TSDB first (outbox) or a Redis list with a durable drain + dead-letter, never an in-memory channelchat buffer (Redis list → 5 s flush → TSDB + 72 h dead-letter) is the accepted pattern

Boundary rules

  • MUST — Redis is never the only copy of tenant data. If losing the key loses user-visible data, it belongs in Postgres/TSDB; Redis may cache it. Reason: extension storage is Redis-only, no TTL, no quota, survives uninstall (F6, ZAF-2186). Exceptions are buffers with a durable drain (chat buffer), and they need a dead-letter and a bounded length.
  • MUST — the Postgres ↔ TimescaleDB boundary (ZAF-791): time-series and their read models live in TSDB; relational tenant state lives in Postgres. There are no cross-database FKs — a TSDB row carries account_id as a plain UUID, and the deletion path for that account must be written explicitly (the retention section, GDPR deletion paths). Reason: public_channel_* had to be moved after the fact (ZAF-843).
  • MUST — no write path drops data on a store error without a retry/dead-letter. "Log and continue" on an insert is data loss, not error handling. Reason: platform events were inserted straight into TSDB and dropped on error, and EventSub does not redeliver (F7, ZAF-2187). Platform-event ingest uses lo_events::persist_event (park + replay); see Chat and event durability.
  • MUST — in-memory queues are drained on SIGTERM (graceful shutdown handles SIGTERM, not just SIGINT), and are bounded. Reason: F7 — the Twitch EventSub mpsc(1024) persist queue was lost on every deploy. apps/api/src/shutdown.rs is the pattern: cancel, await the worker (which awaits its queue), then the final flush.
  • MUST — a statement that legitimately runs longer than the pool's statement_timeout (30 s on the API pools) lifts it for its own transaction with lo_database::begin_unbounded; never raise the pool-wide limit for one query. Reason: one slow query on the 5-connection TSDB pool stalled chat flush, events and audit together (F7).
  • SHOULD — high-churn columns don't sit on hot relational rows. A per-message UPDATE creates one dead tuple per message (audit the retention section "what breaks first" #3). Debounce (update when value changed or older than N minutes), aggregate per flush, or move the counter to TSDB/Redis. Reference: platform_users.message_count is tallied in process and written once per chat-flush tick per account, with fillfactor = 80 so the update stays HOT (ZAF-2195, Chat → Chatter tally).

2. Naming & key conventions​

2.1 Tables and columns​

  • snake_case, plural table names (account_memberships), singular column names.
  • MUST: tables holding cross-platform user-facing platform data use the platform_ prefix (platform_users, platform_chat_messages, platform_event_logs, platform_moderation_log, platform_user_notes); the old chat_* / event_logs names no longer exist (Serena database/platform-table-naming).
  • Public crawler/stats read model: public_channel_*, public_stream_* (TSDB). Extension telemetry: extension_*. Join tables: <a>_<b> in the dependency direction (account_role_permissions).
  • Booleans is_* / has_*; enumerations as TEXT + CHECK (col IN (…)) (cheap to extend in a migration, unlike a PG ENUM's ALTER TYPE).
  • Indexes idx_<table>_<cols>; unique uq_<table>_<cols>; FK constraints keep the PG default name unless a migration needs to reference them.

2.2 Primary keys​

  • MUST: every table has a PK. Default id UUID PRIMARY KEY.
  • SHOULD: new ids are generated as UUIDv7 in Rust (Uuid::now_v7(), already 930 call sites) — time-ordered, so B-tree inserts stay right-leaning and index pages stay hot. DEFAULT gen_random_uuid() (v4, 124 columns today) is acceptable only on small tables; on a large append table a random leading key costs page splits and cache misses (audit F4: channel_history_stats had a random-UUID-leading PK and now defaults to uuidv7(), PostgreSQL 18).
  • Natural/composite PKs are fine for pure join tables (PRIMARY KEY (role_id, permission)).
  • MUST — hypertables (ZAF-433): the PK and every UNIQUE constraint include the partitioning time column: PRIMARY KEY (id, created_at) / (id, time). Put id first only if lookups by id are a real query; otherwise lead with the column the hot query filters on. Never retrofit — ZAF-433/434 had to rewrite tables and edited a merged migration while doing it.

2.3 Foreign keys​

  • MUST: every FK states ON DELETE explicitly, chosen deliberately:
    • CASCADE — the child is meaningless without the parent (memberships, layers, nodes).
    • SET NULL — the child is the account's record and must survive the actor (*_by, uploaded_by, dissolution_requested_by).
    • RESTRICT — only when deletion must be blocked on purpose, and the deletion flow handles it (accounts.owner_id: profile deletion answers 409 OWNS_CHANNELS). Write RESTRICT, never NO ACTION or no clause: the catalog cannot tell an explicit NO ACTION from a forgotten one, so the FK guard test rejects both. Reason: ~50 FKs to users, many NO ACTION (uploads.uploaded_by, sounds.uploaded_by) → DELETE FROM users fails with 23503 for anyone who uploaded into a foreign account (F2, ZAF-2182).
  • MUST: every FK column is the leading column of some index (§3, FK indexes).
  • MUST: no cross-database FKs (Postgres ↔ TSDB ↔ extensions DB). Store the id as plain UUID, document who deletes it.

2.4 Timestamps and types​

  • MUST: TIMESTAMPTZ, never TIMESTAMP (all 269 existing timestamp columns comply). Stored in UTC; display conversion is the client's job.
  • created_at TIMESTAMPTZ NOT NULL DEFAULT now() on every table; updated_at only where rows are updated, set by the writer (no trigger magic on hot tables). Hypertables: the time column is NOT NULL and is what the hot query ranges over (created_at for logs, time for samples).
  • Soft-delete / lifecycle markers as nullable timestamps (deletion_requested_at, revoked_at), never boolean flags.
  • TEXT over VARCHAR(n); enforce length with CHECK (char_length(col) <= n) where it matters. Money as BIGINT minor units + currency, never FLOAT/NUMERIC without scale. JSONB for sparse/opaque payloads only — anything filtered or joined on gets a real column.
  • Secrets (channel_connections tokens, app_credentials) are encrypted at rest (AES-256-GCM) and never land in a cache, log, metric label or audit metadata.

3. Index policy​

3.1 When to add an index​

  • MUST: every index in a plan names the query it serves and its expected selectivity. An index without a query is write amplification and RAM (audit S-items: 6 duplicate + ~20 prefix-redundant indexes on next).
  • SHOULD: don't index columns with < ~5 % selectivity on their own (booleans, platform, status) — make them the trailing column or a partial-index predicate instead.
  • MUST NOT: create an index whose columns are a leading prefix of an existing index on the same table (redundant), or an exact duplicate of a PK/UNIQUE index.
  • MUST: every ordered LIMIT read on a hypertable carries a time lower bound on the partitioning column. ORDER BY created_at DESC LIMIT n runs as a ChunkAppend that walks chunks newest-first until it has n rows; when fewer than n rows exist (an empty account, the last page of a history, a sparse account) it opens every chunk, so its cost grows with retention × chunk count. Bound the read by the retention cutoff when it is finite and enforced, otherwise by a walk window that hands back a continuation cursor at the window edge instead of "end of history" — never hide a reachable row. An account-scoped lower bound that no writer undercuts ends the walk early: the merged chat history stops 7 days before accounts.created_at (the slack covers a YouTube live-chat backlog stored with its original timestamps; ZAF-2254), so an account without chat ends on its first page instead of walking the install's history window by window. Before relying on such a bound, check every dated writer, import, backfill and re-keying path. Measured (ZAF-2251, TSDB 2.30.1): the empty first page of the merged chat history on a 366-chunk platform_chat_messages cost 167 482 shared buffers cold / 14 583 warm (planning + execution) unbounded, 16 572 / 530 with a 30-day floor (31 chunks). Run such a read unprepared (.persistent(false) on the sqlx query): a cached prepared statement may switch to a generic plan after five executions, and a generic plan cannot exclude chunks by a bound parameter at plan time — it lists (and the executor locks) every chunk, excluding them only at executor startup. Measured (ZAF-2254): the generic plan of the same page lists 366 of 366 chunks; the unnamed statement plans 31 on every run. Note: any time predicate turns TimescaleDB's DeferredChunkAppend (per-chunk LIMIT pushdown) into a plain ChunkAppend; on a small, dense chunk the planner may then prefer Seq Scan + Sort of that one chunk — pin the plan with a volume test.
  • Hot-path queries (per message / event / sample / request) need an index-only or single-index plan; the reviewer may ask for EXPLAIN (ANALYZE, BUFFERS) on representative synthetic data. The volume tests provide that data set (small in CI, large on demand) and pin hot-path plans by their counters; the implementer or QA runs them.

3.2 FK indexes​

  • MUST: every FK column (or FK column set) is the leading column(s) of an index. A cascade/SET NULL on the parent otherwise seq-scans the child once per deleted parent row. Reason: 96 unindexed FKs on next; user deletion scales with the largest child table (F2).
  • A partial index does not count as the FK index (notifications.user_id used to have only a partial index → cascades seq-scanned). The one exception is a single-column partial index whose predicate is exactly <fk column> IS NOT NULL: the cascade's fk = $1 implies it.
  • Enforced: apps/api/tests/zaf2182_fk_on_delete.rs fails when an FK to users / accounts or into the extension tree (extensions / extension_versions / extension_installs) has no explicit ON DELETE action (RESTRICT only through its allowlist), or when such an FK or an FK to channel_connections does not lead an index.

3.3 Partial and covering indexes​

  • SHOULD: use a partial index when the hot query always carries the same predicate (WHERE revoked_at IS NULL, WHERE status = 'pending'). The query's WHERE must imply the index predicate literally, or the planner won't use it.
  • SHOULD: use INCLUDE (…) for small columns read by a hot lookup to get index-only scans; never INCLUDE JSONB/large text.
  • Time-ordered reads: index (tenant_key, time DESC) — matches "latest N for account X".
  • TSDB compressed chunks: a B-tree index on the hypertable is also used as a sparse (bloom/min-max) index on compressed batches (current TimescaleDB; measured on 2.30.1, the host test stack version). Add one for every non-segmentby equality filter a hot or GDPR query uses. Measured: GDPR erasure by (platform, user_id) decompressed 2 800/2 800 batches (228 ms on 3 M rows, ~1–2 min at 1 B rows); with the index 14 batches (66 ms, +9 % size).

3.4 CONCURRENTLY and the sqlx constraint​

  • sqlx 0.9 runs every migration in a transaction unless the file starts with -- no-transaction (sqlx-core migrate/source.rs). CREATE INDEX CONCURRENTLY / DROP INDEX CONCURRENTLY / REINDEX CONCURRENTLY cannot run in a transaction.
  • MUST for an index on a large/hot table (the review triggers list, or any table expected > ~1 M rows): CREATE INDEX CONCURRENTLY IF NOT EXISTS … in its own migration whose first line is -- no-transaction, containing exactly one statement (a multi-statement simple query is an implicit transaction block and fails the same way). The .down.sql is likewise -- no-transaction + DROP INDEX CONCURRENTLY IF EXISTS ….
  • A failed CONCURRENTLY build leaves an INVALID index; IF NOT EXISTS would then skip it. The recovery (manual DROP INDEX CONCURRENTLY + rerun) belongs in the PR body.
  • lock_timeout and the runner: the API runner (lo_database::run_migrations) runs migrations with lock_timeout = '5s' and retries on 55P03 — except -- no-transaction up migrations. Each of those runs in its own pass with lock_timeout = 0 (everything before it runs first, with the timeout; a final pass runs the rest). CONCURRENTLY waits for older transactions; under the timeout that wait would leave an INVALID index that the retry skips (IF NOT EXISTS) or trips over (already exists). Its locks never queue ordinary writers, so it needs no timeout. After migrating, the runner logs every INVALID index (pg_index.indisvalid = false) at error; recovery is a manual DROP INDEX CONCURRENTLY and re-running the statement. The migrate CLI sets no lock_timeout at all.
  • Small tables (< ~100 k rows, e.g. plans, feature_flags, oauth_clients): plain CREATE INDEX inside the normal transactional migration is fine.
  • Hypertables: CONCURRENTLY is not supported. Use CREATE INDEX … WITH (timescaledb.transaction_per_chunk) — also non-transactional, so the same one-statement -- no-transaction rule applies. It locks one chunk at a time instead of the whole hypertable.
  • Examples: apps/api/migrations/20261012200002_moderation_log_delete_target_index (CREATE INDEX CONCURRENTLY on platform_moderation_log), 20261012200001_drop_redundant_platform_users_index (DROP INDEX CONCURRENTLY on platform_users), apps/api/tsdb_migrations/20261011150000_chat_messages_platform_user_index (transaction_per_chunk).

4. Retention per data class​

MUST: every new table or hypertable declares its retention in the plan: a window, or "lifetime of parent row X (cascade)", or "kept forever because reason". "Unbounded" without a reason is a review block. Reason: ZAF-648 (no hypertable had retention) and ZAF-221 (chat_retention_days not enforced).

Data classStoreRetentionMechanismRule for new work
Chat messagesTSDB platform_chat_messagesplan-driven: plans.chat_retention_days (Free 365 d / Pro 1095 d), admin override in account_limits; 0 resolves to the 1095-day ceilingper-account sweep workers/chat_retention.rs: row DELETE by account_id + time in slices of ≤ 30 days walked from the oldest chunk (cheap on compressed chunks, 11 ms measured), then drop_chunks below the longest effective window + 1 day; the tail runs for 0 tooNew chat-derived data inherits the finite chat window. The same 0 = 1095-day rule covers events, chatter profiles and moderation data.
Platform eventsTSDB platform_event_logsplan-driven, same window as chatsame per-account sweepOrphaned account rows are removed by workers/chatter_orphan_sweep.rs; new event-derived data must follow the same deletion paths.
AuditTSDB audit_events12 months full → network fields (IP/GeoIP/UA) nulled → row deleted at 24 months; erasure-proof types (event_types::ERASURE_PROOF) deleted at 36 monthsjobs anonymise_old_audit_events (20260823000002) and purge_old_audit_events (20261011130000, drop_chunks past 36 months + row DELETE)Do not put PII in metadata. A new erasure-proof type needs the constant and an alter_job config migration (test tsdb_audit_purge).
Crawler raw samplesTSDB channel_history_stats90 daysadd_retention_policyRaw ≤ 90 d; anything longer is a rollup.
Crawler rollupsTSDB caggs _5m, _1h_5m: 12 months · _1h: none (kept by product promise)add_retention_policy on _5m (ZAF-2219)Reads older than 12 months fall back to _1h. New caggs need their own retention + compression (F4/L1).
Stream history / reportsTSDB channel_history*, milestoneslifetime of the accountaccount deletionFrozen JSONB lists (top_chatters, links, commands, keywords, announcements, pins) and account_bot_keys contain chatter identities → the instructed subject erasure scrubs them in every session of the instructing account (services::history::erasure, ZAF-2346; never the crawler account): counts stay, identity fields are blanked, erased: true. A new frozen column with chatter identities MUST be added to that scrub.
Channel chatter suppression listPG account_chatter_suppressions (ZAF-2641, data design ZAF-2649)until the account is deleted: no TTL, no jobaccounts ON DELETE CASCADEPK (account_id, platform, platform_user_id) + lower-cased Twitch/Kick logins (matched only against list entries without a user id); no reason column. Written only by the instructed erasure of one person in one channel, before that channel's history scrub. Read (fail closed, no cache) by services::history::suppression::subjects_for_account before every freeze and read-time derive of that account's stream history (sampler finalize, bot backfill/re-derive, chat_insights::derive / refreeze_keywords); error → session left unfrozen (retried next tick), read-time derive shows no lists. Never acts on another account or on Lumio Stats.
Lumio chatter suppression listPG chatter_suppressions (ZAF-2351)until withdrawn: no TTL, no joblifting an objection deletes the row; an 'erasure' row is not lifted by the objection pathHolds (platform, platform_user_id) + lower-cased Twitch/Kick logins, reason erasure / objection. Acts on Lumio's own surfaces only: read (fail closed, no cache) by services::history::suppression::subjects_for_lumio on every public stream read with the leaderboard on (services::public_stats, ZAF-2356, as the last read; error → top_chatters = None). It never blanks a channel's report, freeze, derive, share link or export.
Withdrawal-waiver proofPG withdrawal_waiver_consentslifetime of the account (account_id ON DELETE CASCADE); a deleted buyer only nulls user_idaccount deletion (services/storage_erasure.rs) cascades; no sweepOne row per paid Stripe checkout (ZAF-2690, design ZAF-2703). Proof columns are write-once; plan_id / extension_id are plain ids so deleting a plan or extension never rewrites proof. Extension purchases (extension_purchases.account_id / extension_id / version_id ON DELETE SET NULL) outlive the account and the extension but not their own 8-year retention (row below); the proof does not outlive the account.
Extension purchase ledgerPG extension_purchases (+ extension_licenses, extension_purchase_rollups)8 Europe/Berlin calendar years after the last booking event (GREATEST(created_at, updated_at); § 14b UStG / § 147 AO): a row is deletable from 1 Jan of (that Berlin year + 9). Held without end: disputed rows, a running subscription (stripe_subscription_id set, subscription_ended_at NULL), a completed row with neither a sub_… nor a PaymentIntent (mode unknown), a purchase a contract_declarations row references (FK RESTRICT; the declaration's own retention removes it first), a purchase that still has renewal invoices (row below)daily step workers::account_security::run_daily (ZAF-2800, design ZAF-2799): one CTE statement per batch of 500, at most 20 batches, FOR UPDATE SKIP LOCKED, no time index (small table, one seq scan a day); logs deleted, licences_written, held_*; no audit eventEvery UPDATE of a purchase row MUST set updated_at = now(), or the row is deleted early. A deleted one-time purchase leaves an extension_licenses row (no amounts or Stripe ids; accounts/extensions ON DELETE CASCADE), and has_purchased reads purchase OR licence. Deleted rows are added to extension_purchase_rollups, so totals computed from the ledger (deletion snapshot, analytics revenue, revenue summary) read live + rollup and never shrink through retention; only the per-row developer purchase list does.
Extension subscription renewalsPG extension_subscription_invoices8 Europe/Berlin calendar years after the last booking event, the same clock as the purchase ledger; disputed rows are heldsame daily step, before the purchase purge (ZAF-2706, design ZAF-2857): batches of 500, FOR UPDATE SKIP LOCKED; logs invoices_deleted, held_invoicesOne row per paid renewal invoice (invoice #1 is the purchase row itself). purchase_id is RESTRICT, so a purchase is held while it has invoices. Deleted invoices add their revenue_share to extension_purchase_rollups.completed_revenue_share (not to the purchase counts); the deletion snapshot and analytics revenue read live invoices + rollup.
Developer payout ledgerPG developer_payouts8 full calendar years after the year of the last booking event (§ 147 Abs. 1 Nr. 4, Abs. 3 AO): deletable from 1 January 00:00 Europe/Berlin of (Berlin year of GREATEST(created_at, updated_at) + 9). pending / processing rows are held and never deleteddaily step workers::account_security::run_daily (after the purchase purge) → db::extension_payments::purge_expired_payouts (completed / failed only, batches of 500, FOR UPDATE SKIP LOCKED)Detached from the profile (developer_id ON DELETE SET NULL, ZAF-2804, design ZAF-2803): a profile or user deletion keeps the row. Every insert goes through extension_payments::insert_payout, which copies the payee from the verified developer_verifications row (payee_*) and stores a masked payout_reference (Stripe acct_… in full, PayPal j***@domain, IBAN DE89****00); without a verified verification nothing is inserted (only verified developers are paid). A BEFORE DELETE trigger on developer_profiles refuses the delete while revenue_balance > 0 or a payout is pending/processing (SQLSTATE LM001 → 409 DEVELOPER_FUNDS_OUTSTANDING). A BEFORE UPDATE trigger moves updated_at on every change except the SET NULL detach itself, which is not a booking event and keeps the retention clock.
Developer credit notes (Gutschrift)PG developer_credit_notes + write-once PDF in lo-storage (billing/credit-notes/<year>/<id>.pdf, private, sha256 on the row); numbering in credit_note_counters (one row per Berlin year, never deleted)8 Berlin calendar years after the issue year (§ 14b Abs. 1 UStG, § 147 AO): deletable from 1 January 00:00 Europe/Berlin of (Berlin year of issued_at + 9); an original and its cancellation are purged together, once both are past the cutdaily step workers::account_security::run_daily (after the payout purge) → db::credit_notes::purge_expired_credit_notes (groups by COALESCE(cancels_id, id), batch counts groups, plain FOR UPDATE); PDF keys go to storage_deletion_queue in the same statementDetached from profile and payout (developer_id / payout_id ON DELETE SET NULL, ZAF-2807, design ZAF-2806). One effective note per payout, issued in the payout transaction by extension_payments::insert_payout_with_note on every insert path; payout amount = gross_amount. A cancellation is a new row (cancels_id, own number, negated amounts). A BEFORE UPDATE trigger (SQLSTATE LM002) allows only the cancellation mark, the one-time PDF store, delivered_at and the FK detach.
Extension telemetryTSDB extension_events/errors/install_logs180 / 90 / 30 dadd_retention_policy—
Platform users / moderation / notesPG platform_users, platform_moderation_log, platform_user_notesplan-driven like chat (Free 365 d / Pro 1095 d, account_limits override wins), clamped to 1095 d — 0 or a missing account also gets 1095 d (ZAF-2272). platform_users W after last_seen_at; in a moderation state (effective ban, treatment, note) W after the last moderation activity (latest of last_seen_at, banned_at, treatment_updated_at, newest note edit — never updated_at, which reads and enrichment also bump); notes with their chatter (orphaned: W after updated_at); moderation log W after created_at. Per-platform caps (retention::PLATFORM_RETENTION_CAP_DAYS) only shortenworkers/chatter_retention.rs (ZAF-2200, ZAF-2272): per-account key-window sweep, no time index, reads every retained row each cycle (chatter_retention_last_cycle_seconds); autovacuum_vacuum_scale_factor = 0.02; event-driven erasure ZAF-2181; a disconnect deletes nothing here (ZAF-2641, the sweep is per account and keeps running)The ceiling retention::MAX_CHANNEL_DATA_RETENTION_DAYS (1095) is shared by the sweep and the admin validator (REST + GraphQL) — nothing may configure a longer window. New per-chatter tables MUST ship their erasure path.
Twitch AutoMod held-message logPG platform_automod_messagesplan-driven chat window W (COALESCE(account_limits, plans).chat_retention_days, Free 365 d / Pro 1095 d, clamped to 1095 d; 0 or missing = 1095 d) from held_at, for the row and its content (text, flagged spans, term text, emote/cheer refs) together, all outcomes including pendingone always-on deleter workers/automod_log_retention.rs (hourly, advisory lock, runs even with chat.chatter_retention_enabled = false; index (account_id, held_at, id); counts into chatter_retention_rows_deleted_total{table="platform_automod_messages"}); subject erasure/objection, account dissolution and the orphan sweep delete rows (a Twitch disconnect keeps them, ZAF-2641)content is capped at 4 KiB JSONB (emote/cheer refs dropped first, then term text, spans, text). Sender look columns sender_avatar_url, sender_color, sender_badges (badges dropped above 1 KiB) are a snapshot of platform_users at hold time and go with the row. The log is read live for the private Stream History tab and the merged chat history and is never frozen into a report or export.
Sessions / tokensPG sessions, api_keys, overlay_tokens, OAuth stateexpiry columns; Redis state by TTLexpiry + cleanupMUST: every credential table has expires_at or revoked_at and a prune job for rows dead > 30 d. Redis copies (session_valid) are deleted on revoke, not left to TTL (RedisClient::invalidate_session_validity on every session-revoke path).
NotificationsPG notificationsnone (S6)—SHOULD: read notifications pruned after 90 d, unread after 1 y.
CSP reports, login devicesPG/TSDBprune jobsjob—
Uploads / objectsstorage + PG metadatalifetime of the metadata rowonly if the delete path deletes the object (F3)the object storage section.

How retention is implemented​

  • Whole-table window on a hypertable → add_retention_policy (drops whole chunks; near-free). Requires chunk interval ≪ window (the TimescaleDB section, Chunk interval).
  • Per-tenant window (plan-driven) → batched DELETE … WHERE account_id = $1 AND <time> < now() - window per account; account_id is segmentby, so compressed batches are pruned by metadata. Batch per account, never one global statement.
  • Plain Postgres tables → a worker deleting in batches of ≤ 10 000 rows by an indexed time column, committing per batch (DELETE … WHERE ctid = ANY(ARRAY(SELECT ctid … LIMIT n)) or by PK range). Never one unbounded DELETE.
  • Hot Postgres table without a time index (the time column is rewritten on the hot path, so an index would make those updates non-HOT) → walk each account in key windows over its account_id-leading unique index (OFFSET n-1 LIMIT 1 gives the window's upper key), delete (lo, hi] with the retention predicate on the DELETE itself so a concurrently refreshed row is re-checked, then lo = hi. Each statement is bounded by the scanned window, not by matched rows. Reference: workers/chatter_retention.rs (ZAF-2200).
  • Anonymise instead of delete only when the row itself must survive (audit trail); null the PII columns via a job, and say in the plan which columns remain (pseudonymous ≠ anonymous) and when the row itself is deleted (audit: 24 months).

GDPR deletion paths (MUST)​

  • Every table/keyspace/object prefix that stores personal data (a chatter's id/name/message, a user's IP/UA/email, uploads) names its deletion paths in the plan:
    1. subject erasure (a viewer/chatter, chat_privacy.rs path),
    2. user deletion (users row, ZAF-2116),
    3. account dissolution (account_dissolution.rs),
    4. retention expiry.
  • Postgres cascades only cover Postgres. TSDB rows, Redis keys and storage objects need explicit code in each path — and that code must run even when the parent row is already gone (F1, F3).
  • An erasure query on a hypertable must be index-supported (§3, Partial and covering indexes sparse index), or it decompresses everything.
  • Retention wording and windows are signed off by Legal & Compliance; the data layer implements them.
  • A frozen copy of personal data (JSONB lists written at finalize, denormalised names) outlives its source's retention, so the subject erasure must reach it directly — in every session of the instructing account, not only in the sessions whose source rows it just deleted (channel data belongs to the channel, so the erasure acts per account, ZAF-2626). Reference: services::history::erasure::scrub_subjects(tsdb, account_id, subjects) (ZAF-2346): candidate ids collected read-only by JSONB containment over that account's rows (account_id = $1 on idx_channel_history_account_started; no index on the lists, erasure is rare; the crawler account is never scrubbed), then read id = ANY(…) FOR UPDATE and rewritten in batches of 500, counts kept, identity fields blanked, erased: true; fatal on failure, count in the audit metadata (scrubbed_history_session_count).
  • A derived read model (§5.6) is deleted with its source: erasing the source rows queues a recompute that removes the derived rows. An erasure path that must take effect at once also deletes the derived rows in its own transaction (the crawler opt-out erases the public_channel_browse* rows of the channel next to its channel_history_streams rows).

5. TimescaleDB​

5.1 Chunk interval​

  • MUST: every create_hypertable sets chunk_time_interval explicitly. Reason: the 7-day default made one channel_history_stats chunk ≈ 79 GB at 30 k live crawled channels (F4). channel_history_stats, platform_chat_messages and extension_install_logs use 1-day chunks; the other hypertables still run on the 7-day default.
  • Sizing rule: one chunk incl. its indexes ≤ ~1 GB uncompressed at the 12-month forecast, and the sum of all active (uncompressed) chunks across hypertables fits in ~25 % of the TSDB host's RAM. Minimum 1 hour.
  • Guide values: chat/events/audit → 1 day; crawler raw samples → 1 h–1 day depending on crawl scope (≥ 30 k live channels → ≤ 2 h); low-volume telemetry → 7 days.
  • Changing the interval (set_chunk_time_interval) only affects future chunks — the cost of getting it wrong is carried until old chunks age out.

5.2 Compression (columnstore)​

  • MUST: every hypertable that keeps data > 7 days enables compression with a policy. Every hypertable compresses, including the extension telemetry (extension_install_logs, extension_events, extension_errors) and both channel_history_stats caggs.
  • segmentby = the column every hot query and every retention/erasure sweep filters on by equality — normally account_id; for crawler data the channel key (session_id / channel id). Rule of thumb: ≥ ~1 000 rows per segment per chunk; a segmentby with too high cardinality (e.g. user_id) produces tiny batches and no compression.
  • orderby = the time column DESC (plus a second key if range scans use it). Declare it explicitly, don't rely on the default.
  • MUST: set the compression order explicitly. Every hypertable that enables compression sets both timescaledb.compress_segmentby and timescaledb.compress_orderby. compress_orderby leads with the time column (created_at DESC / time DESC) unless the data design review names a different dominant range predicate. Never rely on the default: when TimescaleDB compresses a chunk it derives the order from the table's indexes, which with the (id, <time>) primary-key convention (ZAF-433) puts id first (the chat table got id, created_at DESC, platform, lumio_user_id), and batch min/max metadata then cannot skip anything on a time predicate. Measured (ZAF-2197): the chat retention delete decompressed 25 batches and touched 236 k buffers with the default order, against 1 batch and 11.7 k buffers with created_at DESC.
  • Changing the order of a hypertable that already has compressed chunks: the new setting only applies to chunks compressed from then on; TimescaleDB stores the order per chunk. Never recompress inside the migration (one transaction at API start: every chunk stays locked until commit and is inflated to its raw size). Rewrite the old chunks with a TimescaleDB job that recompresses a few chunks per run, newest first, one chunk per transaction, with compress_chunk(chunk, recompress => true) — see recompress_stale_orderby_chunks (ZAF-2197), which also covers any later order change on the four hot hypertables once re-enabled with alter_job(id, scheduled => true).
  • Compress after the active write window: compress_after ≥ chunk interval and ≥ the late-arrival window (chat 1 day, crawler 1 day, audit 7 days).
  • Updates/deletes on compressed chunks work (TSDB ≥ 2.11) but decompress the touched batches — keep them per-segment (retention by account_id) or index-supported (§3, Partial and covering indexes).
  • Syntax: ALTER TABLE … SET (timescaledb.compress, timescaledb.compress_segmentby = '…', timescaledb.compress_orderby = '… DESC') + add_compression_policy (repo idiom). The 2.18+ columnstore names (timescaledb.enable_columnstore, add_columnstore_policy) are equivalent; pick one per migration.

5.3 Retention​

  • Window per the retention section. add_retention_policy(… if_not_exists => TRUE). A cagg's raw source retention must be longer than the cagg's refresh window (_5m/_1h refresh 2 d / 30 d vs raw 90 d — OK).

5.4 Continuous aggregates​

  • Use a cagg when a dashboard/stats query aggregates > ~100 k raw rows per request or the same aggregate is read repeatedly.
  • MUST: each cagg has a refresh policy, its own retention policy (or a stated "kept forever" reason) and compression if kept > 30 days.
  • Hierarchical caggs (_1h from _5m) are preferred over two aggregates over raw.
  • Real-time aggregation (materialized_only = false) only where freshness < refresh interval matters; it adds a raw scan per query.

5.5 Plain tables in TSDB​

  • Allowed for read models next to hypertables (the store decision tree row 4). Same Postgres rules apply (PK, indexes, retention) — "it's in TSDB" is not a retention policy.

5.6 Derived read models (per-entity summaries)​

When a request would otherwise aggregate every row of a growing table (the public browse grouped every channel_history_streams row on each request), keep a per-entity summary next to it. The public browse read model (public_channel_browse*, ZAF-2300) is the reference implementation:

  • Summarise what no longer changes. The summary covers closed rows only. Rows that change every minute (open streams) go into a small overlay table that is rebuilt as a whole on a fixed cadence (DELETE + INSERT in one transaction — not TRUNCATE, whose ACCESS EXCLUSIVE lock blocks readers). The overlay stores the combined (summary + open) values, so both are read by index.
  • Feed it from row triggers, not from every writer. A source table with many writers gets AFTER INSERT/UPDATE/DELETE row triggers whose WHEN clause is false on the hot path (here: an update of an open row), so the hot path does not even call the trigger function. The trigger only appends the entity key to an append-only queue table (plain INSERT, no unique key): a writer never waits on the drainer, and two writers cannot deadlock on the queue.
  • One drainer. A worker takes a transaction-level advisory lock (pg_try_advisory_xact_lock), removes the visible queue rows of up to N entities and recomputes those entities from the source in the same transaction. An uncommitted queue row is invisible to it and survives, so no update is lost. Two drainers could recompute one entity from two snapshots, which is why there is one.
  • One arm per row. An entity that has an overlay row is excluded from the summary arm through a flag (has_open) kept in sync by the overlay rebuild, with the summary's sort indexes partial on NOT has_open. An anti-join against the overlay would walk past every overlay entity that sorts ahead.
  • Backfill through the queue. The migration seeds the queue with every entity; the read path switches over on a persisted "first drain completed" flag.
  • Bound public reads. Public pagination caps its depth and counts with LIMIT cap + 1 (MAX_BROWSE_DEPTH).
  • Reuse the summary for indexing. The Stats channel sitemap reads public_channel_browse in stable primary-key order, limited to 45,000 rows per file, with a 12-month last_started_at floor and read-time opt-out exclusion. Its count uses the same table but does not subtract opt-outs. The channel share card probes public_channel_browse_live first, then public_channel_browse by (platform, platform_channel_id) and joins public_channel_metrics; it reads the main database for visibility only on a hit and probes the existing live-stream reader only when live. Neither path scans channel_history_streams per request. idx_pcb_sitemap covers the sitemap fields.
  • Pin the read plans at volume (tests/volume_plans.rs, P16): no read of the source table, and the same page cost at twice the source rows.

6. Cache (Redis, lo-cache)​

6.1 Key schema​

  • MUST: lumio:<domain>:<scope…>:<id…> — lowercase, : separated, _ inside segments. <domain> is one of the existing namespaces (perms, chat, chatters, events, emotes, kick, yt, spotify, ratelimit, overlay, widget, session, apikey, …) or a new one named in the plan.
  • MUST — tenant isolation: any key holding tenant-scoped data contains the tenant id (account_id, and user_id where per-user) as a segment, and the code that reads it derives that id from the authenticated context, never from client input alone. Keys holding credentials also contain the credential's identity (client_id), not just the platform. Reason: ZAF-897 cache leak class; lumio:oauth:app_token:{platform} lacks client_id (latent, audit notes).
  • Values: JSON (or the existing binary codec) with a schema version in the key or value when the shape can change (lumio:x:v2:{id}), so a deploy doesn't read the old shape.
  • SHOULD: single values ≤ ~100 KB; hashes/lists/sets ≤ ~1 000 members unless read with HSCAN/LRANGE windows. Reason: HGETALL lumio:chatters:* up to 5 000 fields ≈ 1 MB; emote-set blobs of several hundred KB — the likeliest causes of the > 500 ms tail (ZAF-1532).

6.2 TTL​

  • MUST: every key has a TTL, set atomically with the write (SET … EX, SET … PX, or a pipeline/MULTI/Lua — never INCR then a separate EXPIRE, S2). Collections get EXPIRE refreshed on every write.
  • Allowed exceptions, each named in the plan: a buffer with a durable drain and a bounded length (chat buffer), and bounded static data (fonts). Reason: channel_online sets had no TTL until ZAF-2188 (S3; now a 7-day safety TTL refreshed on every online write); extension storage was Redis-only without TTL until ZAF-2186 (F6).
  • TTL guide: auth/permission caches ≤ 5 min (and explicit invalidation, §6, Invalidation); profile/enrichment 1–4 h; platform metadata (emotes, badges) 1–24 h; OAuth state ≤ 10 min; rate-limit windows = window length.

6.3 Invalidation​

  • MUST: every cache of authorisation data (permissions, roles, memberships, sessions, API keys) is invalidated synchronously in the same code path as the write, on both GraphQL and REST (protocol parity). TTL is only the backstop. Reason: REST update_role/delete_role used to skip the lumio:perms:* invalidation GraphQL did → revoked permissions stayed usable 5 min via REST (F5; both now call services::roles, ZAF-2185); session_valid was not deleted on revoke until ZAF-2188 (S4).
  • Invalidation = DEL of the exact keys, after the DB commit. If the set of affected keys is not computable, use a generation key instead of pattern deletes: a unique token (never a counter — an expired counter can repeat a value) whose TTL outlives the entries, read in the same MGET as the entry and stamped into the entry's value (lumio:perms:ver:{user} / lumio:perms:epoch, ZAF-2235).
  • Cross-replica in-process caches are invalidated via a pub/sub channel that has a subscriber — a published channel with no subscriber is dead code (the former PERMISSION_INVALIDATE constant was deleted for this reason, ZAF-2185).

6.4 Forbidden patterns on the shared connection​

  • MUST NOT: KEYS, SCAN over the full keyspace (MATCH lumio:*… without a hash-tag/index set) on a request or periodic path, unbounded LRANGE 0 -1 / SMEMBERS / HGETALL on collections that can grow, FLUSH*, blocking commands (BLPOP, XREAD BLOCK) on the multiplexed connection. Keep an explicit index set (lumio:<domain>:index:{account} with TTL) if you need to enumerate keys. Reason: one multiplexed connection carries every command; one slow command stalls all of them (ZAF-1532); chat flush did a full-keyspace SCAN every 30 s per replica until ZAF-2188 (S1) — it now reads the index sets lumio:chat:{buffer,deadletter}:pending, maintained in the same MULTI/EXEC as the list writes, and scans only at worker start and ~15 min later to heal them.
  • Subscribers and anything blocking use a dedicated connection (already the pattern).

6.5 Stampede protection​

  • SHOULD for any key whose miss costs > ~50 ms or hits an external API/rate limit: single-flight on miss (SET lumio:<domain>:lock:{id} NX PX <ms>; losers wait briefly or serve stale), plus TTL jitter (±10 %) for keys written in bulk.
  • SHOULD for external-API-backed data (Helix, InnerTube, Kick, 7TV): serve-stale-while-revalidate — keep a soft TTL in the value and a longer hard TTL on the key.
  • Negative caching (short TTL, 30–60 s) for "not found" from external APIs.

6.6 Latency & memory budget​

  • Budget: p99 < 5 ms per command, server-side; nothing in a request path may exceed 50 ms. The client response timeout is 2 000 ms (command_timeout_ms, lo-config/src/common.rs) — a timeout there is an incident, not a tuning knob.
  • Every plan adding a keyspace gives a memory estimate (keys × avg size, at 12 months). Redis has no maxmemory today (H1, ZAF-2190); with noeviction a full Redis fails writes loudly — so the memory estimate is a capacity commitment.
  • Metrics: redis_command_duration_seconds by command (no key, no account id as label — label-cardinality rule).

6.7 Pub/sub channels​

  • Name lumio:<domain>:<event>[:{account}]. Pub/sub is fire-and-forget: a consumer that must not miss a message needs a durable source to resync from (DB read on reconnect). Every published channel has a named subscriber in the plan.

7. Object storage (lo-storage)​

7.1 Naming​

  • MUST: accounts/<account_id>/<kind>/<uuidv7>[.<ext>] for tenant objects (…/uploads/…, …/sounds/…, …/avatar/…); users/<user_id>/… for user-owned objects; content-addressed shared objects (extensions/<ext>/uploads/<sha>) only for data that is not personal, or with a reference-counted delete (ZAF-2183). Legacy <account_id>/… upload keys are not renamed.
  • MUST: the tenant/user id is the segment right after accounts/ / users/, so a dissolution/user deletion can enumerate every kind with one delete_prefix("accounts/<account_id>/"). Reason: legacy uploads use {account_id}/{uuidv7} with no common prefix, which made them unreachable once their row was gone (F3).
  • Object keys never contain user-supplied names, emails or other PII; the original file name lives in the Postgres metadata row.
  • Every object has a Postgres metadata row (uploads, sounds, …) with storage_key, content_type, size_bytes, account_id, created_at. The row is the source of truth; an object without a row is an orphan.

7.2 Public vs private​

  • Public (served via public_url/CDN): only assets meant to be embedded publicly (overlay/widget assets, avatars, published extension bundles). Key contains an unguessable id; never listable.
  • Private (exports, GDPR data exports, non-published uploads): served only via presigned_download_url with expiry ≤ 15 min, after an RBAC check.
  • MUST: the plan states which one; default is private.

7.3 Cache headers​

  • Immutable keys (new key per new content — the default with uuidv7/sha256): Cache-Control: public, max-age=31536000, immutable. Never overwrite an object in place under the same key; write a new key and update the row.
  • Private objects: Cache-Control: private, no-store.
  • Today upload() sets only Content-Type — adding Cache-Control per kind is a lo-storage API change (Rust Backend).

7.4 Lifecycle & deletion​

  • MUST: every delete path of the metadata row (user action, cascade, dissolution, user deletion, retention) deletes the object after the DB commit, and failures go to a retry queue/table — not a log line. Cascaded row deletes can't call S3, so the deletion service collects keys before deleting the parent. The account delete does this through the storage_deletion_queue outbox (services::storage_erasure: keys queued in the delete transaction, deleted after commit, retried by the hourly account-security worker).
  • MUST: temporary objects (exports, previews) carry an expiry: a bucket lifecycle rule on the prefix (S3/R2/MinIO) and an expires_at on the row, so the local backend is covered too.
  • SHOULD: quota enforced at upload against max_storage_bytes (plan limit) by summing size_bytes — generic uploads check uploads + own sounds (db::uploads::create_upload_within_quota).
  • SHOULD: a periodic orphan sweep over a prefix (StorageClient::list_prefix / delete_prefix, S3 ListObjectsV2 or a local directory walk; the prefix must end in /).

8. Migration safety on large tables​

8.1 Format​

  • MUST: reversible pair <YYYYMMDDHHMMSS>_<name>.up.sql / .down.sql (cutoff 20260410000000); .down.sql restores the schema exactly — apps/api/tests/migrations.rs round-trips it (Serena database/reversible-migrations). A .down.sql that cannot restore data (dropped column) restores the schema and says so in a comment.
  • MUST NOT: edit a merged migration — not even a comment. sqlx checksums the file; every database that applied it refuses to start (ZAF-433/434, ZAF-1769). Fix forward with a new migration.
  • MUST: a migration version is unique across open PRs — check open PRs for the same timestamp prefix (ZAF-711 merge ordering). TSDB migrations live in apps/api/tsdb_migrations/, never in migrations/.
  • sqlx::migrate!() embeds at compile time — a new file needs a rebuild of lumio-api.

8.2 Lock levels (tables in the the review triggers hot/large list, or > ~1 M rows)​

OperationLockRule
ADD COLUMN nullable or with a constant defaultACCESS EXCLUSIVE, metadata onlyOK. Volatile default (now(), gen_random_uuid()) rewrites the table → forbidden; add nullable, backfill, then set default. (TSDB also rejects volatile defaults on compressed hypertables.)
ADD COLUMN … NOT NULL without defaultrewrite/failsAdd nullable → backfill → ADD CONSTRAINT … CHECK (col IS NOT NULL) NOT VALID → VALIDATE CONSTRAINT → SET NOT NULL (uses the validated check, no scan).
ALTER COLUMN TYPEACCESS EXCLUSIVE + rewrite (except varchar widening, varchar→text)forbidden on large tables; new column + backfill + switch reads + drop old in a later release.
ADD FOREIGN KEY / ADD CHECKSHARE ROW EXCLUSIVE + scan… NOT VALID in one migration, VALIDATE CONSTRAINT (SHARE UPDATE EXCLUSIVE, doesn't block writes) in the next.
CREATE INDEXSHARE (blocks writes)CONCURRENTLY per §3, CONCURRENTLY and the sqlx constraint (hypertables: transaction_per_chunk).
ADD UNIQUE / PRIMARY KEY—Build the unique index CONCURRENTLY, then ADD CONSTRAINT … USING INDEX.
DROP COLUMNACCESS EXCLUSIVE, metadata onlyOnly after a release in which no code reads it (expand → migrate → contract).
RENAME table/columnACCESS EXCLUSIVE, instantBreaks running old replicas during a rolling deploy → expand/contract (new name + view/dual write) unless the table is unused.
VACUUM FULL, CLUSTERACCESS EXCLUSIVE + rewriteNever in a migration.
  • MUST: a migration touching a large table starts with SET LOCAL lock_timeout = '5s'; (fail fast instead of queueing every writer behind an ACCESS EXCLUSIVE wait; LOCAL scopes it to the migration's transaction). -- no-transaction files hold one statement and cannot set it; CONCURRENTLY takes no write-blocking lock anyway. The API's migration runner (lo_database::run_migrations) also sets a session-wide lock_timeout = '5s' and statement_timeout = 0 on its dedicated connection and retries a run that hits the lock timeout (every 5 s, 12 attempts). The migrate CLI binary (apps/api/src/bin/migrate.rs) runs sqlx's migrator directly without those settings, so the explicit SET LOCAL stays required.
  • MUST: after deploying a CONCURRENTLY or transaction_per_chunk index migration, SELECT indexrelid::regclass FROM pg_index WHERE NOT indisvalid; on the affected database (for hypertables, the TimescaleDB) returns no rows. Both builds are non-transactional; an interrupted one leaves an INVALID index (or chunk index) that is maintained on writes but never used for reads. Drop and rebuild anything it lists.
  • Compressed hypertables: schema changes that TSDB cannot apply to compressed chunks (some ALTER TYPE, drops of segmentby/orderby columns) require decompress → alter → recompress — that is an operation plan, not a migration, and needs the CTO + the SRE.

8.3 Backfills​

  • MUST NOT: UPDATE big_table SET … over all rows in a migration.
  • MUST: backfills run as an idempotent job/worker in batches of 1 000–10 000 rows by PK/time range, one commit per batch, a short sleep between batches, resumable (cursor or WHERE new_col IS NULL), behind an advisory lock so only one replica runs it. Precedent: milestones::backfill (advisory lock, batches, background at start).
  • On compressed hypertables backfill per chunk, newest first, and expect decompression cost — estimate it in the plan.

8.4 Deploy compatibility​

  • Old and new code run side by side during a rollout: a migration must not break the previous release's queries (no rename/drop of anything it reads). Sequence: expand (migration) → code using both → contract (later migration).

9. Template — ## Data design section (Trigger 1 deliverable)​

Written into the chain's plan document (or as a comment on the parent if the document can't be written). Omit sections that don't apply with n/a — <why>; never omit Retention or Verdict.

## Data design

**Reviewer:** Data Architect · **Date:** YYYY-MM-DD · **Standard:** data-design standard v1 (the Data Architect review)
**Scope reviewed:** <plan revision / sections>

### Store choice
- <data>: **<Postgres | TSDB hypertable | TSDB cagg | TSDB table | Redis | pub/sub | object storage>** — because <decision-tree row + reason>.
- Not <alternative>: <why not>.

### Schema & keys
| Table | PK | Columns (type, null, default) | FKs (ON DELETE) | Notes |
|---|---|---|---|---|

### Indexes
| Index | Columns / predicate / INCLUDE | Serves query | Selectivity | Build (`CONCURRENTLY` / plain / `transaction_per_chunk`) |
|---|---|---|---|---|
FK columns covered: yes / <list missing>.

### Retention & compression
| Data class | Window | Mechanism | GDPR paths covered (erasure / user deletion / dissolution / expiry) |
|---|---|---|---|
Hypertables: chunk interval <x> (chunk ≈ <GB> at 12 mo) · segmentby <col> · orderby <col DESC> · compress after <x> · caggs <refresh / retention / compression>.

### Cache
| Key | Value / size | TTL | Invalidated by (GraphQL + REST) | Stampede protection | Memory @12 mo |
|---|---|---|---|---|---|
Pub/sub channels: <name → subscriber>.

### Objects
Key layout · public/private · Cache-Control · deletion paths · lifecycle/expiry · quota.

### Volume model
| Table / keyspace | Rows/day | Size/day | 12 months raw | 12 months compressed | Assumptions |
|---|---|---|---|---|---|

### Migration safety
| Migration | Table (size class) | Statement | Lock level | Strategy |
|---|---|---|---|---|
Reversible `.up/.down`: yes · backfill: <job, batch size, idempotency> · no merged migration edited · version prefix free across open PRs.

### Verdict
**must** (blocks the build until in the plan):
1. …
**should** (fine now, cheaper than later):
1. …
→ **Approved** / **Approved with musts** / **Rework** (state which).

A review that finds nothing is one line under ## Data design: Fine as is: <store>, <retention>, <why it's bounded>. No musts.

10. Trigger-2 diff check (for reference)​

Before gh pr ready on a chain that had a Trigger-1 review, the Data Architect compares git diff origin/next...origin/<branch> -- apps/api/migrations apps/api/tsdb_migrations crates/lo-database crates/lo-cache crates/lo-storage against the ## Data design section, item by item (PK, indexes + build mode, ON DELETE, retention/compression calls, TTLs, invalidation call sites on both protocols, object deletion). Result as an issue comment; mismatches name file:line and the fix.