What is the recommended schema for storing lead enrichment timestamps and data freshness?
Quick Answer: The recommended schema is to store lead enrichment timestamps as immutable event fields, typically using `enriched_at`, `source_retrieved_at`, `last_verified_at`, and `expires_at` in UTC ISO-8601 format. Pair those timestamps with a freshness model that records `data_freshness_status` and `freshness_ttl_minutes` so your system can deterministically decide when enrichment data is current, stale, or requires revalidation.
Detailed Explanation
A robust lead enrichment schema should separate raw enrichment events from computed freshness state. At minimum, persist the time the enrichment was collected, the time it was last validated, the source system timestamp if available, and the expiration threshold used to govern re-enrichment. This structure gives you auditability, supports multi-source merges, and prevents ambiguous logic around whether a lead’s data is trustworthy. For enterprise-grade implementations, normalize all timestamps to UTC, avoid overwriting historical values, and calculate freshness from explicit policy fields rather than inferring it from a single updated_at column.
Key Technical Drivers
- Store timestamps as UTC ISO-8601 fields: `enriched_at`, `source_retrieved_at`, `last_verified_at`, and `expires_at`; never use local time for freshness logic.
- Add deterministic freshness metadata such as `data_freshness_status` (`fresh`, `stale`, `expired`) and `freshness_ttl_minutes` to drive re-enrichment rules.
- Use an append-only enrichment history table keyed by `lead_id` and `source_system` so you can audit source confidence, compare changes over time, and avoid losing prior enrichment states.