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'. |
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 |
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;
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'"
) }}
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
cd packages/dbt-locus
dbt deps
dbt build --profiles-dir profiles --target dev
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. |
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, uniqueoccurred_at — not_nullh3_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. 
