dbt marts

Locus’s analytics tables are built by a dbt-core project (axiom_locus) that transforms raw ingestion tables into typed, tested, APRS-compliant marts. Marts are the layer downstream consumers — the dashboard heatmap, Explorer, scoring service, and API — read from. Staging and intermediate models are internal scaffolding; only marts are part of the public contract.

Locus uses dbt-core only. There is no dbt Cloud deployment. Local development materializes to DuckDB; production runs against the Locus Postgres database.

Project layers

The axiom_locus dbt project has three model layers with fixed materializations:

Layer	Materialization	Purpose
`staging`	`view`	Thin, renamed views over raw source tables (`building_permits`, `nasa_firms_active_fires`, `cell_scores`). One staging model per source.
`intermediate`	`ephemeral`	Reusable joins and rollups (e.g. `int_safety_signals_per_cell`). Compiled inline — never persisted.
`marts`	`incremental`	Analytics-ready tables. Incremental by `ingested_at` watermark with `on_schema_change='append_new_columns'`.

Staging models normalize column names, cast types, and pass through the APRS envelope. Marts apply the aprs_envelope macro so every mart row carries record_id, source_uri, source_system, schema_version, normalization_version, acl_tier, occurred_at, ingested_at, and modified_at.

Sources

The dbt project reads from three source tables in the public schema:

Source	Identifier	Owner	dbt usage
`permits`	`building_permits`	Municipal permit collectors	Read-only via `stg_permits`
`nasa_firms`	`nasa_firms_active_fires`	NASA FIRMS ingestion	Read-only via `stg_nasa_firms`
`cell_scores`	`cell_scores`	Locus scorer service	Read-only via `stg_cell_scores`

cell_scores is owned by the scorer service. dbt reads it through stg_cell_scores but never writes to it. Do not point dbt models at cell_scores as a materialization target.

`mart_axiom_events`

mart_axiom_events is the unified event surface for Locus. It is the same axiom_events signal the dashboard heatmap and Explorer read from. Each row is one real-world event with H3-cell attribution and full APRS envelope.

Configuration

{{ config(
    materialized='incremental',
    unique_key='event_id',
    on_schema_change='append_new_columns'
) }}

Incremental — only rows where ingested_at is newer than the current watermark are added on each run.
Append-only — event_id (an md5 of {source}:{record_id}) is unique and stable. Re-running dbt is idempotent.
Schema-evolution-safe — new columns added to the model are appended to the table without rebuilds.

Columns

Column	Type	Description
`event_id`	`text`	MD5 URN of `{source}:{record_id}`. Unique, stable across runs.
`entity_id`	`text`	Logical entity the event applies to (e.g. `permit:123`, `h3:8828308281fffff`).
`entity_type`	`text`	`permit`, `h3_cell`, etc.
`entity_name`	`text`	Human-readable label.
`event_type`	`text`	`permit.filed`, `permit.approved`, `permit.denied`, `permit.completed`, `fire.detected`.
`event_category`	`text`	`development` or `safety_environment`.
`h3_index`	`text`	H3 r8 cell ID. Always populated (rows without `h3_index` are filtered upstream).
`magnitude`	`double precision`	Domain-specific magnitude: permit estimated cost, fire radiative power (FRP).
`confidence`	`double precision`	0.0–1.0 confidence in the event. Permit events use 0.70; FIRMS detections map `H/N/L` to `0.90/0.70/0.45`.
APRS envelope	various	`record_id`, `source_uri`, `source_system`, `schema_version`, `normalization_version`, `acl_tier`, `occurred_at`, `ingested_at`, `modified_at` (see APRS data standard).
`metadata`	`json`	Source-specific extras, e.g. `{"source":"building_permits"}`.

Current event sources

Part A ships with two unioned sources:

Source	`event_type` values	`event_category`
`building_permits`	`permit.filed`, `permit.approved`, `permit.denied`, `permit.completed`	`development`
`nasa_firms_active_fires`	`fire.detected`	`safety_environment`

