04. Feedback storage and schema¶

Capture is one discipline. Storage is what makes the captured signals queryable, joinable, and durable. The schema is the contract; the storage is the substrate.

A platform engineer at a Bengaluru SaaS company has feedback collection wired up. Six months in, she tries to answer a question: "which features had the worst feedback last quarter, sliced by user tier?" The query takes two days. Explicit feedback is in one table; implicit signals are in another; the response_id is in the audit log; user tier is in a third system. Each join is a manual integration. The team's iteration loop is gated by data-engineering work. The fix is a unified feedback store with consistent schema, joinable by response_id and user_id_hash, with tier and feature pre-joined. The next quarterly review takes thirty minutes.

This chapter is the schema and storage discipline. The signals need to be findable, joinable, and durable.

What the schema must capture¶

Six core fields across all signal types:

event_id: <unique>
ts: <timestamp>
event_type: <thumbs | rating | comment | implicit:abandonment | ...>
event_value: <signal-specific payload>
response_id: <join to the AI response>
context:
  user_id_hash: <hashed identifier>
  session_id:
  feature:
  segment:
  device:
  ...

Explicit and implicit signals share this shape; the event_value differs by type.

The schema is enforced; events that fail validation are rejected (with logging so the gap is visible). A schema-less feedback store accumulates garbage; the team's downstream queries break.

Why response_id is the load-bearing field¶

Every signal joins to the response it is about. The response_id (from the system's audit per 02_ai_infrastructure/01 chapter 11) carries:

The prompt version used
The model version used
The system's actual output
The input context
The tenant, the actor, the audit_id

Joining feedback to the response gives the team: "for prompt v3.2 on smart-reasoner alias in the support feature, the negative thumbs rate is X." Slice and dice by any dimension that lives in the audit.

Without the response_id on the feedback, the join is impossible; the signals float; the analysis is hobbled.

Storage choice¶

The feedback store has different access patterns than the audit log:

Write-heavy. Events from every user action.
Aggregate-read-heavy. Dashboards, weekly reviews, analysis queries.
Sometimes per-event-read. Investigating specific cases.
Long retention. Aligned with audit retention; 1-7 years.

Common choices:

Append-only OLAP store (BigQuery, Snowflake, Redshift) — fits aggregate reads well; per-event reads are okay.
Time-series database (Druid, ClickHouse) — efficient for time-sliced aggregates.
Application database with replicas — simpler but doesn't scale to analytics workloads.

Most platforms use OLAP for the analytical store; events stream from the application via a small ingestion pipeline.

Joining feedback to the audit and other systems¶

A typical analytical query joins:

Feedback (this module's schema)
Audit log (the response details from 02_ai_infrastructure/01)
User attributes (tier, segment, region) from CRM
Sometimes prompt registry (current vs historical prompt versions)

Pre-join key fields into the feedback table for fast queries:

prompt_version (denormalised from the audit)
model_used (denormalised)
feature (always present)
user_segment (denormalised from CRM nightly)

Pre-joining is a tradeoff between storage cost and query speed. For a feedback table queried frequently across these dimensions, pre-joining is worth the cost.

Retention¶

Feedback is user data; the retention discipline from 03_ai_security_safety/03_data_access_governance chapter 06 applies.

A reasonable retention:

Raw event with comment text — 1 year (PII potentially in comments)
Structured event without text — 2-3 years (aggregate analysis)
Aggregated metrics — indefinite

The retention windows are documented; automatic deletion at boundaries.

Privacy¶

Same discipline as elsewhere:

PII in free-text comments — pattern-detection and redaction (or pseudonymisation if reversibility is needed).
User identifiers — hashed; the hash supports correlation without revealing identity.
Right-to-be-forgotten — the feedback store participates in RTBF (chapter 09 of 03_ai_security_safety/03_data_access_governance).
Cross-tenant isolation — feedback is tenant-tagged; queries enforce.

The full privacy discipline is in chapter 08 of this module.

What the schema does not capture¶

The system's response itself. That lives in the audit. The feedback joins by response_id.
The user's full session history. Implicit signals reference prior messages, but the full conversation lives in conversation storage.
Derived analytics. Aggregates are computed, not stored as raw events.

Common mistakes¶

Per-team feedback stores. Each team builds their own; cross-team analysis is impossible.

Missing response_id. Feedback floats; the analytical pipeline cannot use it.

No schema enforcement. Drift; downstream queries break silently.

PII in raw events. A breach in waiting.

Indefinite retention. Compliance risk; the discipline is bounded retention.

Interview Q&A¶

Q1. Why is the response_id the load-bearing field in the feedback schema? Because the join to the audit log is what makes feedback actionable. The audit carries the prompt version, the model, the input, the system's output, the tenant, the actor. Joined to feedback, the team can ask "for prompt v3.2 on this feature, what is the negative-thumbs rate by tenant?" Without response_id, feedback is "a user clicked thumbs-down at this time"; with it, it is "a user clicked thumbs-down on this specific response generated by this prompt against this input." The difference is everything for analysis. Wrong-answer notes: "the user_id is enough" misses the per-response specificity.

Q2. Should you pre-join feature, prompt version, and model into the feedback table? Yes for fields queried frequently across dimensions; the storage cost is small, the query-speed value is large. Pre-join feature, prompt_version, model_used, user_segment. Re-join expensive fields (full audit details) on demand. The choice is per-platform; the principle is "denormalise the hot fields, normalise the cold." Wrong-answer notes: "always join from raw" produces slow queries that gate analysis.

Q3. What storage choice fits the feedback workload? Append-only OLAP (BigQuery, Snowflake, Redshift) is the common fit. Write-heavy (events stream in); aggregate-read-heavy (dashboards, weekly review); long retention; analytical query patterns. A time-series database (Druid, ClickHouse) is appropriate if time-slicing dominates. An application database with replicas does not scale to analytics workloads. Stream events via a small ingestion pipeline. Wrong-answer notes: "the application DB" produces a brittle setup that fails at scale.

Q4. The team has feedback retention set to "indefinite." Why is that a problem? Compliance exposure (feedback contains user data; regulations require bounded retention). Storage cost growth. Subject erasure complexity. The fix is a documented retention window per data type — comments at 1 year, structured events at 2-3 years, aggregated metrics indefinite. Automatic deletion at boundaries; verified per the discipline of 03_ai_security_safety/03_data_access_governance chapter 06. Wrong-answer notes: "we'll deal with it later" produces the next audit's findings.

What to do differently after reading this¶

Unified schema across explicit and implicit signals.
Response_id is mandatory on every event; reject events without it.
OLAP storage with denormalised hot fields for fast analytical queries.
Apply the privacy and retention discipline from chapter 06 of 03_ai_security_safety/03_data_access_governance.
Cross-team feedback stores are a single platform table, not per-team copies.

Bridge. The signals are captured and stored. The next discipline is converting them into the team's artefacts — the eval set, the judge calibration, the prompt iteration. The next chapter is the signal-to-eval pipeline. → 05-from-signal-to-eval.md