Modeling Semarize Outputs in Your Database or Warehouse

Layer 1: Raw Run Log (append-only)

Every evaluation run produces a raw output. Store this as-is, append-only, and never modify it. This layer is your insurance policy - if your scoring logic changes, you can always re-derive signals from the raw output. In practice this means one row per run, with the full JSON response, transcript reference, timestamps, and run configuration.

• One row per evaluation run (not per signal)
• Full JSON response from the evaluation engine
• Transcript reference (inline text or object storage pointer)
• Run configuration: which kit, which bricks, any custom parameters
• Append-only - never update or delete rows in this layer

Layer 2: Signal / Event Table (row-per-signal, versioned)

Extract individual signals from the raw output and store them as structured, typed rows. Each row represents one signal from one evaluation of one conversation. This is where most queries and joins happen. Always include the scoring version so you can filter and compare across framework iterations.

• One row per signal per evaluation (e.g., overall_score, risk_flag, competitor_mention)
• Typed columns: signal_name, signal_value, signal_type, confidence
• Versioned: scoring_version or kit_version identifies the framework that produced it
• Linked to raw layer via run_id or call_id + evaluated_at
• This is your primary query and join layer

Layer 3: Consumption Views (latest snapshot, optional wide marts)

Create views or materialized tables that present the latest signal values per conversation. These power dashboards, CRM syncs, and downstream applications. They're derived from Layer 2 and can be rebuilt at any time.

• Latest-value views: one row per conversation with the most recent score for each signal
• Wide marts: pivot signals into columns for BI tools that prefer wide tables
• CRM sync views: filtered subsets ready to push back to Salesforce, HubSpot, etc.
• Trend views: time-series aggregations for tracking scoring changes over time
• These are derived - rebuild them whenever the underlying data changes

Dumping one big JSON blob only

Problem: Storing the entire evaluation response as a single JSON column with no extraction. Queries require JSON parsing on every read, BI tools can't access individual signals without custom transforms, and storage/compute costs balloon because you scan the full blob for every query.

Fix: Extract signals into typed columns in Layer 2. Keep the raw JSON in Layer 1 for reference, but don't make it your query layer.

Wide table as source of truth

Problem: One table with a column for every possible signal (overall_score, risk_flag, competitor_mentioned, next_steps_agreed, ...). Adding a new signal means a schema migration. Sparse columns waste storage. Historical comparisons are impossible because the column didn't exist when older conversations were evaluated.

Fix: Use the row-per-signal model in Layer 2. New signals appear as new rows, not new columns. Create wide views in Layer 3 for BI tools that prefer that shape.

Overwriting scores without version tracking

Problem: Updating signal values in place when the scoring framework changes. You lose all historical context - you can't tell whether a score changed because the conversation was different or because the scoring logic changed. Trend analysis becomes meaningless.

Fix: Never overwrite. Insert new rows with the updated scoring_version. Use consumption views to show the latest value while preserving the full history.

Skipping the raw layer

Problem: Only storing extracted signals without keeping the raw evaluation output. When your scoring logic improves (and it will), you have no way to re-derive signals from historical conversations without re-fetching and re-evaluating every transcript.

Fix: Always persist the raw run log. Storage is cheap. The ability to reprocess historical data is invaluable.

Mixing CRM sync data with analytical data

Problem: Using the same table for both CRM sync (latest values) and historical analysis. CRM syncs need the most recent score; analysis needs the full history. Trying to serve both from one table leads to either stale CRM data or lost history.

Fix: Layer 2 holds the full history. Layer 3 consumption views serve the latest values for CRM sync. Each layer serves its purpose cleanly.