CAF Catalog — Data Model / ERD (v1)

Backed into from three UI screenshots (founder, 2026-06-15) + his attribute spec. Pairs with the architecture decisions in [[2026-06-15-caf-architecture-decisions-and-meta-council]] (Snowflake VARIANT + thin typed spine, open-internal/hard-walled-external tenancy).

Founder also resolved the open seam from earlier: he has a way to distribute skills to Claude Code, Desktop, and Cowork — that fork is closed.

v1 vs v1.1. Everything below from "What each screen told us" down is the original v1 reasoning (kept for the record). The current design is the v1.1 block immediately below — it supersedes v1 on the schema_contract, outrigger, and constraint decisions after a 3-lens adversarial review.

v1.1 — after adversarial review (current state)

Ran a fresh-eyes 3-lens adversarial panel (Snowflake-idiom · ruthless-YAGNI · Snowflake→Postgres portability) — this is Adversarial Verification (pattern #3) on my own draft, since I wrote v1 and carry confirmation bias.

• Q1 — VARIANT + jsonschema (3/3): keep VARIANT for the modal prose; cut the schema_contract table. It's a versioned render-contract built before a single payload-shape conflict has happened — premature flexibility. Front end renders from known keys; validate app-side if at all. Optional breadcrumb: a bare payload_version int on use_case. Re-introduce a versioned contract only when two payload shapes actually coexist in prod.
• Q2 — industry (3/3): keep industry as a typed dimension, NOT a tag. It carries the prefix (BNK/INS) and is referenced by target_company — a dimension, not a free facet. Keep a thin industry lookup (one home for the prefix; most-stable, twice-referenced) — the one outrigger worth keeping. Tags are for the open-ended chips only.
• Q3 — outriggers (2/3): denormalize capability_category and tag_category to string columns on capability / tag. Trivial controlled vocab, cheap on Snowflake columnar, fewer moving parts. display_order → a UI constant, not a table.
• Snowflake→Postgres portability (the panel's best find): Snowflake does NOT enforce FK/UNIQUE/PK; Postgres does → dirty data accumulates silently and Postgres rejects the load on migration. So now: (1) author enforced-style constraints in the DDL even though Snowflake ignores them (intent + live on Postgres) + a periodic app-side integrity sweep; (2) ANSI GENERATED ALWAYS AS IDENTITY keys (not Snowflake AUTOINCREMENT/SEQUENCE); (3) lowercase_unquoted identifiers everywhere; (4) route all VARIANT reads + upserts through a thin DAL so JSON-path (VARIANT→JSONB) and MERGE vs ON CONFLICT collapse to one file at migration.

Net v1.1 model: use_case (spine + VARIANT payload + payload_version int) · capability (+ category string col) · use_case_capability (M:N) · tag (+ category string col) · use_case_tag (M:N) · industry (thin lookup) · target_company. Dropped from v1: schema_contract, capability_category, tag_category.

erDiagram
    industry ||--o{ use_case : classifies
    target_company ||--o{ use_case : "tailored for"
    use_case ||--o{ use_case : "tailored from (generic_parent_id)"
    use_case ||--o{ use_case_capability : has
    capability ||--o{ use_case_capability : "lit by"
    use_case ||--o{ use_case_tag : has
    tag ||--o{ use_case_tag : applied
    industry ||--o{ target_company : in

(v1.1 — capability.category and tag.category are now string columns, not their own tables; use_case.payload is VARIANT + a payload_version int, no schema_contract entity.)

Open fork for founder: keep tag + use_case_tag junction (structured, category-grouped filter pane) vs collapse tags to a tags ARRAY column on use_case (fewer tables, but loses easy category grouping + referential integrity). Junction = my lean, since the filter pane groups by category.

Founder decisions on the four design forks (recorded 2026-06-15)

Founder's calls (he's removing the open-questions section from the phData-internal copy, so this is the canonical record):

1. Core dims → HYBRID: typed columns for the structured dims, tags only for the open-ended chips. (Founder first answered "everything as tags," then walked it back 2026-06-16 — the v1.1 review had already resolved this correctly and he hadn't realized it. This is the final answer.) The structured, stable, single-valued dimensions — industry, sub_domain, archetype, approach, priority, risk, wave — are typed columns (queryable/sortable; industry keeps its prefix and the target_company FK). The tag/tag_category system is only for the open-ended, multi-valued chips (MULTI-SOURCE WORKFLOW, CROSS-REFERENCE, GENERATIVE…) that you add without a schema change. Numeric/sortable fields (savings, timeline, strategic_rank, peer_validated) are typed too. This is exactly what v1.1 landed.
2. Tailored rows → COPY ALL FIELDS — snapshot the generic at tailor-time; generic_parent_id still links for the usage count. (Matches rec.)
3. Versioning → SKIP the history table — current-state only; optional payload_version int. (Matches rec.)
4. Citations / tech_stack → KEEP IN VARIANT payload — no separate tables. (Matches rec.)

Net: this is the v1.1 model — a typed spine (structured dims + numerics + FKs, incl. industry-as-dimension with its prefix + target_company link) + VARIANT payload + tags for the open-ended chips only. Tag-junction-vs-ARRAY still open (low stakes; junction keeps category grouping).

What each screen told us

1. Catalog page (assets/2026-06-15-caf-catalog-page.jpg) — grid of use-case cards. Top-right context label ("Catalyst Holdings") is a scope: a vertical, a department (horizontal), or a target company. Left filter pane = INDUSTRY category with 20+ tags → tagging mechanism, tags have categories. Cards show: identifier (BNK-001…), star/strategic rating, name, plain-language desc, savings as a RANGE ($8.0M–$22.0M/yr), risk/priority badge, timeline in weeks (26w), a wave (wave 1/2), a sub-domain label (AML/TREASURY/CREDIT/UW/CLM), and chip tags (MULTI-SOURCE WORKFLOW / CROSS-REFERENCE / GENERATIVE). Toggles: "Top-5 strategic only", "Peer-validated only".

2. Use-case detail modal (assets/2026-06-15-caf-usecase-detail-modal.jpg) — identifier shown as BNK-001 · Q-BNK-001 (generic + its tailored/quoted instance). Sections: In Plain Language, How It Works, Mechanism, Tech Stack, Timeline (26 weeks · Q1 · Fixed bid), Annual Savings (range), Why Now, Citation(s) — and the tailored citation injects client context ("Catalyst Holdings 10-K filed Dec 2025"). Badges: WAVE 1 · Q1 · HYBRID · HIGH RISK · MULTI-SOURCE WORKFLOW · HIGH.

3. Capabilities mapping (assets/2026-06-15-caf-capabilities-mapping.jpg) — capability blocks grouped by 5 layers (Governance / Data / Knowledge / Intelligence / Interface). Each block: identifier (gov-data, data-ing, kn-sem, int-ml, if-conv…), name, mini-description, and an X/441 count (use cases touching it). Selected use cases "light up" blocks → many-to-many. Bottom roll-up: annual value range (Σ savings), max delivery window, platform footprint (# distinct capabilities lit).

Entities

use_case (core)

Typed spine (filter / sort / aggregate — these drive the catalog UI, so they're columns not VARIANT):

• id PK · identifier (BNK-001 / Q-BNK-001, unique business key)
• name
• industry_id FK → industry
• sub_domain (AML, TREASURY, CREDIT, UW, CLM…)
• archetype (multi-source-workflow, cross-reference, …)
• approach (deterministic / generative / hybrid)
• priority (high/med/low) · risk (high/med/low)
• strategic_rank (the star rating; powers "Top-5 strategic only")
• peer_validated bool
• annual_savings_min · annual_savings_max (range → roll-up)
• timeline_weeks int · wave int · quarter · pricing_model (e.g. fixed-bid)
• generic_parent_id FK → use_case.id (self-referential; NULL ⇒ this is a generic/white-label; set ⇒ tailored instance)
• target_company_id FK → target_company (NULL for generic)
• version int · schema_contract_id FK → schema_contract
• status · created_at · updated_at

VARIANT payload (payload column, governed by the row's schema_contract) — the long-form, evolving fields the modal renders:

• description (plain language) · delivery_overview (how it works) · mechanism
• tech_stack[] · justification (why now) · citations[]
• room for new fields without a schema migration (the whole point of VARIANT + jsonschema)

capability

• id PK · capability_identifier (gov-data, data-ing, kn-sem… unique)
• name · mini_description
• capability_category_id FK → capability_category
• payload VARIANT (optional; for the future drill-in modal)

capability_category (lookup — the 5 layers)

• id · name (Governance Layer / Data / Knowledge / Intelligence / Interface) · display_order

use_case_capability (M:N — "use cases light up capabilities")

• use_case_id FK · capability_id FK · PK(both) · optional relationship_type (primary/secondary)

tag_category (filter-pane categories) + tag + use_case_tag (M:N)

• tag_category: id · name (Industry, plus evolving facets) · display_order
• tag: id · tag_category_id FK · name · slug
• use_case_tag: use_case_id FK · tag_id FK · PK(both)

industry (lookup — also the card prefix source: BNK/INS/TEL…)

• id · name · prefix (BNK, INS, TEL…) · optional parent for sub-verticals

target_company (client / catalog scope)

• id · name (Catalyst Holdings) · industry_id FK · ticker · … (tailored use_cases point here)

schema_contract (jsonschema = front-end display contract + payload versioning)

• id · version (v1, v2…) · jsonschema (the JSON Schema doc) · effective_from · notes
• Front end fetches this to know how to render use_case.payload. This is founder's stated design.

ER diagram (v1 — SUPERSEDED; current diagram is in the v1.1 section above)

This v1 diagram still shows schema_contract, capability_category, and tag_category — all three were cut in v1.1. Kept only to show what changed.

erDiagram
    industry ||--o{ use_case : classifies
    target_company ||--o{ use_case : "tailored for"
    use_case ||--o{ use_case : "tailored from (self-ref generic_parent_id)"
    schema_contract ||--o{ use_case : "governs payload shape"
    use_case ||--o{ use_case_capability : has
    capability ||--o{ use_case_capability : "lit by"
    capability_category ||--o{ capability : groups
    use_case ||--o{ use_case_tag : has
    tag ||--o{ use_case_tag : applied
    tag_category ||--o{ tag : groups
    industry ||--o{ target_company : in

Spine vs VARIANT — the rule

Column if the catalog filters, sorts, or sums on it (industry, sub_domain, archetype, approach, priority, risk, strategic_rank, peer_validated, savings min/max, timeline_weeks, wave, the two FKs, version). VARIANT if the modal just displays it and it may gain fields (description, delivery_overview, mechanism, tech_stack, justification, citations). The schema_contract.jsonschema versions the VARIANT shape and doubles as the render contract — exactly founder's idea.

Design forks (need founder's call — my recommendation first)

1. Core dims as typed columns + flexible chips as tags (hybrid). Rec: industry/sub_domain/archetype/approach/priority/risk/wave = typed columns (they sort/filter and are stable); the open-ended chips (MULTI-SOURCE WORKFLOW, CROSS-REFERENCE…) = tag/tag_category so new facets need no migration. Industry also gets a lookup table (it's the card prefix + target_company's industry). Confirm the split, or do you want everything-as-tags?

2. Tailored rows: snapshot-copy vs delta-overlay. Self-ref generic_parent_id is set (your call, done). Open: does a tailored row copy all fields from the generic at tailor-time (simple reads, but drifts if the generic changes) or store only overrides and inherit the rest at read-time (stays in sync, more complex reads)? Rec: snapshot-copy for the exploratory phase — simpler, and the link still gives you the usage count (COUNT(* WHERE generic_parent_id = :id)).

3. Versioning depth. version int + schema_contract (jsonschema) now. Do you also want a full immutable use_case_version history table (audit/rollback), or is current-state-plus-schema-version enough for now? Rec: skip the history table until you need rollback; the schema_contract handles the "shape may change" risk you flagged.

4. Citations / tech_stack: VARIANT arrays vs own tables. Rec: keep in the jsonschema'd payload for now (your VARIANT direction, simplest); normalize citation into a table only if you need to dedupe/link citations across use cases.

Cross-references

• [[2026-06-15-caf-architecture-decisions-and-meta-council]] — tenancy, datastore, harness, meta-council patterns
• [[2026-06-14-caf-restructure-organizing-brief]] · [[2026-06-09-caf-restructure-proposal]]
• Screenshots: assets/2026-06-15-caf-catalog-page.jpg · -usecase-detail-modal.jpg · -capabilities-mapping.jpg