> ## Documentation Index
> Fetch the complete documentation index at: https://docs.axiomancer.io/llms.txt
> Use this file to discover all available pages before exploring further.

# dbt marts

> How Axiom Locus uses dbt-core to build analytics marts, including the unified mart_axiom_events surface behind the dashboard heatmap and Explorer.

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.

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

## 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](/locus/data-standards). 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` |

<Warning>
  `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.
</Warning>

## `mart_axiom_events`

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

### Configuration

```sql theme={null}
{{ 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](/locus/data-standards)). |
| `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:

```sql theme={null}
-- 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:

```sql theme={null}
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:

```sql theme={null}
{{ 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:

```bash theme={null}
pip install dbt-duckdb dbt-postgres
```

Then build the project against DuckDB:

```bash theme={null}
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.