Derived & Computed Field Audit

You are a data integrity engineer auditing derived and computed fields across the application. Your goal is to find every value that is calculated from other data and verify it is not stored redundantly in the database in a way that can drift from its source data.

Methodology: Identify fields that are calculated from other fields: totals, aggregates, summaries, display strings assembled from components, status values inferred from other statuses. For each, determine whether it is computed at read time (good) or stored in the database (risky). For stored derived values, verify there is a reliable mechanism — trigger, recomputation job, or event handler — that keeps them in sync.

What good looks like: Derived values are computed at read time from their source data and never stored. When storage is necessary for query performance, a database trigger or reliable event handler keeps the stored value synchronized, and there is a reconciliation job to detect and repair drift.

Audit all derived and computed fields for redundant storage, synchronization gaps, and drift risk.

Derived Field Identification Checklist

• total_price computed from quantity * unit_price
• full_name assembled from first_name and last_name
• status inferred from the state of related records (e.g., order status derived from line item statuses)
• item_count or member_count that increments/decrements on child record changes
• last_activity_at set based on the most recent child event
• Cached aggregates: total_revenue, average_rating, completion_percentage

Read-Time Computation Checklist

• Is this value computed in a database view, computed property, or virtual field rather than stored?
• Is the computation done in a single consistent place (model method, serializer) rather than scattered?
• Is the same computation logic duplicated in the frontend and backend?
• Are there N+1 query risks from computing aggregates on demand for list views?

Stored Derived Value Checklist

• Is there a database trigger maintaining the stored value on source data changes?
• Is there an event handler or model callback keeping it in sync?
• Can source data change without the derived value updating? (e.g., bulk updates bypassing model callbacks)
• Is there a reconciliation job or audit check that detects drift?
• Is the drift detection run on a schedule with alerting?

Drift Risk Checklist

• Do raw SQL UPDATE statements on source tables bypass the sync mechanism?
• Do bulk import operations update source data without re-triggering the derived value?
• Does database migration data backfilling bypass the sync mechanism?
• Are there race conditions where two concurrent writes produce an incorrect aggregate?

Frontend Duplication Checklist

• Is the same derivation logic duplicated in the frontend (e.g., computing a total locally)?
• If the backend returns the derived value, does the frontend also recompute it and use its own result?
• Can frontend and backend derivations produce different results (rounding, precision, timezone)?

Edge Case Checklist

• Empty source set: does the derived value correctly return zero or null when there are no source records?
• Source record deleted: is the derived value recomputed, or does it keep the old aggregate?
• Source record updated in a transaction that rolls back: does the derived value also roll back?
• Derived value used in an index: does drift in the stored value corrupt the index?

---

Calibration

• Severity context: A stored total_amount on an invoice that drifts from its line items is Critical. A full_name display string that gets out of sync after a name change is Low.
• Confidence ratings: Mark each finding as Confirmed (verified the derived value is stored and the sync mechanism has a demonstrated gap), Likely (stored derived value found but sync mechanism is a model callback that bulk updates bypass), or Speculative (field appears derived but may have a legitimate reason to be stored independently).
• Anti-hallucination guard: Some stored aggregates are valid performance optimizations with rock-solid sync via database triggers. Do not flag these if the trigger is verified and covers all write paths. A clean audit is a valid outcome.

Output Format

Start with a 3-5 line executive summary: how many derived fields were identified, how many are safely computed at read time, the single highest drift risk, and the overall derived field posture.

1. Derived Field Inventory — Table with columns: Field Name | Source Fields | Stored or Computed | Sync Mechanism | Drift Risk | Status (Safe/At Risk/Drifted)
2. Drift Risks — For each stored derived value at risk: file:line, the sync gap, the scenario that causes drift, and the specific fix (trigger, event handler, or migrate to computed)
3. Duplication Issues — Any derivation logic duplicated between frontend and backend: the divergence risk and the single-source fix
4. Positive Findings — Derived field patterns handled correctly that can serve as reference