# Data mart schema (v1)

The mart is the consumer-facing layer: clean tables about Cluj-Napoca local council meetings and the permits that relate to them. It is rebuilt from `data/raw/` by `npm run mart`, a pure function: the same raw data always produces byte-identical tables.

- **JSON**: `data/mart/<table>.json`, one sorted array per table, committed to git.
- **SQLite**: `npm run mart -- --sqlite` writes `data/mart.sqlite` with the same tables and indexes. Arrays and objects are stored as JSON text, booleans as 0/1.
- `data/mart/manifest.json` holds the schema version, the years and row counts.
- **Years**: the mart holds every year in `data/raw/`. Each record table has a `year` column and `years` lists them, so "only 2025" is `where year = 2025` and "across years" is no filter at all. Ids carry no implicit year assumption: links and citations work across years (a 2026 decision citing `HCL 596/2025` links to it once 2025 is in).

TypeScript types live in `mart/schema.ts`. Consumers should depend only on that file and on these tables.

## Record ids

Every record has a global id `<table>:<key>`, used by `identifiers` and `links`:

| table | id example |
|---|---|
| meetings | `meeting:sedinta-ordinara-din-1-octombrie-2026` |
| agenda_items | `agenda_item:553729` |
| decisions | `decision:652/2026` |
| permits | `permit:AC/629/2026`, `permit:CU/1782/2026` |

## Tables

### `years`
| column | |
|---|---|
| `year` | key |
| `meetings`, `agenda_items`, `decisions`, `permits` | rows of that year in each table (`permits` counts AC, AD and CU) |
| `first_date`, `last_date` | earliest and latest meeting or issue date in the year. `last_date` can be a convened meeting in the future |

### `categories`
| column | |
|---|---|
| `category` | key, e.g. `urbanism` |
| `label` | Romanian label |
| `sort_order` | rule priority. The first matching rule is an item's category |

### `meetings`
| column | |
|---|---|
| `meeting_id` | slug on the city site |
| `year` | of `date` |
| `date` | `YYYY-MM-DD` |
| `kind` | `ordinara`, `extraordinara`, `de-indata`, `constituire`, `other` |
| `title`, `url` | |
| `minutes_url` | minutes PDF ("minută" / "proces-verbal"), when published |
| `documents` | all document links on the meeting page `[{label, url}]` |
| `has_video` | |
| `is_listed` | `false` while the meeting is only known from its agenda items (upcoming, not yet on the list page) |
| `agenda_item_count`, `decision_count` | |

### `agenda_items`
Draft decisions ("proiecte de hotărâre") on a meeting's agenda.

| column | |
|---|---|
| `agenda_item_id` | WordPress post id |
| `meeting_id` | → `meetings` |
| `year` | the meeting's year (items for January meetings are published in December) |
| `position` | number on the agenda |
| `title`, `description` | |
| `category`, `tags` | → `categories`. `tags` lists every matching category |
| `decision_id` | → `decisions`: what it was adopted as. `null` if not voted (reports), rejected, withdrawn, or the meeting hasn't happened yet |
| `url`, `pdf_url` | the PDF is a scan |
| `published_at`, `modified_at` | |

### `decisions`
Adopted decisions ("hotărâri"), with full text.

| column | |
|---|---|
| `decision_id` | `<number>/<year>` |
| `number`, `year` | `year` is the one in the number |
| `meeting_id` | → `meetings` |
| `meeting_date` | |
| `agenda_item_id` | → `agenda_items`: the item it came from (matched by text similarity within the meeting) |
| `title`, `summary`, `text` | `text` is the full decision |
| `votes`, `votes_text` | e.g. `24`, "Hotărârea a fost adoptată cu 24 voturi" |
| `category`, `tags` | |
| `url`, `pdf_url`, `published_at` | |

### `permits`
Construction (`AC`) and demolition (`AD`) permits and urbanism certificates (`CU`).

