Data Model

Data Model and design rationale

The schema lives in db/schema.sql. This page covers the decisions behind it, not every column. The design principle throughout: promote what you filter on, keep identity off the index, and never let the agent invent categories.

Customer-blind knowledge entries

knowledge_entries is customer-blind by design. Identity never enters search or the embedding, so retrieval matches on the fault, not on who reported it. save_knowledge_entry stamps created_by (a users FK), embeds the entry on save, and keeps customer identity out of both the searchable text and the vector.

Customer and version are properties of the ticket (work_items), not the lesson (knowledge_entries). This is what makes the archive reusable: a lesson about a fault applies regardless of which customer next hits it.

Controlled vocabulary

knowledge_entries.resolution_pattern is a controlled vocabulary (a FK into resolution_patterns, which starts empty), not free text: the agent picks an existing slug or leaves it unset. add_resolution_pattern is a deliberate, separate action, only taken when the user asks. This is what makes cross-team pattern queries group correctly instead of fragmenting across near-synonyms.

Components (list_components / add_component) are a hierarchical, per-product architecture glossary, fed conversationally: either you describe the app directly, or the agent proposes a component from a ticket and you confirm. Never silently invented. Naming variants are handled with aliases on one entry, not duplicate entries (lc, LC, line controller are one component with two aliases).

knowledge_entries.component_id is the validated taxonomy anchor. product_area is derived from the component hierarchy at write time and kept as a plain column only so the generated search columns can reference it (a generated column cannot join another table). You never pass product_area directly.

Promoted facets and signals

knowledge_entries.signals (error codes, config filenames, component names) are promoted into a real, indexed text[] field instead of being buried in structured, so they are searchable by trigram. A future search for 023 or TOO_MANY_STRINGS matches.

The facets cloud, resolution_clarity, learning_value, and hidden_fix are likewise real, indexed columns (promote what you filter on), so "all prod issues" or "high learning_value lessons to review" are queries, not scans. cloud deliberately has no CHECK constraint: the environment vocabulary is deployment-specific (prod/qa for one org, dev/demo/preprod for another). The app layer enforces a lowercase-slug shape and surfaces existing values (list_environments) for reuse.

Everything else stays in the free-form structured JSONB, which is validated against a known shape on save (extra keys kept) but never filtered on. Search results return it wholesale, so the agent has the narrative during consult.

Customers and versions

• work_items.customer_id is auto-matched at ingest by the requester's email domain against customers.aliases, so distributors and resellers fronting for one account map together. Fix a wrong or missing match with set_work_item_customer; corrections survive re-sync. customers starts empty (add_customer).
• work_items.observed_version is set manually with set_observed_version when a ticket states one. Never inferred.

fetch_work_item and get_context return the resolved customer_id, customer_name, and observed_version alongside the ticket, so the agent can reason about staleness narratively ("this customer is on v1.4, the lesson was fixed in v1.6") without any of it touching the search index.

Knowledge lifecycle

knowledge_entries.status is one of draft, approved, rejected, archived, deprecated:

• approved - live and searchable.
• deprecated - outdated but still surfaced in search, flagged, optionally pointing at a replacement via superseded_by. Issues resurface, so a flagged stale lesson beats a rediscovered one.
• archived - fully hidden from search.

A CHECK constraint forbids an entry superseding itself. update_knowledge_entry drives the transitions and uses optimistic locking (version bumped on every write; pass expected_version to guard against a conflicting concurrent edit, which returns 409).

Reference docs

reference_docs hold freeform project context (runbooks, architecture notes, config explainers) that is not issue-to-root-cause-to-resolution shaped. On save, each doc is chunked (reference_doc_chunks) and every chunk embedded, so docs surface in search_reference and get_context by semantic match. Same lifecycle idea, simpler: draft, approved, archived.

Tables at a glance

Table	Holds
teams, products	Org structure; products live under teams, both with slug plus aliases.
users, team_members	Accounts (role, optional password hash) and team membership.
settings	Non-secret runtime settings (see Runtime Settings).
source_connections, source_product_map	Configured sources and the group-to-product routing.
customers	Known customers with aliases for email-domain matching.
work_items, work_item_messages	Raw fetched tickets and their conversation.
knowledge_entries, knowledge_feedback	The archive and corrections/ratings on it.
components, labels, resolution_patterns	Per-product taxonomy and advisory tag vocabulary.
reference_docs, reference_doc_chunks	Freeform docs and their embedded chunks.
analysis_runs	Token-usage audit, with estimated cost in meta.

Generated search columns

knowledge_entries and reference_docs each carry two generated always as (...) stored columns: search_text (concatenated fields, for trigram) and search_tsv (a tsvector, for full-text). An immutable tachy_join() wrapper around array_to_string lets the generated columns fold array fields (symptoms, signals, tags) into the searchable text. See Search for how the three signals (FTS, trigram, vector) blend.