The Interview Detail Most Candidates Miss: Coupon Expiry Is a Data Problem

In a Coupons & Deals platform, “expiry” is not just a UI label — it’s a core data contract that touches correctness, performance, and reporting. How you model and enforce expiration affects queries, analytics, user experience, and compliance.

Below is a concise, interview-friendly summary of the decisions you should make and why they matter, plus practical implementation patterns and gotchas.

---

1) Query path: exclude expired by default

Behavior:

• Default search/listing queries should exclude expired coupons.
• Use an indexed expiry field (e.g., expiry_date) so the filter is fast and selective.

Why:

• Keeps user-facing search performant and correct (users shouldn't see unusable coupons).
• Prevents accidentally applying expired coupons in checkout logic.

Implementation notes:

• Add a persistent boolean or computed field like is_active if your DB supports it, but still keep expiry_date as the source of truth.
• In SQL: create a composite index on (is_active, expiry_date) or on expiry_date alone if is_active is derived.
• In document stores: use a TTL index for automatic deletes if you want hard-deletes after a retention period (see Cleanup).

Example (SQL):

SELECT * FROM coupons WHERE expiry_date > CURRENT_TIMESTAMP AND active = TRUE ORDER BY priority DESC, expiry_date ASC;

---

2) State model: mark as expired (don’t delete immediately)

Behavior:

• When expiry_date passes, change the coupon state to expired (soft-delete style) instead of immediate hard delete.

Why:

• Preserves historical data for analytics, audits, and dispute resolution.
• Enables investigation of incidents (e.g., "Why was this applied?" or "Was this coupon active at this timestamp?").

Recommended fields:

• created_at, starts_at, expiry_date
• state enum: draft, active, scheduled, expired, archived, deleted
• expired_at timestamp (when the system marked it expired)
• deleted_at timestamp for eventual hard-deletion

---

3) Cleanup: archive/purge after a retention window

Behavior:

• Run scheduled jobs (daily/weekly) to move expired coupons into an archive or to purge them after a retention window.

Why:

• Keeps primary indexes and tables small for performance.
• Satisfies storage cost and compliance requirements while preserving data for a defined period.

Options:

• Archive to a cost-effective store (S3/parquet) for long-term analytics.
• Use DB partitioning by date and drop old partitions.
• Use TTL indexes (MongoDB) or scheduled jobs to delete after retention.

Retention policy considerations:

• Business/Legal retention requirements
• Audit and dispute windows
• GDPR/CCPA: implement a path for user data removal that can override retention when necessary

---

4) Indexing & performance tips

• Index expiry_date (or a combined index with other common filters) to allow the DB to skip expired rows quickly.
• Consider maintaining a materialized view or denormalized table for active coupons used in high-throughput queries.
• Cache active coupons carefully and invalidate cache when state changes (or use short TTLs).
• For search engines (Elasticsearch): exclude expired docs at ingestion or use a filter that’s cheap to evaluate.

---

5) Analytics & reporting

• Keep expired coupons in your analytics DB long enough to analyze redemption rates over time.
• Store the coupon state transitions in an event log so you can reconstruct the state at any historical point (useful for billing disputes and A/B testing).
• Tag analytic events with the coupon version/id and the coupon expiry_date so joins are straightforward.

---

6) Edge cases & gotchas

• Timezones & clock skew: store timestamps in UTC and compare against server/DB time. Be explicit about whether expiry_date is inclusive or exclusive (e.g., expires at 00:00:00 UTC of the given date).
• Grace periods: business might want a short grace window for UX reasons — implement explicitly, not implicitly.
• Overlapping promos & stacking rules: expired coupons must not interact with stacking logic.
• Race conditions at checkout: validate coupon validity again at redemption time (server-side) even if shown as active in the UI.
• Compliance: users’ right-to-be-forgotten requests may require earlier deletion of records; have a process to reconcile with analytics needs.

---

7) What to say in interviews (concise checklist)

When asked about coupon expiry, state explicitly:

• "Expiry is a data contract affecting correctness (can't apply expired coupons), performance (filter/index), and reporting (analytics & audits)."
• Describe default query behavior: "Exclude expired items by default; index expiry_date."
• Describe state management: "Mark as expired (soft-delete) and retain for a configured retention window to support audits."
• Describe cleanup: "Archive or purge via scheduled jobs/TTL after retention, and handle compliance exceptions."
• Mention edge cases: timezone, race conditions at checkout, cache invalidation, and legal retention or deletion requirements.

This hits technical design, operational concerns, and compliance — the areas interviewers want to hear about.

---

8) Example data model (JSON-like)

{ "id": "coupon_123", "code": "SUMMER20", "starts_at": "2026-06-01T00:00:00Z", "expiry_date": "2026-06-30T23:59:59Z", "state": "expired", "expired_at": "2026-07-01T00:00:00Z", "created_at": "2026-05-01T12:00:00Z", "deleted_at": null n}

---

Final takeaway

Treat expiry as a first-class data design problem, not a cosmetic UI tag. In interviews, call out the three pillars: query behavior (performance/correctness), durable state model (analytics/auditability), and cleanup/retention (costs/compliance). That concise framing shows you understand both system design and operational realities.

#SystemDesign #BackendEngineering #DataEngineering