| column | |
|---|---|
| `permit_id` | `<kind>/<number>/<year>` |
| `kind`, `number`, `year`, `issued_at` | `year` is the one in the number |
| `works` | what is authorised |
| `address` | as written by the city |
| `value_lei` | declared investment value |
| `request_number`, `request_date` | |
| `basis` | the project the permit is based on (PAC/PAD) |
| `council_refs` | CU only: the council decisions it relies on, as written |
| `url` | |

### `identifiers`
Long table: one row per identifier found in a record (description, full text or permit fields).

| column | |
|---|---|
| `record_id` | any record id |
| `kind` | `cadastral`, `land_book` (CF), `topo`, `street`, `address`, `decision_ref`, `certificate_ref`, `permit_ref`, `beneficiary` |
| `value` | normalized for joining: `356691`, `tractoristilor`, `tractoristilor\|7h`, `596/2025` |
| `label` | as displayed: `str. Tractoriștilor nr. 7H`, `HCL 596/2025` |

Cadastral numbers drop building and unit suffixes (`263814-C1` → `263814`). `*_ref` values may point outside the mart's years.

### `links`
Cross-references between records. There is one row per pair and shared identifier.

| column | |
|---|---|
| `from_id`, `to_id` | record ids |
| `basis` | `cites` (directed: `from_id` mentions `to_id`), `same_parcel`, `same_land_book`, `same_address` (undirected, stored once with `from_id < to_id`) |
| `value`, `label` | the shared identifier |
| `strength` | `strong`, or `medium` for address-only matches |

An agenda item and its decision are joined by the foreign keys above, not by `links`. A shared key that appears in too many records (more than 25, or more than 6 council items, e.g. a block of flats sold apartment by apartment) produces no links.

### `geo_streets`
Every named street in Cluj-Napoca from OpenStreetMap (© OpenStreetMap contributors, ODbL), with its geometry. One row per line in the JSON file.

| column | |
|---|---|
| `name` | OSM name, unique: `Strada Fagului` |
| `street_key` | same normalization as `identifiers.value` for `kind = 'street'`: `fagului` |
| `lat`, `lng` | a point on the street near its middle |
| `lines` | `[[[lat, lng], …], …]`, one polyline per OSM way, simplified to ~8 m; empty for streets known only from address points |

### `locations`
Where records are: one row per street a record names, at the best precision found. One row per line in the JSON file.

| column | |
|---|---|
| `record_id` | any record id |
| `street_key` | → `identifiers.value` (`kind = 'street'`) |
| `street_name` | → `geo_streets.name` |
| `number` | house number as written, `null` for whole-street rows |
| `lat`, `lng` | |
| `precision` | `address`: the number is an OSM address point · `nearby`: placed from neighbouring numbers on the same side of the street (`17` between `15` and `21`, `5A` at `5`) · `street`: no usable number, the point is the street's middle and the whole street is meant |

Street names are matched exactly (ignoring the street type), then through abbreviations and initials (`O. Goga` = `Octavian Goga`). Records without a street (only a cadastral number, say) have no location.

## Example queries

```sql
-- Everything connected to construction permit AC 279/2026, with the meeting
select l.basis, l.label, m.title
from links l
join decisions d on 'decision:' || d.decision_id in (l.from_id, l.to_id)
join meetings m on m.meeting_id = d.meeting_id
where 'permit:AC/279/2026' in (l.from_id, l.to_id);

-- Every record that mentions parcel 357828
select record_id, label from identifiers where kind = 'cadastral' and value = '357828';

-- Council activity on a street
select i.record_id, i.label from identifiers i where i.kind = 'street' and i.value = 'tractoristilor';

-- Everything placed within ~300 m of a point (a rough box; use a real distance in code)
select record_id, street_name, number, precision from locations
where lat between 46.7520 and 46.7547 and lng between 23.6083 and 23.6161;

-- Agenda items per category and how many were adopted, per year
select year, category, count(*), sum(decision_id is not null) from agenda_items group by year, category;

-- Which years are in, and how complete each looks
select * from years order by year;
```