Data Design

How RCARS stores, relates, and scores content. Read this to understand the data model conceptually, then point an AI agent at the full schema reference at the bottom for implementation details.

Content Model

Everything in RCARS is a content entity -- a source-agnostic record representing a piece of RHDP content. The system stores labs, demos, sandboxes, and (in the future) architectures, interactive experiences, or content from non-Babylon sources.

content_entities          babylon_items (1:1 extension)
┌──────────────────┐      ┌──────────────────────┐
│ content_id (PK)  │◄─────│ content_id (PK, FK)  │
│ source           │      │ ci_name (UNIQUE)      │
│ content_type     │      │ stage, category       │
│ display_name     │      │ showroom_url/ref      │
│ summary          │      │ cloud_provider        │
│ products_json    │      │ scan_status           │
│ retired_at       │      │ ...30 more fields     │
│ ...              │      └──────────────────────┘
└──────────────────┘

Two-table design

• content_entities holds fields that apply to any content source: identity (content_id, source, content_type), card-level metadata (display name, summary, products, topics, audience, difficulty), and lifecycle state (retired_at).
• babylon_items extends content_entities 1:1 for Babylon-sourced content. It holds everything CRD-specific: ci_name, stage, category, Showroom URL/ref, cloud provider, OCP version, AgnosticD config, scan status, and infrastructure metadata.

Why this split

The old catalog_items table baked Babylon assumptions into the primary key (ci_name) and mixed source-specific fields with universal ones. The normalized model lets RCARS ingest content from other sources (Confluence, external labs, architecture docs) without schema changes to the core entity table -- just add a new extension table alongside babylon_items.

content_id

Every entity has a stable content_id as its primary key -- a text identifier with a source prefix:

• Babylon items: babylon:<ci_name> (e.g., babylon:ocp4-getting-started.prod)

All foreign keys throughout the schema reference content_entities.content_id. The old ci_name is preserved on babylon_items as a UNIQUE column for backward compatibility and CRD correlation, but it is never used as a foreign key.

Content types and sources

• source: Where the content came from (babylon, and future values like confluence, external)
• content_type: What kind of content it is (lab, demo, sandbox, and future values like architecture, interactive_experience)

Soft-delete and retirement

Items that disappear from Babylon CRDs get content_entities.retired_at = NOW() instead of being deleted. All active-item queries filter on retired_at IS NULL. Items that reappear in a future scan are automatically un-retired (set retired_at = NULL).

This catalog marker is separate from workflow completion. A merged removal PR can set retirement_workflow.step_retired_at before the next catalog sync sets content_entities.retired_at. See Retirement System — Catalog Soft-Delete.

Analysis and Embeddings

Content analysis

When a content entity has Showroom content (labs and demos), the scan pipeline clones the repo, reads the AsciiDoc, and sends it to an LLM for structured analysis. Results are stored in showroom_analysis (1:1 with content_entities):

• Structured fields: summary, products, topics, audience, difficulty, modules, learning objectives, duration estimate, format suitability, use cases
• Change detection: content_hash tracks the SHA of the analyzed content; is_stale flags when the repo has new commits
• Curator enrichment: notes (free text), curated_duration_min (manual override), review_reasons (vocabulary flagging)

Embeddings

After analysis, RCARS generates 768-dimensional vector embeddings using the nomic-embed-text-v1.5 model, served by a dedicated vLLM HTTP server (OpenAI-compatible /v1/embeddings API).

Each content entity can have multiple embeddings in the embeddings table:

embed_type	What it embeds	Used for
summary	The full analysis summary	Advisor vector search, content overlap
module	Individual module titles + descriptions	Fine-grained matching

Key columns:

• content_type and source: Enable filtered searches (e.g., only search labs, or only Babylon content)
• content_text: The original text that was embedded (for debugging and re-embedding)

Nomic requires task prefixes: search_document: when indexing content, search_query: when embedding a user query. RCARS applies these automatically.

Performance and Retirement

Performance data

Performance metrics flow from the RHDP Reporting MCP Server into two tables:

