01The reporter
The report
IdentityOwn record
A photograph and a pin. Nobody types a name, because there is no field asking for one — the account behind the report carries the identity, and nothing else does.
Scene 01 — Case · Production · sold to municipalities · 2025—26
Şeffaf Şehir
Multi-tenant citizen reporting platform
A citizen photographs a broken thing, drops a pin on it and sends it. The report lands with the institution responsible for that street. The institution answers with a photograph of the repair. Then the last word goes back to the person who filed it, inside a six-hour window.
That loop is the entire product, and the hard part is not any single step in it. The hard part is that the loop has to run in public — a street can only hold its council to account for a pothole it can see has been reported — while the person who reported it stays unreadable to everyone on that street. One record, readable and unreadable at the same time, depending on who is holding it.
- NestJS
- PostgreSQL RLS
- PostGIS
- Next.js
- Multi-tenant
- Kind
- Production platform, sold to municipalities
- Tenancy
- One deployment, many municipalities, neighbourhood-level reporting
- Surfaces
- A citizen web app and an institution panel — two audiences, one record
- Repository
- seffaf-sehir — internally Şeffaf Hatay
- fewer
- more
Scene 02 — Problem
One report, four hands
The word in the name is transparency, and transparency is what makes the product work: a neighbour has to be able to see that the collapsed pavement outside the school was reported on Tuesday, that the council answered on Thursday, and what the answer looked like. Take that away and the platform is a suggestion box.
But the person who reported it lives on that street. In a small municipality the set of people who could have photographed that particular pavement from that particular angle is not large. So the record has to be public and the reporter has to be invisible — on the same object, at the same time, across four surfaces that all read from one database.
A rule like that normally lives in a comment above a query —do not select reporter_id on this surface — and it holds for exactly as long as every person who ever touches that query remembers it. Which means it holds until it does not, and the day it stops holding is not a day anyone notices.
Not collected, not built
- No national identity number, at any point in the flow
- No phone number
- No superuser account with a view across municipalities
- No reputation score, karma or ranking for citizens
Every one of those is a thing a platform like this is usually asked for. Each was left out deliberately. A field that is never stored cannot leak in a breach and cannot be exported by mistake — and a score that is never computed cannot be gamed, or used to rank neighbours against each other.
The journey of a report
02The institution
Routing
IdentityReadable here
The report lands with the institution responsible for that neighbourhood. This panel is the one surface on the platform where the reporter is a person with a name.
03The institution
Evidence
IdentityReadable here
The answer is not a status change. It is a photograph of the repair, attached to the same record, sitting next to the photograph that started it.
04The neighbourhood
The last word
IdentitySealed
The person who filed it closes it, inside a six-hour window. The closed record is what the street reads — both photographs, the verdict, and a name strip that stays sealed.
The windowSix hours. Once the institution has posted its evidence, this is the span in which the last word is given — and it belongs to the person who reported the problem, not to the institution that answered it.
- 0h
- 1h
- 2h
- 3h
- 4h
- 5h
- 6h
The visibility matrix
9 fields, 3 surfaces, 27 cells. Two of the fields are never stored at all. Of the seven that are, exactly one is identifying — and it reaches exactly one surface that did not create it. That single cell is the privacy design; everything downstream in this case exists to keep it the only one.
| Field | ReporterTheir own record | NeighbourAny other citizen on the street | InstitutionThe authority that owns the fix |
|---|---|---|---|
| Problem photograph | Own record — Reporter | Visible — Neighbour | Visible — Institution |
| Location on the map | Own record — Reporter | Visible — Neighbour | Visible — Institution |
| Responsible institution | Own record — Reporter | Visible — Neighbour | Visible — Institution |
| Report state | Own record — Reporter | Visible — Neighbour | Visible — Institution |
| Evidence photograph | Own record — Reporter | Visible — Neighbour | Visible — Institution |
| The reporter's last word | Own record — Reporter | Visible — Neighbour | Visible — Institution |
| Reporter identity | Own record — Reporter | Not visible — Neighbour | Visible — Institution |
| National identity number | Never stored — Reporter | Never stored — Neighbour | Never stored — Institution |
| Phone number | Never stored — Reporter | Never stored — Neighbour | Never stored — Institution |
- Visible on this surface
- Not visible on this surface
- The reporter's own record
- Never stored, anywhere
Scene 03 — Approach
Put the rule where forgetting is impossible
There are only a handful of places a rule like this can live, and they are not equally strong. A comment is a request. A review convention is a habit. A type is a fact the compiler checks on every build. A database policy is a fact the database checks on every query — including the ones nobody wrote in TypeScript.
So the rule was written twice, one layer apart, and each writing covers the other's blind spot.
At compile time, the citizen-facing and institution-facing queries do not share a repository. They are separate classes returning separate types, and the citizen type has no field for a reporter identity at all. There is nothing to remember to exclude, because the property does not exist on the object that surface handles. A citizen-side query that reaches for it is not a bug that ships and gets found in an audit — it is a build failure on a laptop.
At run time, tenant isolation is a PostgreSQL row-level security policy rather than a where tenant_id = … that all 93 endpoints have to remember. The policy needs to know which municipality is asking, and that is where connection pooling turns into the interesting problem: a pooled connection is not private to a request. A session setting left on it by one request is still there when the pool hands it to the next. So the connection is pinned to the request with AsyncLocalStorage — the request context follows the async call chain, and the connection whose tenant the policy reads is the one that belongs to this request and to no other.
And because a guarantee nobody can see is a guarantee nobody should trust, there is an end-to-end regression test whose only job is to attempt the forbidden thing: read a reporter's identity from a citizen-facing surface. If it ever succeeds, the build goes red.
Written where the problem is
offlineThe person filing a report is standing in front of the thing they are reporting, which is reliably the worst place for a mobile connection: under a bridge, in a basement stairwell, at the far end of a street.
Drafts are held on the device and sent when the connection returns, so the report survives the walk home. Nothing about the privacy rule changes offline, because the rule is not enforced by the client — the client never had the field in the first place.
The same rule, written twice
Compile timeTypeScript
CitizenReportRepository
returns a citizen-facing record
- photo
- location
- institution
- state
- evidence
- last word
InstitutionReportRepository
returns an institution-facing record
- photo
- location
- institution
- state
- evidence
- last word
- reporter identity
One field exists on the right and not on the left. Nothing forbids the citizen surface from selecting it; there is simply no such property to select.
Run timePostgreSQL 16
- 01Request arrivescarrying the municipality it belongs to
- 02AsyncLocalStoragethe context follows the async call chain
- 03Connection pinnedone request, one connection, one tenant setting
- 04Row-level securitythe database filters, on every query
The filter is below all 93 endpoints rather than repeated inside them, so an endpoint written next year inherits it without knowing it exists.
The stack, and what each piece is for
two apps, one contract- API
- NestJS 11 with TypeORM — 93 endpoints across both surfaces
- Database
- PostgreSQL 16 with PostGIS and row-level security — 18 tables, 35 migrations
- Client
- Next.js 16 on the App Router, React 19, Tailwind v4, MapLibre GL for the map
- Shared
- packages/shared — the DTOs, the enums and the Turkish label set both applications import, so a status name is spelled in one place and the two surfaces cannot drift apart on what a word means
- Delivery
- Docker Compose, with GitHub Actions running the suite on every push
Scene 04 — Obstacle
A compiler is not a runtime
The type boundary is the part people find clever, and on its own it would have been a nice idea that does not hold. TypeScript stops existing at the process edge. It does not check a raw SQL string, it does not check a psql session, and it does not check whatever a future maintainer writes in a one-off script at eleven at night to answer a question from a council.
That is not an argument against the type boundary. It is an argument about what each layer is actually able to promise — and about writing the same sentence twice on purpose, in two languages, because the two failures they prevent are different failures.
Where a rule can liveweakest first
01A comment above the query
Enforced byEveryone who reads it and remembers
Stops atThe first person who does not
02A code review convention
Enforced byThe reviewer who happens to know the rule
Stops atA busy week and a diff that looks reasonable
03Separate types, separate repositories
Enforced byThe compiler, on every build, without being asked
Stops atThe edge of the process — a raw SQL string is not type-checked
04A row-level security policy
Enforced byThe database, on every query, whatever wrote it
Stops atA change to the policy itself, which arrives as a reviewed migration
Only the last two hold when nobody is watching.
Scene 05 — Result
Shape, and the conditions it was tested under
This is a client product in production, so what can be shown is its shape and how its guarantees are checked — not its screens, not its data and not the municipalities behind it.
93
REST endpoints — The API surface behind both applications.
18
Tables — PostgreSQL 16 schema, PostGIS enabled.
35
Migrations — The recorded history of that schema, applied in order.
76
Test cases — Run in CI against a real PostGIS instance, not a mock.
| Measure | Value | Holds under |
|---|---|---|
| REST endpoints | 93 | The API surface behind the citizen application and the institution panel |
| Tables | 18 | PostgreSQL 16, PostGIS enabled, row-level security on tenant-scoped tables |
| Migrations | 35 | The recorded history of that schema, applied in order |
| Test cases | 76 | GitHub Actions CI, run against a real PostGIS instance — not a mock, not an in-memory stand-in |
| Privacy regression | e2e | An end-to-end test that tries to read a reporter identity from a citizen-facing surface, and fails the build if it can |
| Identifying fields collected | 0 | No national identity number and no phone number, at any point in the flow |
3.7%
22.2%
33.3%
What is not on this page
Adoption, uptime and response times are not reported here, because they were not measured under conditions I would defend. The platform is in production and it was sold to municipalities; that is a fact about the product, not a performance figure, and dressing it up as one would be exactly the kind of number this site is trying not to print.
Every figure above is countable from the repository or from a CI run. The three dials are arithmetic on the visibility matrix drawn earlier on this page, and each one carries its own denominator so it can be checked rather than believed.
Why a real PostGIS instance
A mocked database will agree with whatever the test expects. Row-level security, spatial predicates and the behaviour of a pinned connection are precisely the things a mock cannot have opinions about — and they are the things this system depends on.
The guarantee is in the database, so the tests go there too.
Scene 06 — Close
What this case is evidence of
A privacy rule the compiler enforces, not the code review.Ali Batuhan Özsürmeli
The technical content is multi-tenant architecture and database security. The transferable part is a habit: when a rule matters, ask what has to be remembered for it to hold — and then keep moving the rule down until the answer is nothing.
For a municipality buying this, that is the whole pitch. Not that the team is careful, which every team says, but that the guarantee survives the team: it is checked by a compiler on every build, by a database on every query, and by a test that deliberately attempts the thing the contract forbids.
Techniques
applied- NestJS
- PostgreSQL RLS
- PostGIS
- Next.js
- Multi-tenant
- Status
- Production · sold to municipalities
- Metric
- 93 endpoints · 76 tests on real PostGIS
- Built while
- B.Sc. Software Engineering, 09/2021 — 15 June 2026