Skip to content

ADR: Document Storage, DB Schema & Conflict Model

Status

Draft — design only. Backoffice UI and GDrive migration are out of scope (separate tickets).

Amendment 2026-06-26 — ixscraping pipeline (PR #1475) landed with a scraping.* schema. This amendment merges that schema into knowledge.*, replacing the proposed crawl_url_attempts table with a richer stateful scrape_pages table and enriching crawl_runs. The scraping.* schema is deprecated; ixscraping must be updated to write to knowledge.* (separate implementation ticket).

Amendment 2026-06-29 — three modeling fixes (this revision):

  1. Killed the scrape_pages ↔ documents/document_snapshots overlap. scrape_pages duplicated canonical_url, content_hash/raw_uri and decision, then "promoted" them into the durable tables. It is reduced to a thin per-run ledger and renamed crawl_page_decisions (run membership + LLM include/exclude decision + sample only). Because discovery already creates the documents node, scraping now writes a document_snapshots row directly — no promotion back-fill. The durable columns moved to documents (canonical_url, url_hash) and document_snapshots (content_hash, raw_markdown_ref, fetch_*, produced_by_run_id).
  2. Composite docs are now their own table (composite_documents), not documents rows. They reuse the single document_snapshots table (referenced by two nullable owner FKs + a CHECK, not a type tag — Postgres keeps real referential integrity that way); scrape-only snapshot columns are NULL for composite snapshots. The Knowledge Agent ingests both source documents and composite documents. document_sources is renamed composite_document_sources.
  3. Scrape source host decoupled from the tenant key. The host a doc is scraped from is not necessarily a Rose-hosted public.domains row (it may be a competitor / external blog / partner). A new source_hosts table holds scrape source hosts (RLS by site_domain, free-text host, no public.domains FK); crawl_runs and documents reference it. site_domain remains the tenant / RLS key.
  4. Hierarchy derived from path, not a stored parent_id adjacency list. path already encodes the tree for both URLs and uploaded files. The prior parent_id self-FK plus synthesized kind=folder rows was a denormalized cache of what path already says — and a cache that drifts: synthesized folders had to be created, recomputed, and kept id-stable across runs. Dropped. path is the single hierarchy source; the backoffice tree is built by sorting on path and splitting segments; subtrees and gate toggles use path-prefix matching. kind stays a display hint (page | file); folders are render-time grouping, not rows.

Net: nine knowledge.* tables (was seven). documents loses parent_id and the synthesized-folder rows.

Date

2026-06-09

Context

Today, per-client source documents live in Google Drive and are ingested by the document_loader Cloud Run Job (backend/apps/jobs/document_loader/), which drives the ixrag document pipeline (DocumentProcessor → GoogleDriveLoader → markdown-aware chunking → LightRAG → Neo4j + MongoDB). Knowledge FAQs are loaded separately from config.knowledge_faqs (Supabase) via SupabaseKnowledgeFaqLoader.

What already exists (do not claim these are missing):

  • Change detection: per-document SHA1 content hash (ixrag/document_pipeline/hash_utils.py) stored on the MongoDB doc_status row. Unchanged docs are skipped; changed docs are delete + re-insert.
  • Incremental loads: Redis-backed TimestampTracker ({env}:{domain_id}:last_run).
  • Config history: config.client_config_daily_snapshots (90-day retention).

What is genuinely missing and motivates this ADR:

  • No immutable scrape-time snapshot (raw md → processed md) for audit / reproducibility. (HTML is not retained — we keep the markdown views, not the source HTML.)
  • No version chain as a queryable history (the current SHA1 hash is a single per-doc state on doc_status, not a revision table).
  • No global, cross-entity conflict view (document vs document, document vs FAQ, etc.).
  • No way to block a page: neither a never-fetch path blocklist, nor "scrape but exclude from ingestion" (some pages should be stored as docs yet never reach RAG/skill/config).
  • Hard to surface any of the above in backoffice.
  • Google Drive itself is a liability (no versioning audit, no blocklist, permissions drift) and will be migrated out — object storage becomes the single body store for all docs.

Companion ticket (scraping pipeline + change detection): IX-3171.

Goal

Pick a storage model that lets us answer at any time:

  1. Which documents are indexed for a client?
  2. When were they last scraped / processed?
  3. What did the page look like at scrape time? (audit / reproducibility)
  4. Which documents contradict each other, or contradict an FAQ / skill / config?
  5. Which URLs / paths must never be fetched, or be scraped but never ingested?

Decision

Storage split

  • Blob store = Google Cloud Storage (GCS) bucket for immutable, document-scoped snapshot bodies. Each durable snapshot lives under the document id and the canonical content hash, with a stable content.md body and, for scraped pages, an optional raw.md companion. The platform already runs on GCP/Cloud Run and already serves widget bundles from gs://inboundx-cdn/, so GCS keeps the blob concern in the existing infra and IAM model. Cloudflare R2 is a viable alternative (it already backs the GEO pages worker, backend/cloudflare/cloudflare-geo-pages.js) but adds a second cloud for a backend-only concern. The content-hash path segment makes writes idempotent for a given document version.

Correction from prior draft: there is no "Cloudflare Artifacts" product in this stack. The repo uses GCS (widget bundles via gsutil, served through the Cloudflare CDN proxy) and Cloudflare R2 (GEO static pages). Cloudflare Workers here are an API gateway / edge proxy, not a blob store.

  • Metadata + version chain + conflicts = Supabase (queryable, backoffice-native, RLS).
  • Source documents themselves are NOT blobs — they need relational queries and CRUD. The blob store holds only snapshot markdown bodies: canonical content.md, optional scrape raw.md, and original binaries when reprocessing needs them (no HTML).

Why not blob store alone

  • No relational queries ("show all scraped docs for client X older than 30d").
  • Backoffice listing needs a DB join anyway.
  • Versioning is solved by content-hash + a metadata row.

Why not Google Drive (current)

  • No programmatic versioning audit.
  • No blocklist enforcement.
  • Hard to surface in backoffice.
  • Permissions drift.

Tenant key — resolve the schema conflict

This is the core conflict the ticket names. The two existing systems disagree on the tenant key, and the new tables must not invent a third convention.

  • Supabase isolates by domain / site_domain text (FK → public.domains.domain), with RLS via get_accessible_domains(). A public.clients row (id uuid) can own multiple domains, so client_id alone does not identify a knowledge scope.
  • Backend (MongoDB / Neo4j / LightRAG working dir) isolates by tenantId, a string derived from siteName (e.g. info.matera.eu → info_matera_eu), not a uuid FK.

Decision: partition the new tables by site_domain text (FK → public.domains.domain), matching every other Supabase knowledge table and reusing the existing RLS helper. Keep client_id uuid as a denormalized convenience column for cross-domain rollups, but it is not the RLS scope. This makes the new tables joinable to domains, config.knowledge_faqs, and config.client_configs without a mapping table.

Crossing into the backend stores still needs a translation. Neo4j / MongoDB / LightRAG are keyed by tenantId (sanitized siteName). So when the Ingestor writes to those stores it maps site_domain → tenantId with the same deterministic sanitization as rag_instance_manager.get_tenant_context (e.g. info.matera.eu → info_matera_eu) — a pure function, no mapping table. Supabase stays the system of record keyed by site_domain; tenantId is derived at the boundary, never stored as the Supabase partition key.

Unified document model — two axes + routing

Single body store: the body of every doc — raw scrape, internal upload, and composite/generated — lives as an md blob in cloud object storage (GCS), referenced from its snapshot. Google Drive is removed (see migration note); it is replaced by object storage as the universal body store, not by another relational store.

Two document tables. A document is a raw, single-origin unit (a scraped URL, an uploaded file, a feedback note). A composite_document is generated by the Knowledge Agent by distilling one or more source documents — it has its own validatable lifecycle and no scrape/crawl/tree concerns, so it lives in a separate table (composite_documents) rather than as a document row. Both share the single document_snapshots body store.

A document is described by two independent axes plus a separate routing fan-out:

  • origin — where the doc came from: scrape | upload | feedback | unknown (unknown for migrated docs whose provenance can't be reconstructed; gdrive kept transitionally during migration only — see below). Generated bodies are no longer a document origin — they are composite_documents.
  • knowledge_type — how it is treated: generic | faq.
  • destinations — where it is routed (rag / config / skill), in document_routings (a routing targets a source or a composite doc).

Flow (target state). Discovery first registers each URL/file as a document (status=discovered). Gate 1 (scrape_policy) decides fetch vs skip. Once scraped, the Knowledge Agent, per doc, either ingests it directly (gate 2) or combines it into a composite_document (gate 3) — a combined source doc is not ingested on its own; only the composite is.

flowchart TB DISC["discovery: sitemap / crawl / Drive listing"] --> NODE["document<br/>status=discovered<br/>path (tree key)"] NODE --> G1{"gate 1: scrape_policy"} G1 -->|"skip"| SKIP["stays discovered<br/>never fetched"] G1 -->|"scrape"| SCRAPED["scraped → snapshot<br/>raw md → processed md, status=active"] UP["internal doc upload"] --> SCRAPED2["document<br/>origin=upload (+ original_ref)"] FB["feedback"] --> SCRAPED3["document<br/>origin=feedback, faq"] SCRAPED --> KA{"Knowledge Agent<br/>per doc (gates 2 & 3)"} SCRAPED2 --> KA SCRAPED3 --> KA KA -->|"don't ingest"| BLK["status=blocked<br/>(stored, no routing)"] KA -->|"ingest directly"| RIN["routing: rag → Ingestor ingests this source doc"] KA -->|"combine"| CK["composite_document<br/>(separate table)<br/>composite_document_sources → parents"] CK -->|"routing: rag / skill / config"| CIN["Ingestor ingests the composite"] KA -. "if combined: parent source doc gets NO own rag routing —<br/>stays stored for provenance, not ingested alone" .-> CK

A composite_document is produced by the Knowledge Agent by distilling one or more parent source documents — its provenance is recorded in composite_document_sources (every source is a document row; there are no bare URLs). Note: feedback is an origin, not a knowledge_type — a FAQ-from-feedback row is a document with origin=feedback, knowledge_type=faq.

Schema overview (ERD)

The nine new tables and their links to the existing public.domains / public.clients. Body blobs (raw md, processed md, original binary) live in object storage and are referenced by *_ref columns on document_snapshots.

Schema placement: all nine new tables live in a new knowledge Postgres schema (knowledge.documents, knowledge.document_snapshots, …), following the per-concern precedent (config.*, geo.*). They reference public.domains / public.clients cross-schema; document_conflicts references config.knowledge_faqs for knowledge_faq entities. Table names below are unqualified for brevity — read them as knowledge.<table>.

The nine tables: source_hosts, documents, composite_documents, document_snapshots, composite_document_sources, document_routings, document_conflicts, crawl_runs, crawl_page_decisions.

erDiagram domains ||--o{ source_hosts : "site_domain (RLS)" source_hosts ||--o{ documents : "source_host_id (nullable)" source_hosts ||--o{ crawl_runs : "source_host_id" domains ||--o{ documents : "site_domain (RLS)" clients ||--o{ documents : "client_id (denorm)" domains ||--o{ composite_documents: "site_domain (RLS)" documents ||--o{ document_snapshots : "document_id (history)" composite_documents||--o{ document_snapshots : "composite_document_id (history)" documents |o--|| document_snapshots : "current_snapshot_id" composite_documents|o--|| document_snapshots : "current_snapshot_id" composite_documents||--o{ composite_document_sources : "composite_document_id" documents ||--o{ composite_document_sources : "document_id" documents ||--o{ document_routings : "document_id" composite_documents||--o{ document_routings : "composite_document_id" domains ||--o{ document_conflicts : "site_domain (RLS)" domains ||--o{ crawl_runs : "site_domain (RLS)" crawl_runs ||--o{ crawl_page_decisions : "run_id" documents ||--o{ crawl_page_decisions : "document_id" source_hosts { uuid id PK text site_domain FK "RLS scope (tenant)" uuid client_id FK "denormalized" text host "scraped hostname; NOT a public.domains FK" enum kind "own_site|external_reference" } documents { uuid id PK "reuse existing id on GDrive migration" text site_domain FK "RLS scope" uuid client_id FK "denormalized" uuid source_host_id FK "where scraped from; null for upload/feedback" enum kind "page|file (display hint)" text path "URL path or file path — sole hierarchy key" enum origin "scrape|upload|feedback|unknown" enum knowledge_type "generic|faq" text source_url "set when origin=scrape" text canonical_url text url_hash "SHA1(canonical_url)" enum scrape_policy "scrape|skip" uuid current_snapshot_id FK "null until scraped" enum status "discovered|active|blocked|stale|failed|superseded" text tags bool is_deleted } composite_documents { uuid id PK text site_domain FK "RLS scope" uuid client_id FK "denormalized" text title text composite_kind "feature_map|pricing|competitors|... (nullable label)" uuid current_snapshot_id FK enum status "generated|in_validation|validated|staged|prod|stale|superseded" uuid validated_by_user_id "nullable" bool is_deleted } document_snapshots { uuid id PK uuid document_id FK "nullable — XOR composite" uuid composite_document_id FK "nullable — XOR source" int version "monotonic per owner" text content_hash "SHA1" text processed_markdown_ref "universal" text raw_markdown_ref "scrape-only, null for composite" text original_ref "PDF/DOCX, scrape-only, nullable" enum render_mode "scrape-only" enum change_signal "scrape-only" uuid produced_by_run_id FK "scrape-only, nullable" uuid superseded_by FK "nullable" } composite_document_sources { uuid composite_document_id FK uuid document_id FK uuid source_snapshot_id FK "nullable, pins version" } document_routings { uuid document_id FK "nullable — XOR composite" uuid composite_document_id FK "nullable — XOR source" enum destination "rag|skill|config" text target_ref "skill slug / config slug.key" int priority "rag ordering" bool enabled } document_conflicts { uuid id PK text site_domain FK "RLS scope" enum entity_a_type "document|composite_document|knowledge_faq|skill|config_value" uuid entity_a_id "for *_document / knowledge_faq" text entity_a_ref "for skill slug / config key" enum entity_b_type uuid entity_b_id text entity_b_ref enum conflict_type enum severity "info|warn|error" enum status "open|acknowledged|resolved|false_positive" text detector_version } crawl_runs { uuid id PK text site_domain FK "RLS scope (tenant)" uuid source_host_id FK "host being crawled" text target_url "root URL scraped" text raw_map_uri "GCS path to Firecrawl map JSON" jsonb options "Firecrawl Map params" enum mode "website_mapping|incremental|reprocess_from_snapshot|manual" enum triggered_by "cron|webhook|backoffice|api" enum status "queued|mapping|mapped|failed" timestamptz started_at timestamptz finished_at int urls_discovered int urls_included "decision=include" int urls_scraped int urls_failed text error } crawl_page_decisions { uuid run_id FK "PK part 1" uuid document_id FK "PK part 2 — documents node (NOT NULL)" enum decision "unlabeled|include|exclude|needs_review" enum decision_source "system|operator|rule" text decision_reason float decision_confidence "0-1" jsonb decision_metadata "content_kind, evidence[]" text sample_uri "GCS: .../samples/{page_id}.md" timestamptz sampled_at text sample_error uuid produced_snapshot_id FK "snapshot this run created; nullable" }

Audit-column convention (generic audit columns omitted from the ERD for brevity, except where a timestamp is the table's primary event — crawl_runs.started_at/finished_at): every table has created_at timestamptz not null default now(); mutable tables also have updated_at timestamptz maintained by a trigger (the existing Supabase pattern). Immutable rows — document_snapshots, composite_document_sources — carry created_at only. crawl_page_decisions is stateful (one row per URL per run, updated in place across phases): it carries created_at + per-phase timestamps (sampled_at). Row versioning lives on document_snapshots.version; detector_version / extraction_metadata capture algorithm versions.

source_hosts — scrape source hosts (decoupled from tenant)

The host a document is scraped from is not necessarily a Rose-hosted domain. A client (site_domain ∈ public.domains) may have Rose scrape a competitor site, an external blog, or a partner doc whose host is not in public.domains. source_hosts is the registry of those hosts, owned by a tenant for RLS but free of any public.domains FK on the host itself.

  • id uuid PK
  • site_domain text FK → public.domains.domain (RLS scope — the tenant that registered the source)
  • client_id uuid FK → public.clients.id (denormalized)
  • host text — the scraped hostname (e.g. competitor.com, blog.external.io). Plain text, not a public.domains FK — that is the whole point; a source host can be external.
  • kind enum (own_site | external_reference) — own_site when host matches a public.domains row for this client; external_reference otherwise.
  • created_at, updated_at
  • Unique (site_domain, host).

The tenant key stays site_domain (the Rose client). source_hosts answers "where did this come from", site_domain answers "whose knowledge is it". Two tenants scraping the same external host get two rows (one per site_domain), keeping RLS trivial. Per-host scrape settings (cadence, robots, allow/block globs) can later hang off this row without touching documents.

documents — one row per logical doc per domain

  • id uuid PK (on GDrive migration, reuse the existing document id so the loader's content-hash dedup still matches and docs are not reprocessed — see migration note)
  • site_domain text FK → public.domains.domain (RLS scope)
  • client_id uuid FK → public.clients.id (denormalized, for cross-domain rollups; not RLS scope)
  • source_host_id uuid FK → source_hosts.id nullable (where the doc was scraped from; set when origin=scrape, null for upload/feedback). The host of canonical_url equals source_hosts.host.
  • kind enum (page | file) — display hint only. page = a scraped URL; file = an uploaded file. There is no folder value — folders are render-time grouping of path segments, not rows (see hierarchy note below).
  • path text not null — the sole hierarchy key: URL path (/pricing/enterprise) or file path (/decks/2026/pricing.pdf). The full tree (URLs and files) is reconstructed from path alone; no stored parent pointer.
  • origin enum (scrape | upload | feedback | unknown) — provenance kind (unknown = migrated doc with no reconstructable source; gdrive is transitional during the migration window only — see the migration note for how migrated docs are classified). Generated bodies are not here — they are composite_documents.
  • knowledge_type enum (generic | faq) — treatment (composite is gone — composites are a separate table)
  • source_url text (set when origin=scrape; null for upload/feedback sources)
  • canonical_url text (normalized: https, lowercase host, no UTM, no fragment; null when no URL)
  • url_hash text — SHA1(canonical_url), null when no URL. Fast lookup key for the scraper to match a discovered URL to its existing node (replaces scrape_pages.url_hash).
  • title text
  • scrape_policy enum (scrape | skip) — gate 1 (fetch?). skip = never fetched (the path-blocklist decision, materialized per node); only scrape rows are crawled.
  • current_snapshot_id uuid FK → document_snapshots.id (null until first scraped)
  • status enum (discovered | active | blocked | stale | failed | superseded) (discovered = known URL/file, not yet scraped — no snapshot; blocked = gate 2 scraped + stored but excluded from ingestion, Ingestor skips it, no rag/skill/config routing)
  • blocked_reason text nullable, skip_reason text nullable
  • last_seen_at, last_fetched_at, last_processed_at timestamptz
  • kb_indexed bool
  • tags text[]
  • is_deleted bool (soft-delete, audit trail)
  • created_at, updated_at

Unique (site_domain, canonical_url) where canonical_url is not null. Index (site_domain, path text_pattern_ops) — serves both the sorted full-tree read and prefix-scoped subtree reads (path LIKE '/blog/%').

Hierarchy from path, no parent_id. The tree (URL structure and uploaded files) is derived from path, which already encodes it. The backoffice renders it by ... order by path and splitting segments client-side — for the few-thousand-node scale of a domain this is a sort + group, no recursive CTE. A subtree (lazy-load on a big site, or a folder-level toggle) is a path-prefix scan (path LIKE 'prefix%'), index-served by text_pattern_ops. Intermediate folders (/blog/, /blog/2026/) are synthesized at render time from the segments — they are not rows, so there is nothing to create, recompute, or keep id-stable. A folder-level gate decision (skip / block a whole subtree) is a path-prefix rule (the glob editor, see backoffice surfaces) materialized onto the matching leaf rows' scrape_policy / status, so per-node queries stay flat.

Path normalization (required for a clean tree). path is the URL path of canonical_url (same normalization: lowercase host already stripped, no trailing slash, no fragment/UTM) or the upload file path. An index/landing page is the leaf at its own path (/pricing is one node), and a deeper child (/pricing/enterprise) makes /pricing also a branch — the same node is both, no separate folder node. Without this single rule the tree double-renders a path as folder + page.

One table, three gates. A node enters as status=discovered (from sitemap/crawl discovery or a Drive listing) — it exists in the tree before any fetch. Gate 1 scrape_policy decides fetch vs skip; gate 2 (status=blocked / having no rag routing) decides ingest vs not after scraping; gate 3 (composite_document_sources) folds a doc into a composite (subsumption). Because every URL and every file is one document row carrying path, the backoffice renders the whole website + file hierarchy — including skipped and not-yet-scraped nodes — from one sorted path read, annotating each node with its scrape/ingest state.

Two axes instead of one source_type: the prior draft conflated origin and treatment (and listed notion, which has no ingestion path). Splitting them lets a raw scrape be origin=scrape, knowledge_type=generic and lets feedback be an origin without inventing a "feedback" knowledge type. Composites are no longer an origin/knowledge_type combination on this table — they are first-class rows in composite_documents.

document_snapshots — immutable history (source and composite docs)

One snapshot table serves both document tables. The owner is addressed by two nullable FK columns with an exactly-one CHECK — not a (type, id) tag. Postgres cannot FK a single untyped owner_id to two tables, so a type tag would silently drop referential integrity; two real FK columns keep cascade + integrity on both sides.

Universal columns (every snapshot):

  • id uuid PK
  • document_id uuid FK → documents.id nullable
  • composite_document_id uuid FK → composite_documents.id nullable
  • CHECK (num_nonnulls(document_id, composite_document_id) = 1) — exactly one owner
  • version int (monotonic per owner)
  • content_hash text (SHA1 of normalized body, matching ixrag hash_utils.calculate_content_hash)
  • processed_markdown_ref text (historical column name: object key for the canonical content.md body used downstream; for a scrape this is the cleaned/processed md, for a composite this is the generated md)
  • extraction_metadata jsonb (chunker version, embed model, prompt hash; for a composite this carries the generator prompt hash + model — no extra column)
  • superseded_by uuid FK nullable
  • created_at

Scrape-only columns (NULL for composite snapshots):

  • raw_markdown_ref text (object key for the optional scrape-time / first-pass raw.md companion in the same snapshot folder)
  • original_ref text (nullable — object key for the original source binary when one exists: PDF / DOCX / XLSX uploads. Kept for reference + reprocessing with a better extractor. Null for scrapes, where HTML is not retained)
  • render_mode enum (plain_fetch | browser | sandbox)
  • fetch_status int, fetch_time_ms int, byte_size int
  • change_signal enum (etag_changed | lastmod_changed | hash_changed | jsonld_changed | forced)
  • produced_by_run_id uuid FK → crawl_runs.id nullable (which crawl produced this snapshot)
  • CHECK: all scrape-only columns are NULL when composite_document_id is not null

Unique (document_id, content_hash) and (composite_document_id, content_hash) — dedup unchanged refetches / regenerations. Index per owner (..., version DESC).

content_hash is SHA1, not SHA256, to match the existing pipeline (hash_utils.py:26, hashlib.sha1). Hash is for change detection, not security. If a migration to SHA256 is wanted, change both sides together — do not silently diverge.

composite_documents — distilled, validatable knowledge units

A composite_document is generated by the Knowledge Agent by distilling one or more source documents. It has its own validation lifecycle (generated → in_validation → … → prod) and none of the scrape/crawl/tree machinery, so it is a separate table rather than a document row. Its body lives in document_snapshots (owned via composite_document_id).

  • id uuid PK
  • site_domain text FK → public.domains.domain (RLS scope)
  • client_id uuid FK → public.clients.id (denormalized)
  • title text
  • composite_kind text nullable — optional label (feature_map | pricing | competitors | positioning | …); free text in this design, may FK a registry later
  • current_snapshot_id uuid FK → document_snapshots.id
  • status enum (generated | in_validation | validated | staged | prod | stale | superseded) — the Composite Knowledge lifecycle (see the CK lifecycle diagram)
  • validated_by_user_id uuid nullable, validated_at timestamptz nullable
  • is_deleted bool (soft-delete, audit trail)
  • created_at, updated_at

A composite is routed exactly like a source doc — via document_routings (rag / skill / config). The Knowledge Agent ingests both source documents (directly) and composite documents; a source doc folded into a composite gets no routing of its own (subsumption).

composite_document_sources — composite provenance (many-to-many)

Records what each composite doc was distilled from. Every source is itself a document — there are no bare URLs. A URL the Knowledge Agent reads is first materialized as a document (origin=scrape) whose snapshot carries raw md (raw_markdown_ref) and processed md (processed_markdown_ref). So provenance always points at a row, never a loose string.

  • composite_document_id uuid FK → composite_documents.id
  • document_id uuid FK → documents.id
  • source_snapshot_id uuid FK → document_snapshots.id (nullable — pins the distilled version)
  • created_at
  • PK (composite_document_id, document_id)

Index (composite_document_id), (document_id).

Enables reproducibility ("what produced this CK?") and re-distill triggers (when a parent source doc gets a new snapshot, flag dependent composites stale).

Subsumption rule: once a source doc is absorbed into a composite, it gets no rag routing of its own — only the composite is ingested. The parent stays stored (provenance + reprocess), not ingested alone. A source doc not (yet) combined can be ingested directly via its own rag routing.

document_routings — destinations / fan-out

One doc (source or composite) can route to several sinks (e.g. a feature-map composite both injected into RAG and distilled into a skill). The routed entity is addressed by two nullable FK columns + CHECK (same reasoning as document_snapshots — real FKs, not a type tag).

  • document_id uuid FK → documents.id nullable
  • composite_document_id uuid FK → composite_documents.id nullable
  • CHECK (num_nonnulls(document_id, composite_document_id) = 1)
  • destination enum (rag | config | skill)
  • target_ref text (skill slug, or config slug + key path; null for rag)
  • priority int (RAG high-priority ordering; null for non-rag)
  • enabled bool default true
  • created_at, updated_at
  • PK (document_id, composite_document_id, destination, target_ref)

Each destination maps to a different terminal action by the Knowledge Agent: - rag → invoke the rose document loader to ingest the md into LightRAG (priority controls high-priority injection). - skill → write a DB instruction row (deterministic block: pricing, competitors). - config → write a client-editable config value (e.g. engagement.tov).

target_ref is free text in this design (skill slug / config slug.key); a later iteration may FK it to a skill/config registry once those have stable identifiers. The skill path is net-new: today skills are repo markdown resolved by ixskills, not DB rows (see IX-3171).

document_conflicts — global cross-entity view

  • id uuid PK, site_domain text FK → public.domains.domain (RLS scope)
  • entity_a_type enum (document | composite_document | knowledge_faq | skill | config_value)
  • entity_a_id uuid nullable, entity_a_ref text nullable
  • entity_b_type enum, entity_b_id uuid nullable, entity_b_ref text nullable
  • conflict_type enum (factual_contradiction | stale_info | duplicate | policy_mismatch)
  • severity enum (info | warn | error)
  • evidence_a text, evidence_b text (quoted spans)
  • detected_by enum (embedding_diff | llm_judge | manual), detector_version text
  • status enum (open | acknowledged | resolved | false_positive)
  • resolved_by_user_id uuid nullable, resolved_at timestamptz nullable
  • created_at, updated_at (status transitions open → acknowledged → resolved bump updated_at)

Index (site_domain, status, severity).

entity_*_type = knowledge_faq refers only to config.knowledge_faqs (retrieval knowledge). It must never reference geo.faqs — that is generated GEO publishing content and is a totally separate concept (see project FAQ-boundary rule). Naming the enum knowledge_faq (not bare faq) makes the boundary explicit.

Why id/ref polymorphism here (and only here). Unlike document_snapshots / document_routings — whose operands are always one of two real tables, so they use two nullable FK columns — a conflict side can be an entity that has no DB table at all. That forces a generic (type, id-or-ref) address: - document → entity_*_id = documents.id. - composite_document → entity_*_id = composite_documents.id. - knowledge_faq → entity_*_id = config.knowledge_faqs.id. - skill → entity_*_ref = the skill slug (e.g. response_handling/competitors). Skills are repo markdown resolved by ixskills, not DB rows — there is no uuid. - config_value → entity_*_ref = config_slug.key_path (e.g. engagement.tov).

Set exactly one of id / ref per side (CHECK). When the ADR's destination=skill path later materializes instructions as DB rows, those gain a uuid and move to entity_*_id.

crawl_runs — job-level telemetry (populated by scraping pipeline)

One row per scraping job. Status enum: queued → mapping → mapped | failed.

Column Type Notes
id uuid PK
site_domain text FK → public.domains.domain (RLS scope — tenant)
source_host_id uuid FK → source_hosts.id (the host being crawled; may be external)
target_url text Root URL passed to Firecrawl Map
raw_map_uri text GCS path to Firecrawl Map JSON response (domains/{domain}/runs/{id}/map/links.json)
options jsonb Firecrawl Map request params (limit, exclude patterns, …)
mode enum website_mapping \| incremental \| reprocess_from_snapshot \| manual
triggered_by enum cron \| webhook \| backoffice \| api
status enum queued \| mapping \| mapped \| failed
started_at timestamptz
finished_at timestamptz
urls_discovered int Phase 1 output count
urls_included int Phase 2 decision=include count
urls_scraped int Phase 3 success count
urls_failed int Phase 3 error count
error text Top-level failure message

crawl_page_decisions — per-URL run ledger (thin; replaces scrape_pages)

A thin per-run ledger: run membership + the LLM include/exclude decision + the sample. It no longer duplicates document content. The durable columns the old scrape_pages carried (canonical_url, url_hash, content_hash, raw_uri, scrape timings) now live where they belong — documents and document_snapshots. There is no promotion back-fill: the discovered URL is already a documents node (created at map time), and the scrape writes a document_snapshots row directly.

Stateful (not append-only): one row per (run_id, document_id), updated in place as the URL progresses through the phases.

PK: (run_id, document_id).

Column Type Notes
run_id uuid FK → crawl_runs.id
document_id uuid FK not null → documents.id — the discovery node, created at map time (status=discovered)
Phase 2 — LLM decision
decision enum unlabeled \| include \| exclude \| needs_review — default unlabeled
decision_source enum system \| operator \| rule
decision_reason text Human-readable reason (e.g. "blog_post, low relevance")
decision_confidence float 0–1 score from LLM
decision_metadata jsonb {content_kind, evidence[]}
sample_uri text GCS path to sample markdown used for LLM decision
sampled_at timestamptz
sample_error text
Phase 3 — scrape link
produced_snapshot_id uuid FK nullable → document_snapshots.id — the snapshot this run created, if the page was scraped and changed; null otherwise

The page identity (page_id = SHA1(canonical_url)) lives on the documents node as url_hash; the scraper matches a discovered URL to its node by (site_domain, url_hash). The LLM decision materializes onto the node too: decision=exclude → documents.scrape_policy=skip.

Phase 3 write (no back-fill): when decision=include and the freshly-processed markdown hash differs from the current document_snapshots.content_hash (or no snapshot exists), the Scraper writes a new document_snapshots row directly (document_id = the node, raw_markdown_ref + processed_markdown_ref = durable GCS snapshot bodies, produced_by_run_id = this run), advances documents.current_snapshot_id, and sets crawl_page_decisions.produced_snapshot_id. Unchanged pages just touch last_seen_at.

Two hashes, both SHA1. document_snapshots.content_hash is the SHA1 of the processed markdown body used downstream, so it gates snapshot identity and dedup. The raw markdown SHA1 is kept as provenance in extraction_metadata.raw_content_hash. Both SHA1 for consistency with hash_utils.py.

GCS artifact and snapshot body paths

Run-scoped ixscraping artifacts land in gs://rose-scraping/ under a deterministic execution hierarchy:

domains/{site_domain}/runs/{run_id}/
├── map/links.json              → crawl_runs.raw_map_uri
├── samples/{page_id}.md        → crawl_page_decisions.sample_uri              (Phase 2)
├── raw/{page_id}.md            → scrape execution artifact / replay input
└── reports/*.jsonl             → run diagnostics

Durable document snapshot bodies are written outside the run folder and grouped by document id then snapshot content hash:

domains/{site_domain}/document-snapshots/{document_id}/{content_hash}/content.md
  → document_snapshots.processed_markdown_ref
domains/{site_domain}/document-snapshots/{document_id}/{content_hash}/raw.md
  → document_snapshots.raw_markdown_ref (scrape-only, optional)

content.md is the canonical markdown body represented by the document_snapshots row. For scrapes, it is the cleaned/processed markdown; for uploads or generated composite documents, it is the extracted or generated markdown. We use the neutral name content.md instead of processed.md or body.md so every producer writes the same shape, even when there is no raw/processed pipeline.

raw.md is a scrape provenance companion for the same snapshot, not a separate snapshot. The snapshot identity remains content_hash = SHA1(content.md), and the raw body hash is recorded in extraction_metadata.raw_content_hash. If a later scrape changes only raw boilerplate but produces the same content.md, no new document_snapshots row is created; the run-scoped raw artifact remains available for run replay/debugging. If raw-only audit history becomes a first-class requirement, add an explicit raw revision model rather than inferring it from GCS object names.

The path is keyed by {site_domain} (the tenant) for storage layout; the scrape source host lives in source_hosts, not in the object key. {page_id} = SHA1(canonical_url) = the node's url_hash. {document_id} is the durable knowledge.documents.id (or, once implemented for composites, knowledge.composite_documents.id). produced_by_run_id records which run produced a snapshot; the snapshot body URI itself is not run-scoped.

Supabase remains the source of truth for the current version. Readers should follow documents.current_snapshot_id → document_snapshots.processed_markdown_ref instead of listing GCS and guessing which object is latest. The document-scoped prefix is for operator ergonomics and consistency with other knowledge storage use cases: listing domains/{site_domain}/document-snapshots/{document_id}/ shows every durable body version for that logical document.

RLS / lifecycle

  • Per-domain RLS on every table via site_domain + get_accessible_domains() (the existing Supabase pattern — not a client_id policy).
  • service_role retains full access for backend loaders and jobs.
  • Snapshot retention prune job respects snapshot_retention_versions (default 10) but never deletes current_snapshot_id.
  • Soft-delete flag (is_deleted) on documents and composite_documents (no row delete) for audit trail.
  • source_hosts carries no body and no RLS exception — same site_domain policy. Its host column is intentionally not FK'd to public.domains (external hosts are allowed).

Conflict detection model

  • Cron job: embedding similarity (cheap first pass) + LLM judge (expensive, gated).
  • Scans combinations per domain across both document tables: (doc × doc, where doc ∈ documents ∪ composite_documents), (doc × knowledge_faq), (doc × skill), (skill × knowledge_faq), (skill × skill), (× config_value).
  • Skills are read by name from the repo, not the DB. The detector is a backend job with the shared_data prompts; for a domain it resolves the effective skill set via ixskills (global skills + clients/{domain}/ overrides, filtered by agent_config.skill_toggles), loads each skill's markdown body, and compares it like any other knowledge unit. The conflict row stores the skill slug in entity_*_ref and the prompt/commit version in detector_version for reproducibility (the body itself isn't a DB row).
  • Likewise config_value operands are read from config.client_configs and addressed by config_slug.key_path in entity_*_ref.
  • Writes document_conflicts rows; idempotent on (entity_a, entity_b, detector_version) where each entity is its uuid or its string ref.
  • Versioned detector_version so old conflicts can be re-validated when the detector improves.
  • Severity surfaced in the backoffice conflict inbox.

Actors & responsibilities

Three components act on this data (plus the Conflict detector). The split is decide vs execute, with document_routings as the persisted seam between them.

Scraper (ixscraping) — mechanical I/O, no judgment. Runs as a three-phase pipeline.

  • Phase 1 — URL Mapping: resolves/creates the source_hosts row for the host being crawled, calls Firecrawl Map, creates one crawl_runs row, and for each discovered URL upserts a documents node (origin=scrape, status=discovered, url_hash=SHA1(canonical_url), source_host_id set) plus one crawl_page_decisions row (decision=unlabeled); stores the raw map response in GCS (raw_map_uri). crawl_runs.status → mapping.
  • Phase 2 — LLM Page Decider: samples a subset of page content (stored in GCS as sample_uri), calls an LLM to classify each URL as include | exclude with a confidence score and content_kind label; updates crawl_page_decisions in place and materializes the decision onto the node (exclude → documents.scrape_policy=skip).
  • Phase 3 — Firecrawl Batch Scrape: fetches only decision=include pages in batches of 100 (8 concurrent); stores raw run artifacts in GCS. crawl_runs.status → mapped | failed.
  • Snapshot write (end of Phase 3, no back-fill): for each decision=include page whose freshly-processed markdown SHA1 differs from the current document_snapshots.content_hash (or no snapshot exists): writes content.md plus optional raw.md to the durable document-scoped snapshot namespace, writes a new document_snapshots row directly (document_id = the existing node, raw_markdown_ref, processed_markdown_ref, produced_by_run_id), advances the node's current_snapshot_id, sets crawl_page_decisions.produced_snapshot_id. Unchanged pages touch last_seen_at only.
  • Detects change by SHA1 and skips unchanged pages; decides nothing about meaning, knowledge type, or routing destination.

Knowledge Agent — judgment, writes intent only.

  • Reads source documents and assigns knowledge_type (generic/faq); distills several source docs into a composite_document when useful, recording lineage in composite_document_sources.
  • Emits a plan — writes document_routings rows (targeting a source or composite doc) with rag / skill / config + target_ref + priority.
  • Is pure and re-runnable: re-running rewrites rows at worst, never double-applies effects.

Ingestor — execution, no judgment.

  • Reads document_routings and ingests each doc into its destination store: rag via the rose document loader (which itself skips unchanged docs); skill / config by writing the instruction / config rows directly.
  • Maps site_domain → backend tenantId (deterministic sanitization) when writing to Neo4j / MongoDB / LightRAG — the only place this translation happens.
  • Owns all side effects in one place, keeping the Scraper mechanical and the KA pure.
  • Idempotent and replayable: re-applying a plan converges to the same state without re-deciding.

Naming. Not "Router" — that name is taken by the runtime query/intent router (chat-time intent classification → pricing-skill vs RAG). The Ingestor ingests knowledge into whichever store the destination names; the rose document loader is its RAG-specific backend, not the whole thing.

v1 simplification. The Ingestor can run inline in the Knowledge Agent for v1 (decide then ingest in one job). Because document_routings is a persisted plan — not in-memory — a standalone Ingestor can be split out later with zero schema change, the day an independent re-route or a human-approval gate (review plan before it is applied) is needed.

Diagrams

Diagrams adapted from the meeting note "RAG vs skills" (2026-06-08). Participants and stores are renamed to the real code components (document_loader Job, DocumentProcessor, GoogleDriveLoader, LightRAG) and to this ADR's tables. The Knowledge Agent (KA) and Composite Knowledge (CK) store are proposed (companion ticket IX-3171), not built — flagged as such.

Storage architecture

Layers and who writes what:

  • Object storage (GCS) — single body store: raw md + processed md for every doc (incl. composite). HTML is not retained.
  • Supabase — metadata, version chain, provenance, routing, conflicts (refs into object storage).
  • Knowledge Agent (decision layer, skill-like; proposed, IX-3171) — reads docs, decides type + composites, and writes a plan (document_routings). Writes intent only.
  • Ingestor — reads document_routings and applies each: rag via the rose document loader, skill/config by writing the rows. May run inline in the KA for v1.
  • rose document loader (document_loader Cloud Run Job) — the Ingestor's RAG backend; today it writes only LightRAG (Mongo+Neo4j).
  • Instruction / config rows — the deterministic path (skill / config destinations).
flowchart TB subgraph SRC["Sources"] WS["Website URL"] UPL["Internal doc upload"] FB["Feedback"] GD["GDrive<br/>(transitional — migrating out)"] end WS --> SCR["Scraper"] SCR -->|"raw md + processed md"| OBJ[("Object storage GCS<br/>single body store")] UPL -->|"md"| OBJ GD -.->|"one-time import"| OBJ SCR -->|"host + nodes + snapshots"| SB[("Supabase<br/>source_hosts / documents /<br/>composite_documents / snapshots /<br/>composite_document_sources / routings / conflicts")] FB -->|"row (origin=feedback)"| SB OBJ -. "raw_markdown_ref / processed_markdown_ref" .- SB OBJ --> KA["Knowledge Agent<br/>(decide only; proposed)"] SB --> KA KA -->|"composite_document + generated md"| OBJ KA -->|"writes plan: composite_document_sources + document_routings"| PLAN["document_routings<br/>(persisted plan)"] PLAN --> ING["Ingestor<br/>(apply plan; inline in KA for v1)"] ING -->|"rag → rose document loader"| DL["rose document loader<br/>(Cloud Run Job)"] DL --> LR[("LightRAG<br/>Mongo + Neo4j")] ING -->|"skill / config → rows"| INSTR[("Instruction / config rows<br/>in DB — proposed")] classDef store fill:#eef,stroke:#557; class OBJ,SB,LR,INSTR store;

Current vs proposed. Today the document loader writes only LightRAG (no object storage, no Supabase rows), and skills/instructions are repo markdown files resolved at runtime by ixskills (backend/apps/shared_data/prompts/website-agent/skills/), with only agent_config.skill_toggles (JSONB) in the DB. This ADR introduces (1) object storage + the Supabase tables, populated by the scraper/snapshot step before the loader runs, and (2) a DB-stored instruction/config path so a Knowledge Agent can emit a deterministic block without going through RAG. Moving instructions from repo files to DB rows is net-new and belongs to IX-3171 — flagged proposed.

Knowledge layers (raw → generated → routed)

A URL is a document (origin=scrape) with raw md + processed md in its snapshot. The Knowledge Agent distills one or more such docs into a composite_document (separate table), recording provenance in composite_document_sources. Any doc (source or composite) routes to one or more sinks via document_routings.

flowchart TB subgraph RAWL["Raw docs — documents (origin=scrape/upload/feedback)"] R1["URL doc<br/>knowledge_type=generic<br/>snapshot: raw md + processed md"] R2["uploaded md<br/>origin=upload"] RF["feedback<br/>origin=feedback, knowledge_type=faq"] end R1 --> KA["Knowledge Agent (proposed)"] R2 --> KA RF --> KA KA -->|"distills (composite_document_sources)"| CK["composite_document<br/>(separate table)<br/>snapshot: generated md"] CK --> RT["document_routings (plan)"] R1 --> RT RT --> ING["Ingestor"] ING -->|"destination=rag (priority)"| RAGSINK[("LightRAG high-priority")] ING -->|"destination=skill (target_ref=slug)"| SKILL["Instruction / skill block"] ING -->|"destination=config (target_ref=slug.key)"| CFG["Client-editable config"]

Worked routings:

Domain Doc document_routings
Feature map (700 features) composite destination=rag, priority=high
Pricing composite destination=skill, target_ref=pricing
Competitors / battle-cards composite destination=skill, target_ref=competitors
Tone of voice / positioning composite destination=config, target_ref=engagement.tov
Feature-map that also seeds a skill composite two rows: rag + skill (fan-out)

Ingestion pipeline

sequenceDiagram autonumber participant Site as Website participant SCR as Scraper (ixscraping) participant Blob as GCS (gs://rose-scraping) participant SB as Supabase (knowledge.*) participant KA as Knowledge Agent (proposed) participant ING as Ingestor participant RAG as LightRAG (Mongo+Neo4j) participant BO as Backoffice participant Client Note over SCR,SB: Phase 1 — URL Mapping SCR->>Site: Firecrawl Map (sitemap crawl) SCR->>Blob: store map/links.json SCR->>SB: UPSERT source_hosts (host), INSERT crawl_runs (status=mapping) SCR->>SB: UPSERT documents node per URL (status=discovered), INSERT crawl_page_decisions (decision=unlabeled) Note over SCR,SB: Phase 2 — LLM Page Decision SCR->>Site: fetch sample content per URL section SCR->>Blob: store samples/{page_id}.md SCR->>SCR: LLM classify include/exclude + content_kind SCR->>SB: UPDATE crawl_page_decisions (decision, confidence, sample_uri); set node scrape_policy Note over SCR,SB: Phase 3 — Firecrawl Batch Scrape (include pages only) SCR->>Site: Firecrawl Batch Scrape (100/batch, 8 concurrent) SCR->>Blob: store runs/{run_id}/raw/{page_id}.md SCR->>Blob: store document-snapshots/{document_id}/{content_hash}/content.md (+ raw.md) SCR->>SCR: compare processed-md SHA1 vs current document_snapshots.content_hash alt unchanged SCR->>SB: touch documents.last_seen_at, skip else new / changed SCR->>SB: INSERT document_snapshots (document_id, raw_markdown_ref, processed_markdown_ref, produced_by_run_id, version++) SCR->>SB: advance documents.current_snapshot_id, set crawl_page_decisions.produced_snapshot_id end SCR->>SB: crawl_runs.status=mapped Note over KA,ING: Knowledge Agent + Ingestor (unchanged flow) SB->>KA: new/changed snapshots KA->>SB: write document_routings (rag/skill/config) ING->>SB: read document_routings ING->>RAG: ingest via rose document loader SB->>BO: surface document list + snapshots BO->>Client: show for validation Client->>BO: comment / flag / edit BO->>SB: apply validation (status / tags)

Update & contradiction loop

Periodic re-scrape → version chain → cross-entity conflict detection → client validation. No fire-hose for slow-changing data (pricing): versioned minimal edits.

sequenceDiagram autonumber participant Cron as Trigger (re-scrape / event) participant Loader as Scraper / Loader participant DP as DocumentProcessor participant SB as Supabase (snapshots + conflicts) participant Det as Conflict detector (cron) participant Client Cron->>Loader: re-scrape Loader->>DP: new content DP->>SB: read current_snapshot_id + content_hash DP->>DP: compare SHA1 alt unchanged DP->>SB: touch last_seen_at, no new version else changed DP->>SB: new document_snapshots row (version++, change_signal) DP->>SB: set documents.status = stale if downstream conflict end Det->>SB: scan doc x {doc, knowledge_faq, skill, config_value} Det->>SB: write document_conflicts (idempotent on entity_a, entity_b, detector_version) SB->>Client: notify "site says X, KB has Y" Client-->>SB: resolve / acknowledge / false_positive

Feedback loop

Client feedback ("price is 24, not 22") is stored as an attributed, dated document (origin = feedback, tags = [feedback]) that can contradict the KB and trigger a conflict. Feedback capture does not exist today — it is a prerequisite for this loop.

sequenceDiagram autonumber participant Client participant PG as Playground / Backoffice participant SB as Supabase participant Det as Conflict detector participant RAG as LightRAG Client->>PG: feedback on an answer ("price is 24, not 22") PG->>SB: store documents (origin=feedback, tags=[feedback], dated) SB->>Det: signal new attributed doc Det->>SB: contradiction vs source/composite doc / knowledge_faq? alt contradiction Det->>SB: write document_conflicts (severity) SB->>Client: validation request else complement SB->>RAG: index as knowledge end

Graph note: prefer a versioned entity update (new document_snapshots row + superseded_by) over the current destructive "delete doc + linked entities/relations → regenerate all" strategy, so feedback is not drowned by regeneration.

Document lifecycle

Aligned to documents.status enum.

stateDiagram-v2 [*] --> discovered: registered (URL/file known, not fetched) discovered --> discovered: gate 1 scrape_policy=skip (never fetched) discovered --> active: scraped + ingested discovered --> blocked: gate 2 scraped, excluded from ingestion active --> stale: re-scrape change / downstream conflict stale --> active: new snapshot validated (version++) active --> blocked: excluded from ingestion (manual / rule) blocked --> active: re-included active --> failed: fetch / extraction error failed --> active: retry succeeds active --> superseded: replaced by another doc (canonical merge) superseded --> [*]

Companion context (IX-3171 — knowledge taxonomy, not decided here)

These describe the proposed Knowledge Agent / Composite Knowledge / runtime-routing model and are kept for design continuity. They are out of scope for this storage/schema ADR and reference components that do not exist in code yet.

Knowledge taxonomy

flowchart LR subgraph SRC["Raw sources"] WS["Website"] --> RAW["raw md<br/>(immutable, blob store)"] --> PROC["processed md"] DOCS["Internal docs"] FB["Client feedbacks"] end RAW --> KA["Knowledge Agent<br/>(proposed)"] DOCS --> KA FB --> KA KA -->|"generic"| RAG[("Generic RAG<br/>+ confidence")] KA -->|"distills"| CK["Composite Knowledge<br/>(proposed: versioned, validatable)"] CK -->|"generates"| SKILL["Instructions / Skills"] CK -->|"injects high priority"| RAG CFG["Client-editable config<br/>(TOV, price/competitor switches,<br/>positioning, who-we-are)"] classDef store fill:#eef,stroke:#557; class RAG,CK store;

Confidence scoring (proposed)

flowchart TD IN["Knowledge unit"] --> T{"Type?"} T -->|"Recent client feedback"| HI["HIGH confidence"] T -->|"Client knowledge_faq / FAQ-from-feedback"| HI T -->|"Standard page / doc"| MID["MEDIUM confidence"] T -->|"Blog"| LO["LOW confidence"] HI --> AGE{"Freshness?"} MID --> AGE LO --> AGE AGE -->|"recent"| UP["score up"] AGE -->|"old"| DOWN["score down (e.g. old blog = very low)"] UP --> RANK["Retrieval ranking<br/>+ contradiction resolution"] DOWN --> RANK

Composite Knowledge lifecycle (proposed)

stateDiagram-v2 [*] --> Generated: KA distills sources Generated --> InValidation: surface back-office InValidation --> Validated: client OK InValidation --> Edited: client edits / comments Edited --> Validated Validated --> Staging: change tested Staging --> Prod: promotion Prod --> Stale: re-scrape detects contradiction or contradictory feedback Stale --> CandidateVersion: KA proposes minimal edit CandidateVersion --> InValidation Prod --> [*]

Runtime retrieval (proposed)

sequenceDiagram autonumber participant V as Visitor participant R as Router / Agent participant PSK as Pricing skill participant CSK as Competitor skill (index) participant RAG as Generic RAG participant LLM V->>R: question R->>R: classify intent alt price R->>PSK: read pricing instruction (+ allowed-to-talk-price switch) PSK-->>R: deterministic block else competitor R->>CSK: keyword -> exact phrasing (+ comparison switch) CSK-->>R: deterministic block (forced style) else general R->>RAG: retrieve confidence-ranked chunks RAG-->>R: top chunks end R->>LLM: resolved prompt LLM-->>V: on-brand answer

Backoffice surfaces (separate impl tickets)

  • Website + file hierarchy tree (per domain) — renders documents sorted by path (split into a folder tree client-side) spanning both the site URL structure and uploaded files, with each node annotated by scrape_policy (scrape/skip), status (discovered/active/ blocked/…), and knowledge_type. The primary surface for gate 1 + gate 2 decisions. A big site lazy-loads subtrees by path prefix instead of reading the whole tree at once.
  • Document list (flat view; filter by status, origin, knowledge_type, kb_indexed, tag).
  • Composite list (composite_documents by lifecycle status; validate / edit / promote).
  • Snapshot diff viewer (snapshot N vs N-1 markdown diff).
  • Conflict inbox (group by severity, resolve / mark false-positive).
  • Manual rescrape buttons (full / single URL / replay-from-snapshot).
  • Per-node toggles from the tree (or a glob editor that sets them in bulk): gate 1 scrape_policy=skip (never-fetch) and gate 2 status=blocked (scrape-but-don't-ingest).

Consequences

Positive

  • Single queryable source of truth for "what is indexed, when, and what did it look like".
  • New tables join cleanly to existing domains / knowledge_faqs / client_configs because they share the site_domain partition key — no new tenant convention introduced.
  • Immutable snapshots give audit + reproducibility the current SHA1-on-doc_status model lacks.
  • Global conflict view spans source docs, composite docs, knowledge FAQs, skills, and config.
  • External scrape sources (competitor / blog / partner hosts) are first-class via source_hosts without polluting public.domains; the tenant key stays site_domain.
  • The thin crawl_page_decisions ledger removes the scrape_pages content duplication + promotion back-fill — a discovered URL is one document from the start.

Negative

  • Adds a second persistence concern alongside the existing MongoDB doc_status / LightRAG state; the relationship between Supabase document_snapshots.content_hash and the MongoDB hash must be kept consistent (same SHA1).
  • Blob store cost + API-limit projection is unproven (open question below).
  • Conflict detection (embeddings + LLM judge) is a new recurring cost.

Neutral

  • Supabase is a single shared DB across environments; these tables follow that constraint (writes are effectively production writes regardless of IX_ENVIRONMENT).

Alternatives Considered

  • Blob store only (no Supabase tables) — rejected: no relational queries, backoffice needs a DB join anyway, versioning still needs a metadata row.
  • Keep Google Drive — rejected: no versioning audit, no blocklist, permissions drift, hard to surface in backoffice.
  • client_id uuid as RLS scope — rejected: conflicts with the established site_domain partitioning and RLS helper, and a client can own multiple domains.
  • Cloudflare R2 instead of GCS for blobs — viable; deferred. R2 already backs GEO pages, but blobs here are a backend/GCP concern and GCS avoids a second cloud + IAM surface.
  • Composites as documents rows (knowledge_type=composite) — rejected: composites have a distinct validatable lifecycle (generated → … → prod) and none of the scrape/crawl/tree concerns, so cramming them in forces many inapplicable nullable columns and muddies the tree query. A dedicated composite_documents table is cleaner; both still share the single document_snapshots body store.
  • Type-tag polymorphism (owner_kind + untyped owner_id) on snapshots/routings — rejected: Postgres cannot FK an untyped id to two tables, so it would silently lose referential integrity. Two nullable FK columns + an exactly-one CHECK keep real FKs and cascade. (document_conflicts keeps id/ref polymorphism only because its operands include non-table entities — skill markdown, config keys.)
  • Scrape source host as a plain source_host text column (no table) — rejected in favor of a source_hosts registry: a table dedups hosts shared across docs and gives per-host scrape settings (cadence, robots, allow/block globs) a home without touching documents.
  • Scrape source host FK'd to public.domains — rejected: scrape sources can be external (competitor / blog / partner) and are not Rose-hosted tenant domains. The tenant key stays site_domain; the source host is decoupled.
  • Keeping scrape_pages as a stateful content table (the prior amendment) — rejected: it duplicated documents / document_snapshots columns and needed a promotion back-fill. Reduced to the thin crawl_page_decisions ledger; the discovered URL is a document from the start and the scrape writes a snapshot directly.
  • parent_id adjacency list + synthesized kind=folder rows for the hierarchy (the prior amendment) — rejected: path already encodes the tree for URLs and files, so parent_id was a denormalized cache that drifts, and synthesized folders had to be created/recomputed/kept id-stable across runs. Deriving the tree from path (sort + split; prefix scan for subtrees) is strictly less schema and removes the folder-identity churn. The cost — a folder has no row, so nothing can FK or attach metadata to it — is moot today (routing and conflicts target leaf docs, never folders); add parent_id back only if a folder must become an addressable entity.
  • ltree / materialized-path / closure-table for the hierarchy — rejected as premature: a domain's tree is a few thousand nodes, well within sort-and-split + a text_pattern_ops prefix index. Revisit only if a single domain's tree is measurably too big to lazy-load.

Open questions

  • Blob store API limits + per-client cost projection: GCS vs R2 vs Supabase Storage.
  • Snapshot retention default — 10 versions per doc reasonable? Per-tier?
  • Conflict detector — embedding-only first pass vs LLM judge from day 1?
  • ~~What triggers a re-scrape?~~ ✅ Resolved: a new crawl_runs row created via Cloud Run Job (env var SCRAPE_RUN_ID); triggered by cron/webhook/backoffice.
  • ~~How is change detected at the scraping layer?~~ ✅ Resolved: the SHA1 of the processed markdown body compared against the current document_snapshots.content_hash; on change the Scraper writes a new snapshot directly (no scrape_pages staging row). The raw markdown SHA1 is stored separately as provenance.
  • Migration out of Google Drive (separate ticket):
    • Move each doc body GDrive → object storage; create one synthetic document_snapshots row with content_hash = the current hash.
    • Preserve the existing document id on the documents row so the loader's content-hash dedup still matches and docs are not reprocessed.
    • Classify origin from the md frontmatter: website docs reconstruct source_url / canonical_url from frontmatter → origin=scrape; internal-doc conversions → origin=upload (keep the original binary in original_ref if available); docs with no reconstructable source → origin=unknown.
    • Tag all migrated rows tags=[migrated-from-gdrive]; drop the transitional gdrive origin value once complete.

These were listed in the prior draft as future blob-store work but already exist:

  • Widget bundle releases (frontend/widget/) — already versioned on GCS (gs://inboundx-cdn/widget/.../versions/{YYYYMMDD}/), served via the Cloudflare CDN proxy.
  • Generated GEO static pages (geo.faqs HTML export) — already published to Cloudflare R2 and served by cloudflare-geo-pages.js (faq.userose.ai).

Genuinely new candidates for the same blob store (separate scope): LightRAG index snapshots / KB exports, compiled prompt bundles tied to a release tag.

Out of scope (for this design ticket)

  • Knowledge Agent design (IX-3171).
  • Updating ixscraping to write to knowledge.* instead of scraping.*, including: writing a documents discovery node per mapped URL (not a self-contained pages row), writing document_snapshots directly on scrape, and registering the scrape source in source_hosts (decoupled from public.domains) (implementation ticket).
  • Supabase migration: scraping.runs → knowledge.crawl_runs, scraping.pages → the thin knowledge.crawl_page_decisions ledger + knowledge.documents/document_snapshots (the durable columns move out of the pages table); seed knowledge.source_hosts from existing scraping.runs.target_url hosts (data migration ticket).
  • Backoffice UI mockups.
  • Migration of existing GDrive content (separate ticket).