Municipality aggregates

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

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

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)

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)

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 <area>_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

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

packages/preparedness/src/aggregates.ts (k ≥ 20 and rounding)
packages/api/src/features/municipality/municipality.service.ts
packages/api/src/features/municipality/municipality.jobs.ts (nightly job)
packages/db/src/schema/municipality.ts
apps/web/src/features/municipality/municipality.page.tsx (D-Kommune, /municipality)
packages/api/src/features/municipality/municipality.outreach.ts (light points, radio, campaigns)
apps/web/src/routes/api/m/$.ts (public /api/m/...)
apps/mobile/src/features/plan/plan.city.ts (city edition in the plan)
packages/api/src/features/municipality/municipality.settings.ts (report, settings, team) and municipality.report.ts (monthly series)
apps/web/src/features/municipality/municipality.reports-page.tsx (D-Reports), municipality.report-csv.ts, municipality.report-pdf.ts
apps/web/src/features/municipality/municipality.settings-page.tsx (D-Settings)
apps/web/src/routes/api/municipality/$.ts (plain HTTP)