• performance_channels: Raw metrics per (content_id, channel). One row per content entity per data channel (e.g., rhdp for provisioning data). Stores provisions, unique users, cost, pipeline touched/closed amounts, success ratios, and windowed_metrics (JSONB with time-bucketed snapshots for 3m/6m/9m/12m views).
• performance_scores: Computed performance score per content entity. Stores the overall performance_score (0-100 percentile, higher = better), score_breakdown (JSONB with component scores), and ignored_until (curator mute).

This replaces the old monolithic reporting_metrics table, separating raw channel data from computed scores.

Retirement workflow

The retirement_workflow table tracks the lifecycle of content being retired, keyed by content_id:

reviewed → approved → notified → started → retired

Review, approval, notification, and start steps have timestamp/actor pairs. Retirement completion has step_retired_at only. Additional fields track the decision reason, frozen channel-keyed metrics, Jira linkage, target date, replacement, and notes. agv_notice_pr_url, agv_retire_pr_url, and agv_revert_pr_url store external PR references; live merge states are fetched rather than stored as additional status columns.

The API resolves a base name to a stage-specific content entity, and the retirement report deduplicates stage variants for display. Cancellation awaiting a revert uses the stored revert URL and a pending_revert action response, not a new workflow status. See Retirement System for the full lifecycle and Swagger UI for API contracts.

Supporting Tables

Infrastructure Catalog

• infrastructure: Unified registry of workload roles and base configs scanned from AgnosticD v2. Each entry has a role_name (primary key), type (workload or config), description, products, capabilities, category, requires, collection, and source_sha for change detection. Embeddings for infrastructure entries are stored in the shared embeddings table with content_type = 'infrastructure' (the FK constraint on embeddings.content_id is dropped to allow this, since infrastructure entries are not in content_entities).

Workloads and ACL

• babylon_item_workloads: Maps content entities to infrastructure workload roles (Ansible roles deployed by the content). Joined with the infrastructure table to resolve descriptions and products.
• babylon_item_acl_groups: Tracks which ACL groups have access to each content entity.
• workload_aliases: Alternative names for workload products (for search flexibility).

Enrichment and curation

• enrichment_tags: Curator-applied tags (tag_type + tag_value) on content entities -- audience, use-case, and custom classifications.
• overlap_candidates: Precomputed overlap pairs between content entities, replacing the former content_similarity table.

Sessions and jobs

• advisor_sessions: Query history -- each row is one turn in an advisor conversation. Tracks the query, intent, results (including the full chat envelope and scope), and which recommendation the user chose (chosen_content_id).
• jobs: Async job tracking for long-running operations (scans, refreshes, queries). Stores status, progress, and results.

Portfolio architectures

• portfolio_architectures: OSSPA Architecture Center portfolio architectures. content_id is pa:{ppid}, source='portfolio_arch', content_type='architecture'.
• architecture_analysis: LLM analysis results for portfolio architectures, linked to portfolio_architectures.

Retirement and reporting

• retirement_workflow: Retirement lifecycle tracking for low-performing items (review → approve → notify → start).
• nonprod_usage: Dev/event environment provision tracking for non-production items.
• field_source_provisions: Field-sourced provision metrics from the reporting database.

Auth and operational

• api_keys: External API authentication -- hashed keys with prefixes, roles, scopes, and expiry.
• role_assignments: OpenShift group-based role mapping for curator/admin access.
• analysis_log: Audit trail of curator actions and system events.
• token_usage: LLM token consumption tracking per operation, model, and provider. The provider column distinguishes between backends (e.g., anthropic, litemaas).
• vocabulary_unknown_terms: Unresolved product terms encountered during scans, for vocabulary curation.

Schema Management

The schema is defined as SCHEMA_SQL in src/api/rcars/db/database.py -- this is the single source of truth. There is no Alembic or external migration tool.

• Fresh installs: All tables use CREATE TABLE IF NOT EXISTS
• Adding columns: Append ALTER TABLE <table> ADD COLUMN IF NOT EXISTS <col> <type> at the bottom of SCHEMA_SQL
• Structural changes: Run rcars init-db --drop to drop and recreate all tables (destructive -- requires data re-population via pipelines)

rcars init-db runs create_schema() on every deploy. The entire block is idempotent.

