Data model

Every table below is tenant-scoped and RLS-protected unless noted otherwise. See Multi-tenancy for how that's enforced.

Auth

• Tenant: the isolation boundary everything else hangs off.
• User / Membership: bcrypt-authenticated operators. One membership per user in practice, roles unused beyond what already exists.

Accounts

• SocialAccount: platform (x / telegram / discord / linkedin / instagram), connectionType (browser_session / bot_token / manual / oauth_api, the last one unimplemented), status (active / needs_reauth / disconnected), an AES-256-GCM encryptedSecret, daily post counters, riskAcknowledgedAt for browser-session accounts, and a bounded performanceSummary JSON aggregate.

Content strategy

• BrandProfile: knowledge/voice, scoped to one account.
• Topic / ConfigTopic: shared topic pool, rotated per config via lastUsedAt.
• ContentConfig: the generation recipe. Model, topic strategy, schedule, requireApproval, similarity threshold, max regen attempts.
• NewsKeyword, PromptTemplate: supporting config for live-news topics and prompt assembly.

Posts

• Post: the status machine every draft moves through, draft → pending_approval → scheduled → posting → posted / failed, plus ready_to_copy for manual-posting accounts. Carries a contentHash for exact-duplicate blocking.
• PostedSource: per-account tracker of which news URLs have already been used, so live-news topics don't cite the same source twice.

Cost and billing

• CostEvent: one row per metered event (llm / web_search / platform_post).
• CreditBalance / CreditLedger: the balance itself plus an append-only ledger. 1 credit equals $1.
• IdempotencyKey: a generic (tenantId, key) intent/done ledger, used by publishing to guard against double-send on retry.

Reply Manage and Inbox

• Mention: one row per X mention, unique on (accountId, platformPostId) so a mention is only ever drafted once no matter how many times it's re-scraped.
• ReplyConfig: per-account requireApproval and active toggle for Reply Manage.
• DirectMessageThread / DirectMessage: scraped on demand when the inbox is opened, not on a background schedule.

Analytics

• AccountDailyMetrics: a daily rollup of engagement snapshots (likes, replies, reposts, impressions), unique on (accountId, date), keyed by the UTC day a snapshot was captured on rather than the day the underlying post went live. A single post contributes up to four snapshots spread across a week. This is what Analytics actually reads.
• TopicPerformance: the persisted output of the loop's scoring. Average engagement rate and the resulting multiplier, per account per topic.
• CompetitorAccount / CompetitorPost: tracked external handles and their scraped posts.