# Municipality aggregates Anonymous "count me" opt-ins, the nightly k ≥ 20 district aggregation and the municipality dashboard (US-35, ADR-05). ## What it does [#what-it-does] A **municipality** is an Organization with a city edition (`municipality` row) and its **districts**. A home that turns on "count me" in the app sends its district and its readiness in %, nothing else: no account, no device id, no profile. The server answers a random **token**; the home sends it back to update its count or to leave, and every update **rotates** it. Only the token's SHA-256 is stored. Every night (the first cron tick at or after 01:00 UTC) the job aggregates the opt-ins of each municipality per district into an immutable **AggregateSnapshot**: only districts with **at least 20 homes**, readiness and each area as a mean **rounded to a whole point**, the home count rounded down to tens. Opt-ins not refreshed for 90 days no longer count. Members with the Org role `municipality` see the latest snapshot at `/municipality` (D-Kommune), sortable by lowest readiness or A–Z. C7 ("never a district below 20") holds three times: the aggregation (`aggregateDistricts` in `@varu/preparedness/aggregates`), a check constraint on `aggregate_snapshot`, and the dashboard query. ## Procedures [#procedures] | Procedure | Who | Failures | | --------------------------------------------------------- | ------------------------------------------- | -------------------------------------------------------------- | | `municipality.list` · `GET /api/municipality` | anyone, no account | — | | `municipality.optIn` · `POST /api/municipality/optin` | anyone, no account | `NOT_FOUND` (unknown district or token), `BAD_REQUEST` (shape) | | `municipality.optOut` · `DELETE /api/municipality/optin` | anyone holding the token | — (idempotent) | | `municipality.dashboard` | `municipality` in the Active Organization | `FORBIDDEN`, `NOT_FOUND` (Organization is no municipality) | The opt-in body is `@varu/preparedness` `AggregatePayload` (`district`, `readinessPercent`, `areaPercent`) plus an optional `token`; any other field is refused. ## Data and isolation [#data-and-isolation] | Table | Tenant access | | --------------------- | ----------------------------------------------------------------------------------------------------------------- | | `municipality` | select for everyone (the app's picker) | | `district` | select for everyone | | `municipality_opt_in` | **none**: no grant, no policy. Only the system path (`municipality.opt-in` reason, the nightly job) touches it | | `aggregate_snapshot` | select own Organization's rows; no insert, update or delete grant; a trigger refuses UPDATE and TRUNCATE for all | | `aggregate_run` | none; one row per municipality and night, written with the snapshots, makes the job idempotent | Migrations: `20261106203005_municipality` (tables, policies) and `20261106203010_municipality_grants_immutable` (grants, immutability trigger). Snapshots go only with their Organization (`ON DELETE CASCADE`). ## Kat-Leuchttürme, radio and campaigns (US-36) [#kat-leuchttürme-radio-and-campaigns-us-36] What the municipality publishes for the app, edited on D-Kommune by the `municipality` role (`municipality.outreach.*` over `/api/rpc`): - **Light points** (`light_point`): draft → published; a published point shows in the plan of its district's homes. - **Radio** (`radio_station`): the whole station list is replaced at once (FM band only). - **Campaigns** (`campaign`): a kind and `send_at`. A moment already past sends at once. A scheduled campaign may be cancelled; a sent one is immutable: the trigger `campaign_immutable` refuses UPDATE and DELETE for every role (only the Organization's cascade removes it), and app_tenant has no UPDATE grant. The app reads them anonymously by municipality, as plain GETs with no body: `GET /api/m/{municipality}/lightpoints`, `/radio` and `/campaigns/active` (sent in the last 14 days). It picks its district on the device and filters there, so no request names a home or a district, and no table has a recipient (ADR-05). The public outputs select their fields by name: a point is `id, districtId, name, address`; a campaign `id, kind, sendAt`; a station `name, note, mhz`. | Table | app_tenant | | --------------- | -------------------------------------------------------------------------------------------- | | `light_point` | select published rows or own drafts; insert, delete and update listed columns of own rows | | `radio_station` | select for everyone; insert and delete own rows | | `campaign` | select sent rows or own; insert own; delete own only before `send_at`; no update | Migrations: `20261106213944_outreach` and `20261106213945_outreach_grants_immutable`. ## Reports and city settings (US-38) [#reports-and-city-settings-us-38] Two more pages for the `municipality` role, both aggregate-only: - **D-Reports** (`/municipality/reports`, `?range=6|12`): the readiness curve, the five weakest areas, "what the municipality cannot see", and the export. `municipality.report` returns one point per calendar month (the latest night of the month): the homes-weighted mean readiness over the districts shown that night, so a district under 20 homes can never be in the curve. Sent campaigns are drawn as dashed lines at their month. The export is built in the browser from what the page already holds: `reportCsv` writes only `snapshot_date, district, homes, readiness_percent` and the eight `_percent` columns from an allow-list (a district under 20 is dropped again, a leading `=`, `+`, `-` or `@` is defused), `reportPdf` a text-only A4 report with the same table. - **D-Settings** (`/municipality/settings`): three switches the app follows (`show_branding`, `show_light_points`, `show_radio` on `municipality`; with a switch off, `/m/{municipality}/lightpoints` or `/radio` answers an empty list, and `municipality.list` carries `showBranding`), the team (Members of the Organization with their strongest role) and the privacy rules. "At least 20 homes per district" and "round to 1 %" are shown on and **locked**: they are product rules (C7), not settings. The DPA (AVV, Art. 28) status reads `municipality.dpa_signed_at`, written by Varu's staff when the signed contract arrives; the tenant has no UPDATE grant on that column. | Procedure | Who | Notes | | ------------------------------ | ----------------------------------------- | --------------------------------------------- | | `municipality.report` | `municipality` in the Active Organization | the last 12 months | | `municipality.settings` | same | switches, `dpaSignedAt`, `team` | | `municipality.saveSettings` | same, audited (`municipality.settings_saved`) | the three switches only | Migrations: `20261106215122_municipality_settings` (columns, UPDATE policy) and `20261106215125_municipality_settings_grants` (UPDATE on the three switch columns only). The seed adds eleven earlier monthly snapshots so the curve has data. ## Production config [#production-config] `scripts/check-production-config.mjs` (deploy step) also enforces ADR-0017: the Worker's placement region is in the EU, every R2 bucket and `STORAGE_ENDPOINT` use the EU jurisdiction, and `DATABASE_URL` is a Neon host in `eu-central-1`. ## Key files [#key-files] - [`packages/preparedness/src/aggregates.ts`](https://github.com/CarlosZiegler/varu/blob/main/packages/preparedness/src/aggregates.ts) (k ≥ 20 and rounding) - [`packages/api/src/features/municipality/municipality.service.ts`](https://github.com/CarlosZiegler/varu/blob/main/packages/api/src/features/municipality/municipality.service.ts) - [`packages/api/src/features/municipality/municipality.jobs.ts`](https://github.com/CarlosZiegler/varu/blob/main/packages/api/src/features/municipality/municipality.jobs.ts) (nightly job) - [`packages/db/src/schema/municipality.ts`](https://github.com/CarlosZiegler/varu/blob/main/packages/db/src/schema/municipality.ts) - [`apps/web/src/features/municipality/municipality.page.tsx`](https://github.com/CarlosZiegler/varu/blob/main/apps/web/src/features/municipality/municipality.page.tsx) (D-Kommune, `/municipality`) - [`packages/api/src/features/municipality/municipality.outreach.ts`](https://github.com/CarlosZiegler/varu/blob/main/packages/api/src/features/municipality/municipality.outreach.ts) (light points, radio, campaigns) - [`apps/web/src/routes/api/m/$.ts`](https://github.com/CarlosZiegler/varu/blob/main/apps/web/src/routes/api/m/$.ts) (public `/api/m/...`) - [`apps/mobile/src/features/plan/plan.city.ts`](https://github.com/CarlosZiegler/varu/blob/main/apps/mobile/src/features/plan/plan.city.ts) (city edition in the plan) - [`packages/api/src/features/municipality/municipality.settings.ts`](https://github.com/CarlosZiegler/varu/blob/main/packages/api/src/features/municipality/municipality.settings.ts) (report, settings, team) and [`municipality.report.ts`](https://github.com/CarlosZiegler/varu/blob/main/packages/api/src/features/municipality/municipality.report.ts) (monthly series) - [`apps/web/src/features/municipality/municipality.reports-page.tsx`](https://github.com/CarlosZiegler/varu/blob/main/apps/web/src/features/municipality/municipality.reports-page.tsx) (D-Reports), [`municipality.report-csv.ts`](https://github.com/CarlosZiegler/varu/blob/main/apps/web/src/features/municipality/municipality.report-csv.ts), [`municipality.report-pdf.ts`](https://github.com/CarlosZiegler/varu/blob/main/apps/web/src/features/municipality/municipality.report-pdf.ts) - [`apps/web/src/features/municipality/municipality.settings-page.tsx`](https://github.com/CarlosZiegler/varu/blob/main/apps/web/src/features/municipality/municipality.settings-page.tsx) (D-Settings) - [`apps/web/src/routes/api/municipality/$.ts`](https://github.com/CarlosZiegler/varu/blob/main/apps/web/src/routes/api/municipality/$.ts) (plain HTTP)