[13]
GZM METROPOLIS — MUNICIPAL VOTING APP ON 3D MAP
▓ TIER B · YEAR 2026 · STATUS: LIVE · LANGUAGES: TYPESCRIPT / PYTHON / SQL
Geospatial data and a 3D browser map, with race-proof voting
[FIG. 1] MISSION
I work with geospatial data and build 3D map interfaces on top of it (MapLibre GL + Three.js) with real interaction, not decorative animation. That is how the civic-budget map app for the GZM Metropolis came about, a BLOXHUB hackathon project awarded an honorable mention and built by a four-person team: the core was laid by Mikołaj Paweł Sapek (mikolajpawelsapek.eu), and alongside me worked Maciej Sachse (maciejsachse.com) and Adam Bączek. An interactive 3D map shows projects from 14 cities, district boundaries, bike routes and POIs, and a resident browses projects, votes, and submits proposals with photos and 3D models. Data flows from open sources (GZM CKAN, Gliwice Open Data, GUGiK, OSM) and syncs daily via cron — I write the ETL on the pure standard library, idempotently, with not a single pip dependency, because zero baggage was the point. I design voting as a concurrent system: one vote per person, protection against races and server-side validation, and underneath 38 SQL migrations with RLS, triggers and hardening, including a formal security audit with P0–P2 findings. Login works through PLZ: a QR code scanned in the mobile app, with a hand-built bridge to Supabase Auth, because PLZ offers no OAuth. In a team I work with discipline: CLAUDE.md is the project's constitution and we quote its rules in code comments, and CI/CD in GitHub Actions with Playwright e2e tests closes the whole thing.
[FIG. 2] ARCHITECTURE
hover a block to see its description
[FIG. 3] CHALLENGES
[+][CH-01]
PLZ has no OAuth or OIDC — login is a QR code scanned in a mobile app, and the callback returns its own xsessionId, not our session's identifier. The three-way browser ↔ PLZ ↔ Supabase handshake had to be built by hand, including a token-type trap: generateLink for an unconfirmed account returns a signup-type token that the frontend rejects with a 403 — the fix is confirming the e-mail via the Admin API and generating the link a second time, now as a magiclink. Replay was a separate problem class: the PLZ callback can arrive twice, and naively writing the token re-armed a consumed session. A conditional UPDATE on status='pending' makes the operation atomic — the second callback hits zero rows, and the sessionId never travels in a URL, only in an HttpOnly cookie.
[+][CH-02]
Open government data fought us at every layer of the stack. A WAF in front of CKAN reset connections from the GitHub Actions runner with a Python User-Agent, while Supabase simultaneously rejected the server key when a request looked 'browser-like' — two systems with contradictory demands on the same header, so the sync picks its User-Agent per target. Further down: some CSVs arrive in Windows-1250 (decoding as UTF-8 destroys every Polish diacritic — hence a strict-UTF-8-then-cp1250 fallback), Gliwice has its own column schema different from the GZM standard, Ruda Śląska's monuments dataset has swapped coordinate axes, and duplicate keys crashed the upsert with PG 21000 — pre-write deduplication keeps the last occurrence. The whole thing is idempotent and runs daily with retry and backoff, on Python's standard library alone.
[+][CH-03]
The audit's most dangerous finding sat in a nuance of Postgres's role model: the guard protecting the PostGIS catalog checked session_user, but PostgREST connects as the authenticator role and does SET ROLE — session_user stays authenticator, so the guard never blocked and an anonymous user could corrupt the SRID catalog, disabling all of PostGIS. On top of that, RLS doesn't protect individual columns: a project's owner could overwrite system fields like vote_count or status, because policies only checked created_by. The remediation migration moved to SECURITY INVOKER triggers using current_user, REVOKEd write grants and forced system fields for public-API roles — and earlier, another trigger protecting manually spread project coordinates turned out to be a 'silent rule' that never fired, rewritten from a flag check into a geometric comparison.
[FIG. 4] AI LAYER
No model runs at runtime — it's a classic geodata stack with no LLM layer. AI worked on the build side: CLAUDE.md served as the project constitution, its rules cited in code comments (the ref+state pattern, the cancelled flag, a ban on double casts), the security-migration spec was written directly for the agent with acceptance criteria, and a code audit listed P0–P2 findings with file:line links and verdicts.
[FIG. 2A] THE CITY AT EVERY HOUR
BOTTOM = BEACH (PEAK 1 PM) · MIDDLE = CENTRE (PEAK 8 PM)
GRACEFUL DEGRADATION — CLICK TO CUT A SOURCE
→ /api/health: heatmapa: real · eventy: real · miejsca: real — the same Pydantic contract either way
[FIG. 5] GALLERY
