Data model

This page answers where every kind of user-facing data lives. Schema source of truth in the monorepo: apps/server/src/db/schema.ts.

Mental model: chat ≠ channel

Surface	Canonical store	What the user sees
Support chat (messages, conversation state)	Intercom conversations + parts	Hub → Support thread
Tickets	Intercom tickets + parts	Hub → Tickets
AW Channel posts	PostgreSQL	Hub → Channel feed
Comments on posts	PostgreSQL	Comment thread under a post
CSAT / complaints	PostgreSQL (admin panel)	Close → rating; complaint form

Support messages are not copied into Postgres as a message log. Channel comments are not stored in Intercom.

Ownership matrix

Data	Canonical store	Related Postgres	Object storage (S3 / disk)	Client
Support messages / conversations	Intercom	conversation_reads, conversation_durations	Chat attachment bytes	In-memory liveStore only
Tickets + ticket parts	Intercom	csat_ratings (rated overlay)	—	RN tickets-seen is session-local
CSAT ratings	Postgres csat_ratings	Best-effort Intercom tag/note	—	—
Complaints	Postgres complaints	Best-effort Intercom note	—	—
Channel posts, buttons, media meta	Postgres channels, channel_posts, post_attachments, post_buttons	Soft status lifecycle	Media / poster bytes	—
Channel comments	Postgres channel_comments	comment_attachments, reports, bans, mods, events	Comment image bytes	In-memory while thread open
Reactions / views / button clicks	Postgres post_reaction_*, post_views, post_button_clicks	Denormalized view_count on posts	—	View dedupe in memory
Channel unread	Postgres channel_reads	—	—	Hub badge also client-derived
Devices / push tokens	Postgres devices	—	—	Registered from SDK props
Notification prefs	Postgres notification_settings	—	—	—
userId ↔ Intercom contact	Postgres user_contacts + Intercom contact	Contact profile attrs on Intercom	—	Opaque to SDK
Reasons / topic lists	Postgres reasons	Served as GET /api/config	—	—
Admin users	Postgres admin_users	scrypt password hash	—	—
Admin session	Signed HMAC bearer (not a DB table)	Revoked by deactivating user	—	sessionStorage in admin SPA
Admin audit	Postgres admin_logs	—	—	—
Webhook dedup	Postgres processed_events	Auto-delete after ~7 days	—	—
Host session JWT	Minted by your app	Validated with JWT_SECRET; not stored	—	Passed as sessionToken

Layers

┌──────────────────────────────────────────────────────────┐
│ Intercom                                                  │
│  conversations, messages, tickets, contact profile/tags   │
└──────────────────────────────────────────────────────────┘
┌──────────────────────────────────────────────────────────┐
│ PostgreSQL (@aw-chat/server)                              │
│  maps, reads, CSAT, complaints, channel, comments, admin  │
└──────────────────────────────────────────────────────────┘
┌──────────────────────────────────────────────────────────┐
│ S3 (or UPLOAD_DIR)                                        │
│  chat / channel / comment file bytes                      │
└──────────────────────────────────────────────────────────┘
┌──────────────────────────────────────────────────────────┐
│ Client SDK                                                │
│  React state only — no durable chat/comment cache         │
└──────────────────────────────────────────────────────────┘

Support chat & tickets (Intercom)

• Each support request is an Intercom conversation. The in-app UI shows one continuous thread; closed requests stay in history and a new request appends below.
• Message bodies, operator HTML, and conversation state (open / closed / snoozed) live in Intercom.
• Postgres only keeps:
  • conversation_reads — last-read watermark for unread badges.
  • conversation_durations — timing for admin analytics (on close webhook).
  • csat_ratings — whether this conversation/ticket was rated (and stars).
• Attachments: uploaded via POST /api/files/upload → S3 (or disk) → public signed URL handed to Intercom as an attachment.

Channel comments (Postgres)

• Table channel_comments (status: visible / hidden / deleted).
• Images in comment_attachments → storage keys on S3/disk.
• Moderation: comment_reports, comment_bans, comment_moderators, comment_moderation_events (DLP hits store a redacted snippet only).
• Client APIs under /api/channel/.../comments — see Comments guide and API.

Channel posts (Postgres)

• channels, channel_posts (draft / scheduled / published / hidden / deleted).
• Media metadata in post_attachments; CTA buttons in post_buttons.
• Engagement: reactions, views, clicks, channel_reads.

Files

Path	Bytes	Metadata
Chat attachments	S3 or UPLOAD_DIR	Owner-tagged key; Intercom holds the URL
Channel media	S3 or disk	post_attachments.storage_key
Comment images	S3 or disk	comment_attachments.storage_key

Public download: GET /files/:id?t=<hmac> (short-lived signature). Authenticated download: GET /api/files/:id. Production requires REQUIRE_S3=1.

Retention

What	Policy
Intercom conversations / tickets / contacts	Intercom workspace retention (outside this app)
DATA_RETENTION_DAYS set	Daily prune of post_button_clicks and admin_logs older than N days
processed_events	Rows older than ~7 days removed
post_views / reactions / comments / CSAT / complaints	Not auto-pruned
Local disk uploads without S3	Ephemeral in containers unless you mount a volume

Default: DATA_RETENTION_DAYS empty → analytics/audit kept forever.

In-memory (not durable)

Per process (lost on restart; not shared across replicas):

• Intercom read-coalescing cache
• /api rate-limit counters
• Admin login lockout map
• Circuit-breaker state

Before running more than one API replica, move shared state to Redis — see Deployment.

Client persistence

The SDKs keep chat, tickets, channel, and comments in React state for the mounted session. Unmount / process kill drops that cache; the next open re-fetches from the backend (and Intercom via the backend). There is no AsyncStorage / localStorage chat history in the SDK.