# Data model Authoritative once implemented in `apps/api/prisma/schema.prisma`; this document explains the entities and their intent. Naming below uses the English terms (pond = Teich). ## Overview ```mermaid erDiagram users ||--o{ user_identities : "logs in via" users ||--o{ sessions : has users ||--o{ ponds : "created" ponds ||--o{ pages : contains ponds ||--o{ labels : defines labels o|--o{ labels : "parent of" pages }o--o{ labels : "tagged with" pages ||--o{ page_versions : "has history" pages ||--|| page_content_cache : "derived text" pages ||--o{ page_links : "links to" pages ||--o{ attachments : has ponds ||--o{ attachments : owns users ||--o{ role_grants : "subject of" ponds ||--o{ role_grants : "scoped to" plugins ||--o{ pond_plugins : "activated in" ponds ||--o{ pond_plugins : activates pages ||--o{ comments : has users ||--o{ notifications : receives users ||--o{ watches : sets ``` ## Identity and access ### `users` | Column | Notes | | ----------------------------- | ----------------------------------------------------------- | | `id` (uuid) | | | `username` | unique, URL-safe | | `email` | unique, stored verified/unverified with `email_verified_at` | | `display_name` | shown at cursors, comments | | `locale` | UI language (ADR 0012) | | `is_site_admin` | boolean; Site Admin is a user flag, not a grant | | `is_site_admin_managed` | true when the IdP claim mapping set the flag (issue #217) | | `status` | `active` / `disabled` / `pending_verification` | | `created_at`, `last_login_at` | | ### `user_identities` (ADR 0007) `user_id`, `provider` (`password` now; `oidc:` later), `subject`, `credential` (Argon2id hash for `password`), unique on (`provider`, `subject`). ### `sessions` Opaque id (hashed), `user_id`, `created_at`, `expires_at`, `last_seen_at`, user-agent summary (for "active sessions" UI). ### `auth_tokens` Single-use tokens for e-mail verification and password reset: hashed token, `purpose`, `expires_at`, `consumed_at`. ### `role_grants` — the permission table (see `permissions.md`) | Column | Notes | | -------------------------- | --------------------------------------------- | | `id` | | | `pond_id` | every grant belongs to exactly one pond | | `subject_type` | `user` / `authenticated` / `public` | | `subject_id` | user id when `subject_type = user`, else null | | `role` | `pond_admin` / `editor` / `reader` | | `scope_type` | `pond` / `label` / `page` | | `scope_id` | label id or page id when scoped, else null | | `effect` | `allow` / `deny` | | `created_by`, `created_at` | audit | | `origin` | `manual` / `idp` (claim mapping, issue #217) | Unique on (`pond_id`, `subject_type`, `subject_id`, `role`, `scope_type`, `scope_id`). `pond_admin` grants are only valid with `scope_type = pond` and `subject_type = user`. ## Content ### `ponds` | Column | Notes | | -------------------------------------------- | ---------------------------------------------------------------------------------------------- | | `id`, `slug` (unique), `name`, `description` | | | `type` | `personal` (one per user, from self-signup) / `shared` | | `owner_id` | creator; personal ponds: the user it belongs to | | `settings` (jsonb) | fonts (ADR 0016), sidebar sort mode (`alpha` / `created` / `manual`), default page permissions | | `deleted_at`, `deleted_by` | pond-level trash (ADR 0013) | ### `pages` | Column | Notes | | ---------------------------------------- | ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | `id`, `pond_id` | | | `title` | also indexed for search weight A | | `slug` | unique per pond, for stable URLs and wikilink resolution | | `ydoc_state` (bytea) | current merged Yjs state (ADR 0003) | | `ydoc_updates` | append log table `page_updates(page_id, seq, update bytea)`, compacted periodically | | `sort_key` | manual sidebar ordering (fractional indexing) | | `classification` | VS-NfD marking level, enum `UNCLASSIFIED` / `VS_NFD` (ADR 0022, issue #204). A marking, not a protection mechanism: permissions ignore it; separation of levels is one instance per level. Default for new pages from `instance_settings` (`classification.newPageDefault`). | | `created_by`, `created_at`, `updated_at` | | | `deleted_at`, `deleted_by` | trash | ### `page_content_cache` One row per page, refreshed on persistence: `plain_text`, `markdown`, `html`, `outline` (headings JSON, for TOC plugins), generated `tsvector` column with GIN index (ADR 0010). ### `page_versions` (ADR 0013) `page_id`, `ydoc_snapshot` (bytea, self-contained), `trigger` (`auto` / `manual` / `pre_restore`), `label`, `contributor_ids`, `created_at`. ### `labels` `pond_id`, `name`, `parent_id` (nullable — hierarchy), `color`, unique on (`pond_id`, `parent_id`, `name`). Cycles are rejected at write time. `page_labels(page_id, label_id)` is the assignment table. ### `page_links` Wikilink index maintained on persistence: `from_page_id`, `to_page_id` (nullable when target does not exist yet — "phantom" links), `target_slug`. Backlinks = query by `to_page_id`. Creating a page with a phantom-linked slug resolves those rows. ### `attachments` (ADR 0011) `id`, `pond_id`, `page_id` (nullable — pond-level files), `file_name`, `mime_type`, `size_bytes`, `storage_path`, `uploaded_by`, `created_at`, `deleted_at`. ## Plugins (ADR 0008) ### `plugins` `id` (manifest id, primary key), `name`, `version`, `api_version`, `kind` (`code` / `section_style`), `mode` (`DISABLED` / `OPTIONAL` / `REQUIRED`, the instance mode — issue #72 sets it), `manifest` (jsonb — the full validated manifest, so serving/admin views never re-read disk), `installed_at`, `updated_at`, `removed_at` (soft-delete tombstone: set on uninstall, files removed, so existing `plugin_block` nodes can still resolve the manifest fallback). Unpacked bundles live on the plugins volume at `///`; the path is derived from id+version, not stored (issue #71). ### `pond_plugins` (`pond_id`, `plugin_id`, `enabled`) — only meaningful for `optional` plugins; `required` plugins are active everywhere. Rows cascade-delete with their pond or plugin, and are dropped when a plugin is uninstalled. ## Quotas and settings ### `instance_settings` Key-value (typed JSON) singleton set: registration mode, SMTP config (secrets referenced from env, not stored plaintext — see `security.md`), default quotas, upload allowlist, legal pages content (imprint, privacy), instance default locale. ### `quota_overrides` `subject_type` (`user` / `pond`), `subject_id`, `quota_key` (`editors_per_pond`, `readers_per_pond`, `additional_ponds`, `storage_bytes`, `max_file_bytes`), `value`. Resolution: pond override → user override → instance default (most specific wins, mirroring the permission philosophy). ### `pond_usage` Cached counters per pond: `storage_bytes_used`, `editor_count`, `reader_count` — updated transactionally, reconciled nightly. ## Collaboration support - `collab_tokens` are **not** stored — they are short-lived signed JWTs (ADR 0007). - `mail_outbox`: pending/sent e-mails with retry state (ADR 0002 — no broker). - `jobs`: conversion jobs for import/export (ADR 0009) and maintenance jobs (compaction, trash purge, quota reconciliation) with status + timestamps. ## Read-access trail (`read_events`, issues #222–#224, ADR 0023) One row per read of a `classification = vs_nfd` page per channel and dedup window — separate from `audit_log` because volume, purpose and legal basis all differ. | Column | Notes | | ---------------------------- | ------------------------------------------------------------------------------------ | | `id`, `occurred_at` | composite PK (the partition key must be part of it) | | `actor_id` | reader, null = anonymous; **no FK** — evidence outlives accounts | | `session_key` | `session:` / `token:` / `job:` / `anon` | | `page_id`, `pond_id` | plain ids, **no FK** — evidence outlives purges | | `channel` | `page_view` / `no_js_shell` / `public_api` / `attachment` / `export` / `collab_join` | | `classification` | at read time; reclassification never rewrites history | | `dedup_key`, `window_bucket` | unique pair per partition — race-free dedup (#223) | | `window_seconds` | the row represents up to this many seconds of access | | `details` | small context (export format, attachment id) — never content | Storage shape (issue #224): RANGE-partitioned by month on `occurred_at`, with a DEFAULT partition as safety net (a lagging maintenance job must never make classified reads fail — the trail write is hard-failing by design). The daily `read-trail-maintenance` job creates months ahead (each with its per-partition dedup index) and applies the trail's own retention `readTrail.retentionDays` (default 365): whole expired months are DROPped without scanning, remainders deleted by range; every pruning run is audited as `read_trail.pruned`. Growth, measured on PG 17: **~1 MB per 1000 events** including indexes (≈970 bytes/row) — a 100-person instance at hundreds of classified reads/day stays in the tens of MB per year. Query path: `GET /admin/system/read-events` (Site Admin, filters `pageId`, `actor`, `channel`, `from`/`to`) — deliberately API-only, no panel: the trail is an examiner's tool, not a daily screen. ## Comments & notifications (later milestone) - `comments`: `page_id`, `author_id`, `body` (Markdown), `anchor` (optional serialized position), `resolved_at`, `created_at`, thread via `parent_id`. - `watches`: (`user_id`, `target_type` `page`/`pond`, `target_id`). - `notifications`: `user_id`, `type`, `payload` (jsonb), `created_at`, `read_at`; delivered in-app, optionally by e-mail digest. ## Deliberate non-entities - **No `organizations`/`teams`** — ponds + grants cover the vision; groups can be added as a new `subject_type` in `role_grants` without migration pain. - **No content translations** (ADR 0012). - **No per-keystroke authorship** — contributor granularity is the version snapshot (ADR 0013).