Additional event sources land in Part B without changing the row shape — on_schema_change='append_new_columns' lets new columns appear without a rebuild.

Querying

mart_axiom_events is a regular Postgres table — query it with any SQL client:

-- Fire detections in the last 7 days, ranked by radiative power
select event_id, h3_index, magnitude, confidence, occurred_at
from mart_axiom_events
where event_type = 'fire.detected'
  and occurred_at > now() - interval '7 days'
order by magnitude desc
limit 50;

Join to cell_scores to pull current scoring context for any event’s cell:

select e.event_id,
       e.event_type,
       e.occurred_at,
       cs.composite_score,
       cs.confidence as score_confidence
from mart_axiom_events e
join cell_scores cs using (h3_index)
where e.event_category = 'development'
  and e.ingested_at > now() - interval '24 hours';

The `aprs_envelope` macro

Every mart row carries the APRS envelope via a single macro call, so envelope shape is identical across sources:

{{ aprs_envelope(
    source_system="'municipal_permits'",
    source_uri="'public.building_permits'",
    record_id_expr="'urn:aprs:record:locus:permit:' || coalesce(permit_id, source_id, 'unknown')",
    occurred_at_expr="coalesce(cast(issue_date as timestamp), ingested_at)",
    ingested_at_expr="ingested_at",
    modified_at_expr="modified_at",
    acl_tier="'research'"
) }}

Defaults: schema_version='aprs.locus/1.0.0', normalization_version='1.0.0', acl_tier='research'. Override per source when needed.

Local development

Install the dbt adapters into a Python environment:

pip install dbt-duckdb dbt-postgres

Then build the project against DuckDB:

cd packages/dbt-locus
dbt deps
dbt build --profiles-dir profiles --target dev

The dev target writes to /tmp/axiom_locus.duckdb. Use any DuckDB client to inspect compiled marts locally.

Targets

The project ships three profiles:

Target	Adapter	Where it writes
`dev`	`duckdb`	Local `/tmp/axiom_locus.duckdb`. Used for iterative development.
`ci`	`postgres`	Pull-request CI environment. Reads `DBT_POSTGRES_HOST`, `DBT_POSTGRES_USER`, `DBT_POSTGRES_PASSWORD`, `DBT_POSTGRES_DB`, `DBT_POSTGRES_PORT`.
`prod`	`postgres`	Locus production Postgres. Same env vars as `ci`.

Both Postgres targets materialize into the public schema by default.

Continuous integration

A GitHub Actions workflow runs dbt deps && dbt build --profiles-dir profiles --target ci on every pull request that touches the dbt project or the workflow file itself. The build executes every staging, intermediate, and mart model plus the not_null / unique schema tests defined in models/marts/schema.yml. PRs that break a model or fail a test are blocked at merge. Schema tests currently enforced on mart_axiom_events:

event_id — not_null, unique
occurred_at — not_null
h3_index — not_null

Roadmap

Three marts are planned. Part A ships mart_axiom_events; the remaining two land in Part B without changes to the existing mart:

mart_cell_score_composite — replaces the in-app composite computation in the scorer service.
mart_metro_overview — replaces the hand-rolled metro overview SQL in the web service and retires the legacy refresh_metro_overview pg_cron job.

The full retirement plan for legacy pg_cron event-source jobs runs in stages: land marts, parity-check against the existing pg_cron-derived tables, migrate downstream consumers, then disable the pg_cron jobs.

Get started

Integrations

Data standards

Project layers

Sources

`mart_axiom_events`

Configuration

Columns

Current event sources

Querying

The `aprs_envelope` macro

Local development

Targets

Continuous integration

Roadmap

​Project layers

​Sources

​mart_axiom_events

​Configuration

​Columns

​Current event sources

​Querying

​The aprs_envelope macro

​Local development

​Targets

​Continuous integration

​Roadmap

Project layers

Sources

`mart_axiom_events`

Configuration

Columns

Current event sources

Querying

The `aprs_envelope` macro

Local development

Targets

Continuous integration

Roadmap