pablan/docs/data-model.md

Data model

All primary keys are UUIDs. All tables get created_at / updated_at via a shared mixin (omitted in the ER diagram). Enum-like columns are stored as varchar(32) backed by Python StrEnums — no native Postgres enum types. Naming rule: chat threads are conversations, login sessions are auth_sessions — never mix these up.

The ER diagram lives in diagrams/data-model.svg — one maintained source, updated with every model or migration change.

Tables (13)

(Plus alembic_version, Alembic's migration bookkeeping table.)

departments

id, name (unique)

users

id, email (unique), name, role (member|admin), password_hash (argon2), locale (varchar(5), nullable), department_id → departments A person is described by a DOCUMENT, not by a profile column: the one started from the person blueprint (GET /api/account/document). It is versioned, approvable and findable by the search, which a text column on users was not. locale pins the interface language (de | en); NULL follows the browser's Accept-Language. One column rather than a preferences table: it is the only preference that has to follow the person across devices. The colour theme deliberately does NOT live here — it is per-device and sits in the browser's localStorage. department_id is nullable (ON DELETE SET NULL).

auth_sessions

id, user_id → users, expires_at, created_at Server-side sessions; the row id doubles as the httpOnly cookie token (UUIDv4). Sessions expire after 14 days (PABLAN_AUTH_SESSION_TTL_DAYS) and cascade when the user is deleted. Instantly revocable (deleting rows logs the user out everywhere) — important because "employee leaves the company" is literally our core use case. No JWT.

templates

id, name, version, config (jsonb) config holds an authoring template — a Markdown skeleton plus per-section hints (see authoring-templates.md). Every row is the customer's own and fully editable — there is no read-only template. The YAML files in templates/ are a catalog of blueprints that an admin adds from; a blueprint has no row until it is added, and a fresh instance is seeded with four starter templates.

conversations

id, mode (query|insight), status (active|completed|abandoned), user_id → users One generic table for all modes. The only core mode is query (RAG Q&A); EE adds insight. Adding a mode requires no migration. Capture is no longer a conversation — it writes a Document directly (see app/authoring/), so this table holds no per-turn engine state, template link, or result-document link any more (the state, template_id and result_document_id columns were dropped). Deleting a user cascades to their conversations.

messages

id, conversation_id → conversations, role (user|assistant|system), content (text), meta (jsonb), created_at Full history is stored: state reconstruction after restarts, debugging/eval material, and (aggregated only) knowledge-gap reporting. Messages cascade with their conversation. Privacy rules below.

meta holds an assistant turn's citation snapshot ({"sources": [{document_id, title, heading_path, excerpt, used}]}, {} for user turns). Chunks are disposable derivatives that re-indexing replaces, so the rendered citation is stored with the message rather than referenced — reopening an old conversation still shows what was cited. used marks whether a passage grounded the answer or was only retrieved and dropped as too weak (a no-answer turn); the "?" context inspector shows the full retrieved set, the cited-source badges only the used ones.

documents

id, title, status (draft|published|archived), is_builtin (bool), visibility (public|department|restricted), content_md (text), meta (jsonb), author_id → users, department_id → departments

Markdown is the source of truth. content_md holds the canonical document; everything else (chunks, embeddings) is derived and disposable. meta holds the summary, extracted entities, and — for a captured document — the template config id it was created from, plus (when the capture started from a chat) the LLM topic summary of that conversation as context and its conversation_id, used as background for section refinement.

Capture writes here directly. A captured document is authored by the user, starting in draft (author-only, never indexed), and becoming published when its author says so — one action, no queue. There is no source_type: everything in the table was written in Pablan. The column and its upload member went with the tags removal — the day an import lands, a provenance column earns its place again, and can be reintroduced then.

Three statuses, and only three. draft is being written, published is readable and searchable, archived is retired. Doubt about the CONTENT is deliberately not a status: it is a review_request (below), because a document can be published for months and still carry an unanswered question about a number in it — which is exactly when readers need to be told.

No tags, and no freshness date. Both were removed rather than kept around: tags were write-only (free text, no way to know which ones existed, too inconsistent to filter by, and hybrid search already finds things), and verified_until marked documents without ever asking a person anything. A review request does that job — it names someone, carries a question, and can sit on a document published months ago. Treat a UI that displays or edits tags, or a date that flags a document as stale, as a regression.

author_id and department_id are nullable with ON DELETE SET NULL — documents survive their author leaving the company.

is_builtin marks the shipped help pages that describe Pablan itself (source: help/*.md, re-imported on every start). They are published, public and authorless, and the API refuses to edit or delete them (409 builtin_readonly) — the next deploy would overwrite the change.

chunks

id, document_id → documents, chunk_index (int), content (text), embedding (vector(1024) — bge-m3; dimension change = migration + reindex), tsv (tsvector, stored generated column, german config over the content AND the heading path), meta (jsonb)

Chunks are slices of a document (~400 tokens, split along Markdown heading hierarchy; each chunk stores its heading path in meta, e.g. "Ablauf: Reklamation › Schritt für Schritt", included as context in prompts and citations). The heading path is not decoration: it is embedded WITH the chunk (rag/indexing.embedding_text) and included in tsv, because a section saying "Solldruck 180 bar" never repeats which machine it belongs to and is otherwise unreachable by the name the asker uses. What is STORED as content stays the raw section. meta also carries denormalized filter fields (department_id, visibility) so retrieval doesn't need triple joins. Chunks are deleted and regenerated whenever the document or the embedding model changes, and cascade on document deletion. Indexes: HNSW on embedding (cosine), GIN on tsv; (document_id, chunk_index) is unique.

doc_permissions

document_id → documents, department_id → departments, level (read) Additional department read grants on top of documents.visibility — the mechanism behind multi-department sharing (a document reaches departments beyond its owning one). Managed through PUT /api/documents/{id}/departments (author/admin), which replaces the full set of extra departments; the permission filter's EXISTS doc_permission branch already unions them into reads and retrieval, so sharing is management, not new permission logic. Composite PK (document_id, department_id); both FKs CASCADE. Note the asymmetry with documents.department_id, which is SET NULL: deleting a department orphans its owned documents (they keep existing, department set NULL) but drops its grant rows here, silently removing that department's shared access — an admin lockout vector to weigh before deleting a department.

review_requests

id, document_id → documents, requester_id → users, reviewer_id → users, question (text, nullable), resolved_at (timestamptz, nullable), resolved_by_id → users, created_at, updated_at

"Please check this", addressed to one colleague, optionally about something specific ("do the 14 holiday days still hold?"). Open while resolved_at is NULL; the pair (resolved_at, resolved_by_id) is the answer. All three user FKs are ON DELETE SET NULL (a request outlives the accounts on either side of it); rows CASCADE with their document.

It is deliberately not a status, and it is orthogonal to one: a request can sit on a draft the author is unsure about OR on a document published months ago. An open request marks the document wherever it appears — the list, the detail page, and the sources under a chat answer (review_pending on every search hit).

Being asked is what grants the right to edit: a reviewer who spots a wrong number should fix it rather than file a second question about it. The grant is scoped to the open request and disappears with the answer — for a draft that also means the reviewer's read access ends there. The relationship is loaded with every document (lazy="selectin"), because whether a question is open decides both who may edit and how the document is marked.

document_events

id, document_id → documents, actor_id → users, action, content_md, title, visibility, meta, created_at An append-only audit trail: who changed or reviewed a document, and when. action is one of created | edited | published | archived | visibility_changed | review_requested | review_resolved. Content-bearing events (created, edited) snapshot the Markdown source of truth (content_md, title, meta) so a past version can be viewed or diffed — the disposable chunks are never snapshotted (rule 1); pure transitions carry no content snapshot. visibility is small, so it is recorded on every event. actor_id is ON DELETE SET NULL so the trail outlives the actor's account (like author_id); events CASCADE with their document.

This is where "who checked it" lives: review_resolved records the colleague who answered as actor_id, next to the review_requested that asked — the two events are the record that someone with the knowledge looked at this, which the review_requests row alone (one open flag) does not preserve once it is answered. History is read through the document's own permission gate (GET /api/documents/{id}/history), so it never leaks to a user who cannot read the document.

llm_settings

id, role (unique), base_url, model, api_key, created_at, updated_at Per-role endpoint overrides edited in the admin UI; at most three rows. NULL means "inherit from the environment" (precedence env < DB, per field — see architecture.md). api_key is plaintext because it is replayed to the endpoint; it is never returned by the API and never logged.

prompt_settings

id, key (unique), content, created_at, updated_at Admin overrides for shipped system prompts. Every prompt has a CODE default (app/prompts/defaults.py); a row exists here only when an admin has changed one, and content is the full replacement. Applied without a restart via a module cache (app/prompts/overrides.py), refreshed on every write — like llm_settings, and single-process by design (--workers 1). Resetting a prompt deletes its row so the code default takes over. Unlike LLM settings there is no .env layer: prompts have no environment representation, so the reset target is the code default. Keys: query_system, query_no_sources, refine_persona, refine_rules, grounding_framing, topic_summary, title, bio_persona, bio_rules.

jobs

id, type, payload (jsonb), status (pending|running|done|failed), run_after, attempts, last_error Postgres-backed background queue, claimed via FOR UPDATE SKIP LOCKED. Indexed on (status, run_after) for the claim query.

Permission model

Two layers:

1. documents.visibility as the fast default:
   • public — whole company
   • department — author's department only
   • restricted — only explicit grants
2. doc_permissions for additional department grants.

Authors always see their own documents: any status through the API (writing needs drafts), published-only in retrieval — unpublished documents are never searchable, not even for their author. A colleague with an open review request sees the document the same way: an open-request clause in readable_documents_filter but never in searchable_documents_filter, so being asked to check a draft reaches the API without ever leaking into chat/search. That clause is also the edit gate (can_edit = author, admin, or open reviewer): whoever is asked may fix what they find, until they answer.

Self-lockout guard. Before a visibility or grant change is committed, the proposed state is evaluated against the read rules (_guard_self_lockout, a Python mirror of readable_documents_filter). An author always keeps access as author, so this only bites an admin editing a document they do not own: the change is refused (409 self_lockout_warning) until they resend it with confirm_lockout, after which they may knowingly give up their own access.

The filter runs BEFORE the LLM ever sees anything. Retrieval (rag/retrieval.search) requires a user argument and applies the visibility CTE (visibility rules + doc_permissions + status = published) before vector/full-text search. It is structurally impossible to retrieve a chunk the requesting user may not read. The filter is shared with the documents API (rag/permissions.py) and always reads the live documents table — never the denormalized copies in chunk meta, which can be stale between an edit and the next reindex.

Retrieval (hybrid)

Sequence: diagrams/retrieval-sequence.svg

One SQL query: visibility CTE → in parallel (a) vector top-20 via embedding <=> :qvec (HNSW) and (b) full-text top-20 via tsv @@ websearch_to_tsquery('german', :q) → merged with Reciprocal Rank Fusion → top-k. Rationale: pure vector search underperforms on German technical terms, product names, and error codes; hybrid is the default.

The full-text half also works alone. chunks.tsv is a stored generated column (to_tsvector('german', content || ' ' || heading path)) behind a GIN index — an inverted index that needs no model at all. text_search() uses exactly that, with the same permission CTE, and query mode falls back to it when the embedding endpoint is unreachable. Keyword matching finds less than the hybrid path, so the user is told (fallback in api-protocol.md), but the knowledge base stays searchable when nothing is running behind the LLM config.

Privacy / GDPR defaults

• Deleting a user (offboarding) deletes their conversations and messages via ON DELETE CASCADE — the transcript is personal data of the departed employee; the published document is the legitimate artifact and survives authorless (author_id SET NULL).
• Users can delete their own conversations.
• Configurable retention: query-mode conversations are auto-deleted after a default of 90 days (retention_cleanup job).
• A captured document is private until its author publishes it (draft → published, the author's own action) — nothing enters the knowledge base without the writer's consent.
• Insights features (EE) only ever receive aggregated, anonymized data — never per-user raw messages.