Public records are inherently temporal, and most normalization failures stem from conflating different time dimensions. This page maps every temporal field Codex emits per dataset, so you never have to guess which clock a timestamp is on.
Universal fields
Every Codex record carries up to seven temporal fields where semantically applicable. These extend the normalization standard bitemporal contract.
| Field | Meaning |
|---|
ingested_at | When the Codex system first observed the row. Never mutates after insert. |
modified_at | When the row was last updated. Refreshed on every write. |
occurred_at | When the real-world event actually happened. |
filed_at | When the record was filed or submitted to the authority. |
published_at | When the source authority published the record. |
effective_from | When the record or ruling became legally effective. |
effective_to | When it expired or was superseded. Null means currently active. |
filed_at is distinct from occurred_at for records where the filing is itself the event (e.g. SEC filings) versus records where filing follows the event (e.g. a council vote happens, minutes are filed days later).
Field availability by dataset
| Field | Civic | Events | AIS | USG | LEHD | POI | Permits | OSHA |
|---|
ingested_at | required | required | required | required | required | required | required | required |
modified_at | required | required | required | required | required | required | required | required |
occurred_at | required | required | required | — | — | optional | required | required |
filed_at | optional | optional | — | — | — | — | required | required |
published_at | required | optional | — | — | — | optional | optional | optional |
effective_from | optional | — | — | — | — | — | required | — |
effective_to | optional | — | — | — | — | — | optional | — |
Domain-specific temporal fields
Some datasets carry additional temporal fields beyond the universal set.
AIS Maritime — port-call lifecycle
Port calls have four clocks that must not be conflated:
| Field | Meaning | Source |
|---|
eta | Estimated time of arrival | Vessel-declared (AIS message 5) |
ata | Actual time of arrival | Observed from first in-port AIS position |
etd | Estimated time of departure | Vessel-declared or port authority schedule |
atd | Actual time of departure | Observed from last in-port AIS position |
occurred_at is set to ata for port.entered events and atd for port.departed events. ETA/ETD are retained as separate fields, never overwritten by actuals. Use both to analyze punctuality:
SELECT vessel_name, port_id, (ata - eta) AS arrival_delay
FROM port_calls
WHERE ata IS NOT NULL AND eta IS NOT NULL;
Permits — six-stage lifecycle
Each permit record carries up to six stage-specific timestamps. Only filed_at is universally present; others are populated as stages complete.
| Field | Meaning |
|---|
filed_at | Application filed with the authority (required) |
applied_at | Alias for filed_at in jurisdictions that use the term |
approved_at | Approved by the authority |
issued_at | Permit issued or recorded. May differ from approved_at by days. |
inspected_at | Last inspection, if tracked |
expired_at | Expired or withdrawn |
occurred_at is set to the stage-specific timestamp of the event type — a permit.filed event has occurred_at = filed_at, a permit.approved event has occurred_at = approved_at.
Civic Intelligence — publication lag
For council proceedings, the gap between occurred_at (meeting date) and published_at (minutes publication) can be days to weeks. Both fields are always populated so you can analyze either event time or public-record time.
OSHA — case lifecycle
| Field | OSHA source column | Meaning |
|---|
filed_at | open_date | Inspection opened |
occurred_at | varies | Event that triggered inspection |
modified_at | close_case_date | Case closed, or last update if still open |
Common pitfalls
Avoid these temporal field mistakes — they are the most common source of data errors in downstream analysis.
- Single-timestamp fallacy. Do not collapse
occurred_at, published_at, and filed_at into one field. They are rarely the same value.
- Using
ingested_at for event-time analysis. ingested_at is the wrong clock for analysis — use occurred_at or the domain-specific actual time.
- Forgetting
modified_at on updates. Every writer must refresh modified_at. Skipping this breaks incremental-sync consumers.
- Overwriting ETA with ATA. Overwriting the estimate with the actual loses the punctuality signal. Keep both.
- Confusing
applied_at and filed_at. Some jurisdictions distinguish these. Codex publishes one or the other per record, never both with different values.
Example queries
Permits filed but not yet approved after 90 days
SELECT id, filed_at, jurisdiction_slug
FROM normalized_permits
WHERE approved_at IS NULL
AND filed_at < now() - interval '90 days';
Publication lag distribution for council proceedings
SELECT
jurisdiction_slug,
percentile_cont(0.5) WITHIN GROUP (ORDER BY published_at - occurred_at) AS p50_lag,
percentile_cont(0.9) WITHIN GROUP (ORDER BY published_at - occurred_at) AS p90_lag
FROM civic_records
WHERE published_at IS NOT NULL AND occurred_at IS NOT NULL
GROUP BY jurisdiction_slug;
Port calls where arrival was more than 12 hours late
SELECT vessel_name, port_id, ata - eta AS delay_hours
FROM port_calls
WHERE ata IS NOT NULL AND eta IS NOT NULL
AND ata - eta > interval '12 hours';
