Every table, what it holds, and why it is shaped that way. Source of truth:
apps/api/prisma/schema.prisma.
Conventions
| |
|---|
| Ids | UUID, @db.Uuid |
| Columns | snake_case in the database, camelCase in Prisma, snake_case on the wire |
| Money | Integer minor units. Never a float, anywhere |
| Calendar dates | @db.Date — a budget period is not an instant, and storing it as one invites a timezone to shift it a day |
| Closed sets | Database enums, not strings. A typo must not become data |
| Tenancy | Everything org-scoped carries organizationId and is filtered on it |
Tenancy and identity
| Table | Holds | Notes |
|---|
organizations | The tenant | Every query filters by it |
users | People | role is a UserRole enum — it was free text, which let an unanticipated value become a privilege decision (S-002). hashedPassword is nullable: a social-only account has none. tokenVersion makes refresh tokens revocable (S-015) |
oauth_accounts | Linked Google/GitHub identities | Tokens encrypted at rest. Keyed on (provider, providerAccountId), so one provider account maps to at most one user |
audit_logs | Append-only trail | Includes ipAddress and login failures — a burst against one account is invisible if only successes are recorded |
rate_limits | Fixed-window counters | Postgres, not Redis. A counter is not a queue |
Billing
| Table | Holds | Notes |
|---|
subscriptions | Local read-model of Stripe | Stripe is the source of truth; this exists so an entitlement check is a database read rather than a network call. Holds no money and no card data, and can be rebuilt entirely from Stripe |
webhook_events | Every applied Stripe event id | The claim is made by INSERT before the work, so the primary key is the lock and a concurrent redelivery cannot double-apply (D-021) |
Projects
| Table | Holds | Notes |
|---|
projects | The unit of work | channels is an array of MarketingChannel. Slug unique per organization — two tenants may both have q4-launch |
project_budgets | One amount per channel per period | Unique on (project, channel, periodStart): re-allocating updates the row rather than stacking, so the total is never a sum nobody can reason about. currency is stored alongside the amount so a later currency change cannot silently reinterpret history. spentMinor is filled by ingestion, never typed in |
competitor_snapshots | One Instagram Business Discovery lookup: a competitor's public profile counts, recent posts and computed metrics — or our own account, measured the same way for a comparison (is_own_account) | F-105. Append-only, so a username's rows over time are its history; follower/following/post counts are columns so a trend is an indexed read. No token and no insights are ever stored. social_account_id SetNull: disconnecting the account keeps the history |
project_members | Who can see a project | This is the agency scoping mechanism. No row, no access — even inside the same organization |
Email broadcasts (F-111 · F-111.1)
| Table | Holds | Notes |
|---|
email_custom_audience_categories | One audience category a project defined for itself | Unique on (project, slug) where slug is the case/whitespace-folded name — per project, not per org: two projects in one agency routinely both want a "VIP", and two categories a CSV cell cannot tell apart would be filed at random. Referenced with two different delete behaviours: RESTRICT from email_contacts (deleting a category holding contacts would orphan a mailing list — the domain makes the caller reassign first) and SET NULL from email_broadcasts (a broadcast is history and must not block the delete) |
email_contacts | One person on a project's mailing list | Unique on (project, email), address stored lower-cased — case-preserving storage under a case-sensitive index is how a list mails Bob@x.com and bob@x.com twice. unsubscribed is a flag, never a delete: a deleted row is re-created by the next CSV import and starts receiving mail again. F-111.1 — the audience is exactly one of category (built-in enum, now nullable) or custom_category_id, enforced by a hand-written CHECK constraint. The rejected alternative was keeping category NOT NULL as a "fallback" beside the FK: a contact in the custom segment "VIP" would then still match WHERE category = 'active_clients', and any audience query forgetting AND custom_category_id IS NULL would mail the wrong list |
email_broadcasts | One email sent (or waiting) to one segment | Counters (recipientCount / sentCount / failedCount) are denormalised so the history list renders without aggregating recipients per row; written once at the end of dispatch from the actual rows. status is claimed conditionally (draft → sending via updateMany), which is the double-send guard. F-111.1 — same exactly-one audience shape as the contact, plus audience_label: the segment's name snapshotted at draft time, the same trick email_broadcast_recipients uses for the address, so "who was this sent to" survives the category being renamed or deleted |
email_broadcast_recipients | Delivery outcome per (broadcast, address) | The address is copied onto the row, not only referenced through contactId (which is SetNull) — the contact may later be edited or deleted, and "who did this broadcast actually go to" has to stay answerable. Status is dispatch only: we do not claim to know about opens or bounces |
Agentic layer
| Table | Holds | Notes |
|---|
agent_runs | One run: input, proposal, cost, decision | Both the execution record and the approval-queue entry, deliberately one row — a proposal without its provenance is what gets rubber-stamped. status moves only through the state machine, which has no edge from running to applied |
agent_run_steps | What it actually did | Append-only, with per-step usage so a run's cost is attributable |
Growth intelligence
| Table | Holds | Notes |
|---|
scraped_pages | A fetched page and its extracted facts | URL normalised before storage — two spellings of one page must not become two rows, or every count downstream is wrong. Cached aggressively: re-fetching is rude and buys nothing |
site_audits | One audit run | Score plus pages audited |
audit_findings | One thing worth fixing | Re-derived every run, never accumulated. A fixed page stops reporting without anyone marking it resolved — a stale issues list is the fastest way to make a tool nobody trusts. rule is a stable id (seo.title.missing) that the fix-PR pipeline keys off |
Social listening (F-104.2)
| Table | Holds | Notes |
|---|
listening_terms | A brand or competitor term a project tracks, its SocialCrawl sources and cadence | Unique per (project, term). last_run_at holds the cadence — the hourly sweep only LOOKS; a deploy cannot reset a schedule. run_started_at is an atomic in-flight claim (expires after 30 min) so a term never runs twice at once. |
listening_runs | One sweep of one term, or one SocialCrawl explorer search | Trigger (schedule / manual / explore), status (ok/partial/failed), credits used and remaining, items and new items, per-source summary, started_by_id. Explorer searches have no term_id and carry their keyword. The organization's monthly listening budget is the sum of credits_used here (D-056). Cascades with the project. |
listening_mentions | One post or comment a term surfaced | One row per (term, platform, external id), refreshed on every sweep that sees it (latest metrics, last_seen_at, seen_count); first_seen_at is what "new per day" counts. Metrics the platform did not report stay null. Full provider item kept in raw (D-053). engagement_total = likes + comments + shares + saves over whichever the platform reported (null when none) — derived at write time so the explorer sorts by engagement in the database; backfilled for existing rows (D-055). Platform-specific fields are read from raw at display time, not promoted to columns. |
Enums
| Enum | Values |
|---|
UserRole | owner · admin · member · agency · client |
ProjectRole | lead · contributor · viewer |
ProjectStatus | draft · active · paused · archived |
MarketingChannel | seo · aeo · paid_search · paid_social · organic_social · email · whatsapp · content · influencer · affiliate · pr · events |
SubscriptionStatus | trialing · active · past_due · canceled · incomplete · none |
AgentRunStatus | queued · running · awaiting_approval · rejected · applied · failed · cancelled |
AgentRunKind | plan_generation · campaign_optimisation · content_draft · site_audit |
AuditKind · AuditSeverity | seo/aeo · critical/warning/info |
EmailAudienceCategory | active_clients · inbound_leads · supply_partners — the built-in segments; a project's own live in email_custom_audience_categories |
EmailAudienceColor | slate · blue · green · amber · red · violet · teal · pink |
EmailBroadcastStatus | draft · sending · sent · partially_sent · failed |
EmailRecipientStatus | pending · sent · failed · skipped |
Adding a table
- Add the model, with a
/// comment saying why it is shaped that way — not what it is.
npm run db:push (dev) or db:migrate (a change others will get).- Add it to
apps/api/src/test/setup.ts's TABLES or tests leak state between cases.
- Add a row here.