Full Schema Reference

The complete CREATE TABLE statements from SCHEMA_SQL. This is what an AI agent should parse for column-level details.

-- ═══════════════════════════════════════════════════════════════════
-- content_entities — universal entity registry
-- ═══════════════════════════════════════════════════════════════════
CREATE TABLE IF NOT EXISTS content_entities (
    content_id      TEXT PRIMARY KEY,
    source          TEXT NOT NULL,
    content_type    TEXT NOT NULL,
    is_hands_on     BOOLEAN NOT NULL DEFAULT FALSE,

    display_name    TEXT NOT NULL,
    summary         TEXT,
    products_json   JSONB,
    topics_json     JSONB,
    audience_json   JSONB,
    difficulty      TEXT,

    retired_at      TIMESTAMPTZ,
    retirement_reason TEXT,
    created_at      TIMESTAMPTZ DEFAULT NOW(),
    updated_at      TIMESTAMPTZ DEFAULT NOW()
);

-- ═══════════════════════════════════════════════════════════════════
-- babylon_items — Babylon-specific extension (1:1 with content_entities)
-- ═══════════════════════════════════════════════════════════════════
CREATE TABLE IF NOT EXISTS babylon_items (
    content_id      TEXT PRIMARY KEY REFERENCES content_entities(content_id) ON DELETE CASCADE,
    ci_name         TEXT NOT NULL UNIQUE,

    category        TEXT,
    stage           TEXT,
    catalog_namespace TEXT,
    is_prod         BOOLEAN DEFAULT FALSE,
    is_published    BOOLEAN DEFAULT FALSE,
    published_ci_name TEXT,
    base_ci_name    TEXT,

    showroom_url    TEXT,
    showroom_ref    TEXT,
    content_path    TEXT,
    showroom_url_override TEXT,

    is_agd_v2       BOOLEAN DEFAULT FALSE,
    agd_config      TEXT,
    cloud_provider  TEXT,
    ocp_version     TEXT,
    os_image        TEXT,
    worker_instance_count TEXT,
    control_plane_instance_count TEXT,
    instances_json  JSONB,

    keywords        TEXT[],
    description     TEXT,
    owners_json     JSONB,
    icon_url        TEXT,
    last_crd_update TIMESTAMPTZ,
    last_refreshed  TIMESTAMPTZ DEFAULT NOW(),

    scan_status     TEXT NOT NULL DEFAULT 'not_scanned',
    scan_error_class TEXT,
    scan_error      TEXT,
    scan_failed_at  TIMESTAMPTZ
);

-- ═══════════════════════════════════════════════════════════════════
-- showroom_analysis — LLM-generated content analysis
-- ═══════════════════════════════════════════════════════════════════
CREATE TABLE IF NOT EXISTS showroom_analysis (
    content_id              TEXT PRIMARY KEY REFERENCES content_entities(content_id) ON DELETE CASCADE,

    summary                 TEXT,
    products_json           JSONB,
    topics_json             JSONB,
    audience_json           JSONB,
    difficulty              TEXT,
    content_hash            TEXT,
    last_analyzed           TIMESTAMPTZ,
    is_stale                BOOLEAN DEFAULT FALSE,
    stale_commit            TEXT,

    content_type            TEXT,
    modules_json            JSONB,
    learning_objectives_json JSONB,
    estimated_duration_min  INTEGER,
    curated_duration_min    INTEGER CHECK (curated_duration_min >= 0),
    format_suitability_json JSONB,
    use_cases_json          JSONB,

    last_repo_commit        TEXT,
    last_repo_updated       TIMESTAMPTZ,

    enrichment_review_needed BOOLEAN DEFAULT FALSE,
    review_reasons           JSONB,
    notes                   TEXT
);

-- ═══════════════════════════════════════════════════════════════════
-- embeddings — 768-dim vectors from nomic-embed-text-v1.5 via vLLM
-- Note: FK on content_id is dropped at runtime to allow infrastructure
-- embeddings (keyed by role_name, not a content_entities row).
-- ═══════════════════════════════════════════════════════════════════
CREATE TABLE IF NOT EXISTS embeddings (
    id              SERIAL PRIMARY KEY,
    content_id      TEXT NOT NULL,
    content_type    TEXT NOT NULL,
    source          TEXT NOT NULL,
    embed_type      TEXT NOT NULL,
    module_title    TEXT,
    content_text    TEXT,
    embedding       vector(768)
);

