Bitemporal fields

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';

Get started

Frameworks

Reference

Universal fields

Field availability by dataset

Domain-specific temporal fields

AIS Maritime — port-call lifecycle

Permits — six-stage lifecycle

Civic Intelligence — publication lag

OSHA — case lifecycle

Common pitfalls

Example queries

Permits filed but not yet approved after 90 days

Publication lag distribution for council proceedings

Port calls where arrival was more than 12 hours late

​Universal fields

​Field availability by dataset

​Domain-specific temporal fields

​AIS Maritime — port-call lifecycle

​Permits — six-stage lifecycle

​Civic Intelligence — publication lag

​OSHA — case lifecycle

​Common pitfalls

​Example queries

​Permits filed but not yet approved after 90 days

​Publication lag distribution for council proceedings

​Port calls where arrival was more than 12 hours late

Universal fields

Field availability by dataset

Domain-specific temporal fields

AIS Maritime — port-call lifecycle

Permits — six-stage lifecycle

Civic Intelligence — publication lag

OSHA — case lifecycle

Common pitfalls

Example queries

Permits filed but not yet approved after 90 days

Publication lag distribution for council proceedings

Port calls where arrival was more than 12 hours late