Was the Supplier Approved Then? Query Valid Time and Recorded Time Separately

A supplier approval is corrected on Thursday to show that its scope changed on Tuesday. An order was reviewed on Wednesday. A single updated_at column cannot answer both questions an investigator now asks: what is the corrected approval history, and what did the system show at the time of review?

This article proposes a hypothetical procurement database. Every identifier, date and approval below is synthetic. It is not a Zenith Eclipse implementation or a real supplier decision.

Define the approval scope before its dates

An approval concerns a particular supplier, product scope and purchasing context. It is not a permanent property of a company name. For example, a feed wheat enquiry has an intended use and specification distinct from other wheat purchases. The proposed approval key must identify the actual approved scope rather than use a generic “wheat supplier” flag.

Use an internal scope identifier that resolves to a versioned definition. Include the purchasing organization or tenant in every lookup. This article assumes that identity resolution and the scope definition are already settled.

Give every assertion two intervals

Valid time says when the assertion applies in the business timeline. Recorded time says when that assertion was present in the database's accepted history.

Use half-open intervals: the start is included and the end is excluded. A transition at noon belongs to the new interval, never both. PostgreSQL provides tstzrange for timestamp ranges and @> for containment; its range documentation explains these bounds.

The following standalone query demonstrates a correction. The system originally recorded approval for all October. On October 22 at 09:00 UTC it accepted a correction: the scope was suspended from October 20.

WITH history(state, valid_during, recorded_during) AS (
  VALUES
  ('approved',
   tstzrange('2026-10-01 00:00+00', '2026-11-01 00:00+00', '[)'),
   tstzrange('2026-10-01 00:00+00', '2026-10-22 09:00+00', '[)')),
  ('approved',
   tstzrange('2026-10-01 00:00+00', '2026-10-20 00:00+00', '[)'),
   tstzrange('2026-10-22 09:00+00', NULL, '[)')),
  ('suspended',
   tstzrange('2026-10-20 00:00+00', '2026-11-01 00:00+00', '[)'),
   tstzrange('2026-10-22 09:00+00', NULL, '[)'))
)
SELECT state
FROM history
WHERE valid_during @> TIMESTAMPTZ '2026-10-21 12:00+00'
  AND recorded_during @> TIMESTAMPTZ '2026-10-21 12:00+00';

This selects approved. Change only the second query timestamp to October 23 at noon UTC and it selects suspended. The business instant remains Wednesday; the knowledge cutoff changes.

An unbounded recorded-time end means the assertion has not been superseded in this model. It does not mean perpetual business approval.

Write a correction as one controlled operation

In a real schema, attach an assertion ID, tenant, supplier and scope key, evidence reference, decision authority and correction reason. Keep the original assertion's state and valid interval. Close its recorded interval at the correction boundary and insert the replacement assertions in the same transaction.

Serialize changes for that scope so two reviewers cannot independently create incompatible replacements. Reject empty intervals. Define and enforce an invariant that no two accepted assertions for the same scope overlap in both valid and recorded time. This may use range exclusion constraints with appropriate operator classes, or a carefully serialized write path; a frontend overlap check is insufficient.

Do not describe a timestamp taken at transaction start as the exact moment every reader could see the commit. For strict reconstruction, keep a committed decision revision or snapshot reference on each order review. Timestamps are useful lookup coordinates, but the stored revision identifies exactly which evidence the decision consumed.

Preserve uncertainty and consequences

No matching row means “no assertion available for that query,” not “approved.” Multiple matches mean a history integrity error, not permission to take the newest row.

A retroactive correction must not silently rewrite the recorded outcome of an old purchase review. Produce a separate impact report listing potentially affected decisions, then apply the organization's authorized review procedure. A database query does not decide contractual consequences or release a commodity lot.

Test the exact correction boundary, a query before the first record, missing scope, overlapping assertions and two simultaneous corrections. Verify both historical and corrected views. The goal is an explainable answer to “what was known when,” while preserving the evidence for what is believed now.

---

About the publisher: Zenith Eclipse Co works across agricultural commodities, international logistics and machinery sourcing.