Database Design for Marketing OS
Atul Ranjan
Verified
Marketing OS - Database Design
1. Overview
Marketing OS runs on Firestore as its single primary datastore — 69 registered top-level collections, no subcollections, foreign keys as plain string IDs, and 122 hand-declared composite indexes checked into the repo.
That is an unusual choice for a system this shape. MOS is not a CRUD app: it is a content factory. A listing fans out into assets, a calendar plan, jobs, shots, and hundreds of pipeline tasks, which converge on a render, a human review, a delivery, and a billing event. The data model has to serve five very different access patterns at once:
Point reads on the hot path — a worker resolving its task's inputs, hundreds of times a minute.
Indexed range queries — the scheduler finding dispatchable jobs by
status + priorityScore, sweeps finding stale tasks by status + TTL.Real-time client subscriptions — a customer's calendar filling in live via
onSnapshot.Cross-entity reconciliation — does billing agree with the calendar, which agrees with jobs, which agrees with reviews, which agrees with delivered media?
Cross-team ownership — several collections are written by a different repo (Firebase Cloud Functions) and only read here.
The design brief was therefore not "model the domain." It was: make concurrent, retryable, partially-failing distributed work converge on one consistent answer, without a relational database and without a single collection scan in the request path.
This case study covers the decisions that made that work — and the two places where the model had to be corrected in production.
2. Problem It Solves
Problem Design response A pipeline step may run two, three, or five times (SQS at-least-once delivery + a reaper that re-enqueues) Content-addressed cache documents.
processedAssets keyed on (sourceAssetId, stepType, paramsHash) — a re-run either cache-hits or regenerates identical output. Re-running is never a correctness problem. Re-running a batch must not create duplicate rows Deterministic document IDs where idempotency matters: baseClips/{listingId}__{sourceAssetId}, templateVersions/{templateId}_v{N}, reviews/job-{jobId}, digestLocks/{YYYY-MM-DD}. Writing twice overwrites one doc instead of creating two. S3 paths carry run-specific UUIDs, so naive param hashing never cache-hits across runs Per-step stable cache-key builders (videoGenerateCacheKey, upscale key) that hash only content hashes and semantic params, and deliberately exclude startFrameS3Key / inputS3Key. Adding a field to a cache key would invalidate every existing entry Keys evolve conditionally — e.g. the provider tag is folded in only when seedance is requested, so pre-existing Veo entries hash identically to before the field existed. One overloaded status enum can't express "funded, rendered, claimed by an editor, revision requested, not yet delivered" Six orthogonal state axes (funding · production · execution · editor work · customer review · delivery), resolved by pure functions into one display state and one queue bucket. Two surfaces computing "credits reserved" from different sources disagreed on a live customer account One canonical authority per question. getWorkspaceState owns access + credits; the frontend renders the answer and never merges independent counters. A job's task list is unbounded (~180 tasks for a photo-to-video batch) but its shot list is not Embedded where bounded, referenced where not. jobs.shots[] is an embedded array; tasks is its own collection so sweeps can query by status and workerQueue across all jobs. Recovery sweeps and workers race on the same document 79 transactions across the codebase for leases and claims: recovery-attempt reservation, delivery claim, digest lock, notification dedupe upsert. The same error firing 40 times in a minute would page 40 times Dedupe-by-hash documents — sha256(errorClass|errorKind|service|jobId|taskId) in a 5-minute window, bumping occurrenceCount instead of re-notifying. Client SDKs must see live calendar updates without being able to corrupt anything Default deny-all security rules with narrow, field-scoped read allowances; every mutation goes through the API on the Admin SDK. A presigned URL stored in a document is a time bomb URLs are never persisted — mediaLibrary stores the S3 key and re-signs per read.Who depends on it
Workers — resolve inputs, claim tasks, and write outputs at pipeline rate.
The scheduler and self-healing sweeps — need indexed queries over task/job state to detect what can no longer progress.
The customer web app — reads its calendar live via
onSnapshot, everything else via the API.Editors and ops — need a queue derived from state, not a queue stored as state.
Billing / Cloud Functions (separate repo) — owns the credit ledger and Stripe mirror; this schema reads it and never rewrites it.
3. Core Design Decisions
3.1 Flat collections, explicit foreign keys
No subcollections. Every entity is a top-level collection with reference fields (
listingId, jobId, calendarEntryId, ownerId, sourceAssetId). The reason is query reach: a sweep that needs "every task in state X across every job" cannot do that against per-job subcollections without a collection-group index and a much weaker security story. Flat collections make every reaper, audit, and admin view a single indexed query.Names and typed refs live in exactly one place —
packages/shared/src/firebase/collections.ts — as a COLLECTIONS const plus a *Ref() accessor per collection. No string literal for a collection name appears anywhere else in the codebase.3.2 Document IDs carry meaning — or deliberately don't
Three ID strategies, chosen per collection by what the write needs to guarantee:
Strategy Used for Why UUIDv4
listings, assets, jobs, tasks, calendarEntries, jobErrorEvents, apiCostEvents Independent creation, no coordination, no hotspotting. Deterministic composite baseClips/{listingId}__{sourceAssetId}, templateVersions/{templateId}_v{N}, reviews/job-{jobId} Idempotent writes and single-get() resolution — a template shot's base_clip bind resolves its clip without a query. Natural key / singleton digestLocks/{date}, baseClipConfig/active-config, editorSettings/calendar-lead-templates, editorSettings/active-local-tour-template, infraStatus/production-health A distributed lock is a document that either exists or doesn't; a global config is one row.The singleton pattern is worth calling out: the daily-digest lock is a transaction that reads
digestLocks/{today} and creates it only if absent. Fifteen scheduler replicas, one digest — no external lock service.3.3 Embedded vs. referenced
jobs.shots[] is an embedded array of maps; tasks is a separate collection. Both are children of a job, so the split is a deliberate judgment:Shots are bounded (a template's slot count), always read together with the job, and must be updated atomically with the job's own status during recovery —
retryFailedProductionJob rewrites the whole shots array and the job status in a single batch write.Tasks are unbounded (~180 for a base-clip batch), are written by a different process than the one that reads them, and must be queryable across jobs by
status and workerQueue so the orphaned-task reaper can find stale work fleet-wide.The rule that falls out: embed when the child is bounded, co-read, and co-written; reference when it's unbounded or independently queried.
3.4 Content-addressed caching
processedAssets is the cache table for every generative step. The lookup is a three-field equality query — (sourceAssetId, stepType, paramsHash) — backed by a dedicated composite index, and it is the single most frequent query in the system.The discipline that makes it work is in the key, not the table.
paramsHash is sha256 of the params object with sorted keys, so property order can't change the hash. Per-step builders then narrow what goes in:S3 paths are excluded on purpose — they embed the run's shot and task UUIDs and would make every re-run a miss even when the keyframes are pixel-identical. The upscale key follows the same rule, anchored on the SHA-256 of the input mp4 bytes, because ffmpeg lanczos is deterministic: identical bytes in plus identical params gives bit-identical bytes out, so the cache is exact rather than approximate.
derivedAssets sits one level up: a job-scoped, shot-chain-level record exposing three outputs per shot (finalS3Key, imageS3Key, lastFrameS3Key) so a downstream shot can request pixel-exact continuity with its upstream neighbor's ending frame rather than its starting image. Cache and chain are separate tables because they answer different questions — "has this step ever been computed?" versus "what did this shot hand to the next one, in this job?"3.5 State as orthogonal axes, derived not stored
The editor lifecycle is the most state-heavy part of the product, and the thing that broke first when modeled as one enum. It is now six independent axes:
Axis Values Funding
unfunded · funded · consumed · refunded Production draft · scheduled · production_window_open · materializing · queued Execution pending · processing · waiting · running · stalled · failed · recoverable · rendered Editor work unclaimed · claimed · locked · in_repair · escalated · approved_internally Customer review not_visible · pending_customer_review · revision_requested · revision_in_progress · customer_approved Delivery not_delivered · delivered · download_only · approved_for_posting · posted · cancellededitor-lifecycle-resolver.service.ts takes (calendarEntry, job, review, taskSummary, renderState) and resolves each axis with a pure function, then folds them into one of 23 display states and one of 11 queue buckets. None of that is stored. There is no editorState field to drift, no migration when a bucket is added, and the whole resolver is unit-testable without Firestore. The underlying documents keep their own narrow status fields; the composite view is computed at read time.3.6 One canonical authority per question
The corollary: when a number must be stored, exactly one place may compute it. This was learned the expensive way —
creditsReserved had two independent derivations and they disagreed on a live account, showing "0 reserved" while work was committed. The fix was not a better formula; it was GET /api/account/workspace-state as the single answer for subscription access, credits, and trial-video count, with frontend surfaces rendering it and explicitly forbidden from doing their own Math.max(...) reconciliation.The reliability audit enforces the same boundary from the other direction: it reads only canonical authorities (
getWorkspaceState, the billing ledger, calendar entries, jobs, reviews, delivered media) and never re-derives credits or access from a display label.3.7 Two time representations, on purpose
Business dates —
scheduledDate, productionStartDate, deliveryDate — are ISO 8601 strings. They represent a calendar day a human chose, they sort lexicographically, they work in Firestore range queries unchanged, and they don't shift under a timezone conversion.System events —
createdAt, updatedAt, startedAt, completedAt, failedAt — are Firestore Timestamps, so they can be written with serverTimestamp() and ordered against a server clock rather than a worker's.The honest footnote: documents written across a year of shipping contain all three shapes a timestamp can take (
Timestamp, a raw {_seconds} map from an older SDK path, and ISO strings). Recovery code therefore routes every read through a timestampToMillis() coercion helper rather than assuming. That helper is the seam that let the schema evolve without a backfill.3.8 Denormalization, with a reason each time
Every denormalized field earns its place by serving a query or a rule:
processedAssets.listingId — lets cache invalidation scope to a listing without joining through assets.calendarEntries.ownerId — serves both the composite index for a customer's calendar and the security rule that lets that customer subscribe to it.jobs.calendarEntryId — lets recovery re-point the entry and review in the same batch as the job.jobs.errorEventCount / hasErrors — a counter bumped by FieldValue.increment so the monitoring list never has to count a subcollection.listings.assetSummary — running image/video/byte counts so eligibility checks don't scan assets.FieldValue.increment appears 60 times; it is the default for any counter, because two workers incrementing concurrently is the normal case, not the edge case.3.9 Security rules: deny by default, read-only by exception
The Admin SDK bypasses rules entirely, so
firestore.rules governs only what a browser can touch. It is a closed list:…with narrow exceptions, all read-only, all field-scoped:
Collection Who may read Why the exception exists
calendarEntries ownerId == uid, or admin Live onSnapshot during the calendar reveal instantPreviews createdByUid == uid, or admin Onboarding preview polling videoFeedback own docs where source == 'customer', or admin Customer feedback history adminActions admin, or actorUid == uid Append-only: create, update and delete are all false errorReports / errorGroups / templateIngestionJobs / stripeEvents adminLevel in [superadmin, support, developer] Admin dashboardsNo client write path exists anywhere in the database. The one nested path in the whole system —
stripeEvents/{customerId}/events/{eventId} — is written by Cloud Functions in the other repo and read-only here.A hard external constraint shapes the auth model: Firebase custom claims cap at 1000 bytes, which allows roughly 25–30 entries in
assignedListingIds. Past that, listing access has to move to a server-side Firestore lookup — the schema is designed so that switch is a middleware change, not a data migration, because listings.assignedUserIds already carries the inverse edge.3.10 Indexes as code
firestore.indexes.json is checked in and deployed: 122 composite indexes across 42 collection groups, plus 4 field overrides. The distribution tells you where the query pressure is — jobs (10), users (8), calendarEntries (7), jobErrorNotifications (7), metricSnapshots (7). The widest is five fields (metricDeltas: accountId, platform, entityType, entityId, windowEnd). Exactly one index uses arrayContains, because array-contains queries don't compose and are a dead end for pagination.The more interesting number: 43 of the 69 registered collections have no composite index at all. Those are reached by document ID — which is the point of the deterministic-ID work in §3.2. An index you don't need is the cheapest index.
4. Technologies Used
Database & access
Google Cloud Firestore (Native mode) — primary datastore, 69 top-level collections
Firebase Admin SDK — singleton init in
packages/shared/src/firebase/admin.ts; bypasses security rulesFirebase Auth custom claims —
role, adminLevel, assignedListingIds (1000-byte budget)Firestore transactions (79 call sites) — leases, claims, locks, dedupe upserts
FieldValue.increment / serverTimestamp / FieldValue.delete (60+ sites) — contention-safe counters and explicit field removalBatched writes (52 sites) — multi-document atomic repair
firestore.indexes.json — 122 composite indexes, version-controlled and deployedfirestore.rules — deny-by-default, read-only exceptionsFirebase Emulator Suite — local Firestore + Auth, no cloud dependency in dev
Type & validation layer
TypeScript 5 (strict) — 39
*.types.ts modules in packages/shared/src/types/, one per entity, with FirebaseFirestore.Timestamp typed explicitlyZod — 29 schema modules in
packages/shared/src/schemas/, validating every request body before it can reach a writeTypes and schemas ship in the shared package, so API, workers, and frontend components compile against the same shape
Adjacent stores (deliberately not Firestore)
Amazon S3 — all binary content; Firestore stores keys, never bytes and never presigned URLs
Amazon SQS — work queues; the message is a pointer, the task document is the state
CloudWatch — metrics and logs; time-series that would be an antipattern as documents
Cross-repo ownership
Firebase Cloud Functions (separate repo) own
creditTransactions, referrals, brokerageReferralRequests, stripeEvents, and Stripe synchronization. This schema reads them and never writes them.5. Architecture at a Glance
Where the schema lives
packages/shared/src/firebase/collections.ts — the only place collection names exist; 69 constants + typed refs + deterministic-ID builderspackages/shared/src/types/*.types.ts — 39 entity type modulespackages/shared/src/schemas/*.schema.ts — 29 Zod modules, one per write surfacefirestore.indexes.json · firestore.rules — deployed with the appsystem-design.md §3 — the original schema design document6. Integrity, Ownership, and Evolution
Integrity is enforced in code, not by the database. Firestore has no foreign keys, no uniqueness constraints, and no check constraints. Three mechanisms substitute:
Zod at the boundary — nothing reaches a write without schema validation.
Transactions at the contention points — recovery leases, delivery claims, digest locks, dedupe upserts. A race loser throws
ConflictError rather than silently double-acting.A standing audit for what neither can catch — the reliability control plane reads across collections and reports contradictions as typed findings (
CREDIT_ALLOCATION_DIVERGENCE, UNFUNDED_WORK_IN_EDITOR_QUEUE, DELIVERED_WITHOUT_MEDIA, ORPHANED_EDITOR_REVIEW, …). It is read-only by design: detection ships before repair, and no repair is added until its false-positive rate is understood.Ownership is explicit. Collections split into backend-owned, Cloud-Functions-owned (billing, Stripe, referrals), and a small deprecated set. That boundary shows up in the index file: 16 indexed collection groups aren't in
COLLECTIONS at all — most belong to the other repo's analytics and prediction subsystem, and one (audioAssets) is a removed collection whose index outlived it.Schema evolution is additive by default. Every field added since launch is optional, so a rolling deploy can't break an in-flight document:
users.focusType / users.leadSources — accounts onboarded before these shipped have neither; behavior is unchanged for them.jobs.kind — a discriminator (standard · local-tour · free-trial · editor_test) that unlocks placeholderListing, editorWebhookUrl, editorWebhookSecret without forking the collection.jobs.productionIncident / automaticRecoveryCount — stamped by the recovery layer onto existing documents.Step names migrated kebab-case → snake_case; the reaper normalizes both rather than requiring a backfill.
Removal is the harder half.
audioAssets (per-user audio uploads) was deleted outright because the user-upload model reintroduced exactly the licensing problem the audio-swap feature exists to avoid — the spec calls for a royalty-free library only. It was replaced by audioTracks, an admin-managed catalog seeded from S3. The lesson embedded in the comment left behind in collections.ts: record why a collection was removed, in the file where someone would otherwise re-add it.7. Success Criteria
No collection scan in the request path. Every hot read is either a
.doc(id).get() or an equality query served by a declared composite index. 43 of 69 collections need no composite index because they are addressed by ID.Re-running anything is safe by construction. Content-addressed cache keys plus deterministic document IDs mean a retry, a reaper re-enqueue, or a replayed SQS message converges instead of duplicating.
Derived state cannot drift, because it is not stored — six lifecycle axes and the editor queue bucket are computed at read time by pure, unit-tested functions.
Every stored number has exactly one author. Access, credits, and trial-video counts come from
getWorkspaceState; no surface recomputes or reconciles them.Cross-system disagreement is detected, not discovered by a customer — six named invariants audited continuously and reported as typed findings.
The client can read exactly what it needs and write nothing. Deny-by-default rules, field-scoped read exceptions, append-only audit log, all mutations through the API.
Schema changes deploy without a migration window — additive optional fields, discriminator-based variants, and coercion helpers at the read seam instead of backfills.
One source of truth for the schema itself — collection names, types, Zod schemas, indexes, and rules all live in version control, and a new collection isn't done until it has all five.
Like this project
Posted Sep 20, 2026
Database schema for a content pipeline: flat collections, content-addressed caching, deterministic IDs, and lifecycle state derived at read time, never stored.