-- ═══════════════════════════════════════════════════════════════════
-- performance_channels — per-channel performance metrics
-- ═══════════════════════════════════════════════════════════════════
CREATE TABLE IF NOT EXISTS performance_channels (
    id                      SERIAL PRIMARY KEY,
    content_id              TEXT NOT NULL REFERENCES content_entities(content_id) ON DELETE CASCADE,
    channel                 TEXT NOT NULL,

    provisions              INTEGER DEFAULT 0,
    unique_users            INTEGER DEFAULT 0,
    requests                INTEGER DEFAULT 0,
    page_views              INTEGER DEFAULT 0,
    downloads               INTEGER DEFAULT 0,
    completions             INTEGER DEFAULT 0,

    pipeline_touched        NUMERIC,
    closed_amount           NUMERIC,
    marketing_spend         NUMERIC,
    total_cost              NUMERIC,
    avg_cost_per_provision  NUMERIC,
    success_ratio           NUMERIC,

    first_activity          DATE,
    last_activity           DATE,

    windowed_metrics        JSONB DEFAULT '{}'::jsonb,

    synced_at               TIMESTAMPTZ DEFAULT NOW(),
    UNIQUE(content_id, channel)
);

-- ═══════════════════════════════════════════════════════════════════
-- performance_scores — computed retirement scores
-- ═══════════════════════════════════════════════════════════════════
CREATE TABLE IF NOT EXISTS performance_scores (
    content_id      TEXT PRIMARY KEY REFERENCES content_entities(content_id) ON DELETE CASCADE,
    performance_score INTEGER NOT NULL DEFAULT 0,
    score_breakdown JSONB,
    channel_scores  JSONB,
    computed_at     TIMESTAMPTZ DEFAULT NOW(),
    ignored_until   DATE
);

