90 lines
2.7 KiB
Markdown
90 lines
2.7 KiB
Markdown
---
|
|
read_when:
|
|
- changing tables, IDs, or invariants
|
|
- planning Postgres support
|
|
---
|
|
|
|
# Data Model
|
|
|
|
Schema lives in `apps/api/internal/store/sqlite/migrations/`. The mirror in
|
|
`infra/migrations/sqlite` is for tooling and stays in sync. `infra/migrations/postgres`
|
|
is reserved for the future Postgres backend.
|
|
|
|
## IDs
|
|
|
|
Sortable ULID-style text IDs with semantic prefixes:
|
|
|
|
| Prefix | Object |
|
|
|---------|--------|
|
|
| `usr_` | user |
|
|
| `idn_` | identity (provider link) |
|
|
| `wsp_` | workspace |
|
|
| `chn_` | channel |
|
|
| `msg_` | message |
|
|
| `evt_` | durable event |
|
|
| `eph_` | ephemeral event (in-memory only) |
|
|
| `upl_` | upload |
|
|
| `inv_` | invite |
|
|
| `mlk_` | magic link |
|
|
| `ses_` | session |
|
|
|
|
## Tables (V1)
|
|
|
|
```text
|
|
users identities
|
|
workspaces workspace_members
|
|
channels
|
|
messages thread_state
|
|
reactions
|
|
events auth_magic_links / sessions
|
|
uploads message_attachments
|
|
direct_conversations direct_conversation_members
|
|
invites
|
|
messages_fts (FTS5 virtual)
|
|
```
|
|
|
|
Full SQL is in
|
|
[`apps/api/internal/store/sqlite/migrations/0001_initial.sql`](../apps/api/internal/store/sqlite/migrations/0001_initial.sql)
|
|
and
|
|
[`0002_auth.sql`](../apps/api/internal/store/sqlite/migrations/0002_auth.sql).
|
|
|
|
## Thread invariants
|
|
|
|
For any row in `messages`:
|
|
|
|
- Root: `parent_message_id IS NULL`, `thread_root_id = id`,
|
|
`channel_seq IS NOT NULL`, `thread_seq IS NULL`.
|
|
- Reply: `parent_message_id = root.id`, `thread_root_id = root.id`,
|
|
`channel_seq IS NULL`, `thread_seq IS NOT NULL`.
|
|
- DM: `direct_conversation_id IS NOT NULL`, `channel_id IS NULL`,
|
|
`parent_message_id IS NULL`, `channel_seq` used as the per-conversation
|
|
sequence.
|
|
|
|
Nested replies are forbidden — the API rejects replies to non-root messages.
|
|
|
|
## Sequences
|
|
|
|
- `channels.channel_seq` (computed): `MAX(channel_seq) + 1` per channel for
|
|
root messages, assigned in the insert tx.
|
|
- `thread_root.thread_seq` (computed): `MAX(thread_seq) + 1` per root message
|
|
for replies.
|
|
- `events.cursor`: globally sortable opaque cursor used by realtime
|
|
recovery.
|
|
|
|
## Soft-deletes
|
|
|
|
Messages set `deleted_at` instead of removing the row. This keeps
|
|
`channel_seq`/`thread_seq` stable for cursors and reconnect.
|
|
|
|
## FTS
|
|
|
|
`messages_fts` mirrors `messages.body` with `porter unicode61`. Three triggers
|
|
keep it in sync on insert/delete/update-of-body. See
|
|
[features/search.md](features/search.md).
|
|
|
|
## Postgres path
|
|
|
|
Postgres tables will live in `infra/migrations/postgres/`. The store
|
|
interface in `apps/api/internal/store/types.go` is the abstraction line —
|
|
handlers should keep calling store methods, not embed dialect-specific SQL.
|