-- ═══════════════════════════════════════════════════════════════════
-- retirement_workflow — retirement lifecycle tracking
-- ═══════════════════════════════════════════════════════════════════
CREATE TABLE IF NOT EXISTS retirement_workflow (
    content_id          TEXT PRIMARY KEY REFERENCES content_entities(content_id) ON DELETE CASCADE,
    status              TEXT NOT NULL DEFAULT 'reviewed',
    step_reviewed_at    TIMESTAMPTZ,
    step_reviewed_by    TEXT,
    step_approved_at    TIMESTAMPTZ,
    step_approved_by    TEXT,
    approval_reason     TEXT,
    approval_snapshot   JSONB,
    step_notified_at    TIMESTAMPTZ,
    step_notified_by    TEXT,
    step_started_at     TIMESTAMPTZ,
    step_started_by     TEXT,
    retirement_target_date DATE,
    step_retired_at     TIMESTAMPTZ,
    replacement_ci      TEXT,
    replacement_name    TEXT,
    curator_notes       TEXT,
    jira_key            TEXT,
    jira_project        TEXT NOT NULL DEFAULT 'RHDPCD',
    agv_notice_pr_url   TEXT,
    agv_retire_pr_url   TEXT,
    agv_revert_pr_url   TEXT,
    created_at          TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at          TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- ═══════════════════════════════════════════════════════════════════
-- babylon_item_workloads — workload role associations
-- ═══════════════════════════════════════════════════════════════════
CREATE TABLE IF NOT EXISTS babylon_item_workloads (
    id SERIAL PRIMARY KEY,
    content_id TEXT NOT NULL REFERENCES content_entities(content_id) ON DELETE CASCADE,
    workload_fqcn TEXT NOT NULL,
    workload_role TEXT NOT NULL,
    workload_collection TEXT,
    UNIQUE(content_id, workload_fqcn)
);

-- ═══════════════════════════════════════════════════════════════════
-- babylon_item_acl_groups — ACL group membership
-- ═══════════════════════════════════════════════════════════════════
CREATE TABLE IF NOT EXISTS babylon_item_acl_groups (
    id SERIAL PRIMARY KEY,
    content_id TEXT NOT NULL REFERENCES content_entities(content_id) ON DELETE CASCADE,
    group_name TEXT NOT NULL,
    UNIQUE(content_id, group_name)
);

-- ═══════════════════════════════════════════════════════════════════
-- infrastructure — unified workload roles + base configs catalog
-- ═══════════════════════════════════════════════════════════════════
CREATE TABLE IF NOT EXISTS infrastructure (
    role_name   TEXT PRIMARY KEY,
    fqcn        TEXT,
    collection  TEXT,
    type        TEXT NOT NULL,
    description TEXT,
    products    JSONB DEFAULT '[]',
    capabilities JSONB DEFAULT '[]',
    category    TEXT,
    requires    JSONB DEFAULT '[]',
    source_sha  TEXT,
    scanned_at  TIMESTAMPTZ
);

-- ═══════════════════════════════════════════════════════════════════
-- Reference tables
-- ═══════════════════════════════════════════════════════════════════
CREATE TABLE IF NOT EXISTS workload_aliases (
    id SERIAL PRIMARY KEY,
    product_name TEXT NOT NULL,
    alias TEXT NOT NULL UNIQUE,
    added_at TIMESTAMPTZ DEFAULT NOW()
);

-- ═══════════════════════════════════════════════════════════════════
-- Enrichment and curation
-- ═══════════════════════════════════════════════════════════════════
CREATE TABLE IF NOT EXISTS enrichment_tags (
    id SERIAL PRIMARY KEY,
    content_id TEXT NOT NULL REFERENCES content_entities(content_id) ON DELETE CASCADE,
    tag_type TEXT NOT NULL,
    tag_value TEXT NOT NULL,
    added_by TEXT,
    added_at TIMESTAMPTZ DEFAULT NOW(),
    UNIQUE(content_id, tag_type, tag_value)
);

-- ═══════════════════════════════════════════════════════════════════
-- Operational tables
-- ═══════════════════════════════════════════════════════════════════
CREATE TABLE IF NOT EXISTS analysis_log (
    id SERIAL PRIMARY KEY,
    ci_name TEXT,
    action TEXT NOT NULL,
    user_id TEXT,
    details TEXT,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS token_usage (
    id SERIAL PRIMARY KEY,
    operation TEXT NOT NULL,
    model TEXT NOT NULL,
    ci_name TEXT,
    query_text TEXT,
    input_tokens INTEGER NOT NULL DEFAULT 0,
    output_tokens INTEGER NOT NULL DEFAULT 0,
    provider TEXT DEFAULT 'anthropic',
    created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS jobs (
    id TEXT PRIMARY KEY,
    job_type TEXT NOT NULL,
    status TEXT NOT NULL DEFAULT 'queued',
    queue TEXT NOT NULL DEFAULT 'default',
    created_by TEXT,
    progress_json JSONB,
    result_json JSONB,
    error TEXT,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    started_at TIMESTAMPTZ,
    completed_at TIMESTAMPTZ
);

CREATE TABLE IF NOT EXISTS api_keys (
    id SERIAL PRIMARY KEY,
    key_hash TEXT NOT NULL UNIQUE,
    key_prefix TEXT NOT NULL,
    name TEXT NOT NULL,
    created_by TEXT NOT NULL,
    scopes TEXT[],
    role TEXT NOT NULL DEFAULT 'user',
    expires_at TIMESTAMPTZ,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    last_used_at TIMESTAMPTZ,
    revoked_at TIMESTAMPTZ
);

CREATE TABLE IF NOT EXISTS advisor_sessions (
    id SERIAL PRIMARY KEY,
    session_id TEXT NOT NULL,
    turn_index INTEGER NOT NULL,
    user_email TEXT,
    query_text TEXT,
    event_url TEXT,
    results_json JSONB,
    overall_assessment TEXT,
    chosen_ci_name TEXT,
    chosen_content_id TEXT,
    chosen_at TIMESTAMPTZ,
    opted_out BOOLEAN NOT NULL DEFAULT FALSE,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    intent TEXT,
    envelope_json JSONB,
    scope_json JSONB
);