← Francisco Cascalheira

Case study · Feb 2026 — present

In production · API responding · 02:16 UTC

opPORTOnities

Câmara Municipal do Porto places young people in summer internships through software one student built alone.

Client
Câmara Municipal do Porto
Programme
Summer-internship programme, ages 18–21
Role
Sole developer — requirements to production
Stack
TypeScript · Express · Prisma · PostgreSQL · React (Vite) · Zod · Azure Blob · Railway
Public record

What this document claims.

opPORTOnities is the recruitment platform behind the city's summer-internship programme: candidates aged 18–21 register, approved companies post vacancies, and applications move through a supervised pipeline to confirmed placements. I sat in the requirements meetings, designed the data model, wrote every line, and deployed it. I am the only engineer who has ever committed to this codebase.

294
commits, all mine
git shortlog -sn master: one author
99
internship positions
count(*) from vacancies — one row per slot · 16 Jul 2026
Ata n.º 3 — entidades, 20 Apr 2026
12
relational models
3
portals: candidate · company · admin

What the city needed.

Every summer, Porto's city council places hundreds of young residents and students in paid internships at local companies. Before this platform, that meant forms, spreadsheets and email threads: candidates mailing documents, staff cross-checking eligibility by hand, companies chasing the council for candidate lists.

The council needed a real system: one place where candidates register and prove eligibility, companies apply and get vetted, vacancies get published, and every application moves through a controlled pipeline that municipal staff supervise — with the reporting a public institution is accountable for.

The rules of the job.

One developer

No team to review my architecture. Every decision — schema, auth, deployment — was mine to get right, and mine to fix at 2 a.m. when it wasn't.

Municipal stakeholders

Requirements arrived in meetings with council staff, in Portuguese, shaped by administrative law and programme rules — an age window, residence criteria, vetting duties. The spec was a conversation, not a document.

A real deadline

The programme has a calendar. Registrations open on an announced date whether the software is ready or not.

Other people's sensitive data

Eighteen-to-twenty-one-year-olds submitting IDs, school records and addresses. GDPR consent, honour declarations, audit trails and strict company-side visibility rules are load-bearing features, not compliance theatre.

Twelve models, one machine.

The whole platform stands on twelve Prisma models over PostgreSQL. This is the real schema, sanitised to shapes — click a model, or walk it with the arrow keys.

Application

Matching

The core object: one candidate applying to one vacancy, exactly once.

Shape

  • status machine (see fig. 2)
  • timestamps per transition: applied, interest, selected, rejected
  • selectionExpiresAt — selections lapse
  • company + admin notes
  • UNIQUE (candidate, vacancy)

Relations

  • · belongs to
  • · belongs to
  • · 1—1 optional

Why it's shaped this way — The unique constraint is the referee: the database — not application code — guarantees a candidate can't apply twice to the same vacancy, no matter what races the UI produces.

fig. 1 — 12 relational models · one developer · in production for Câmara Municipal do Porto

An application is the contested object: companies compete for candidates, candidates weigh offers, and the council supervises all of it. Its lifecycle is an explicit state machine — every transition timestamped, every selection given a deadline.

  1. 01 PENDING

    Candidate applied. Visible to the company under the platform's visibility rules.

  2. 02 COMPANY_INTERESTED

    The company flags interest. Timestamped; the candidate is notified.

  3. 03 SELECTED

    A selection with a deadline — selectionExpiresAt. Unanswered selections lapse instead of blocking the candidate forever.

  4. 04 CONFIRMED

    Candidate accepts. A Match record is created — the placement now exists independently of the pipeline.

Terminal states

  • REJECTED · by the company
  • WITHDRAWN · by the candidate
  • DECLINED · candidate turns down a selection
fig. 2 — the application state machine, as deployed. Each transition is timestamped; selections expire.

What held.

4.1Create the account last

The first registration wizard created the user account at step one. Real users abandoned mid-wizard, and the half-registered accounts leaked into admin dashboards and Excel exports until staff asked why the numbers looked wrong. I rewrote the flow to defer account creation to the final submit, added error recovery for the failure cases uploads produce, and filtered the legacy orphans out of every report. Transactional boundaries belong at the end of a flow, not the beginning.

commit: “refactor(register): defer account creation to final submit”

4.2Let the database referee

Selection is contested: companies compete for the same candidates, and a candidate can be selected by one company while another is deciding. Application code can't be trusted to serialise the world, so the guarantees live in Postgres — a unique constraint on (candidate, vacancy), a 1—1 constraint between Match and its winning application, and selection deadlines that lapse automatically. Then I wrote race-condition tests against the running API to prove it holds.

commit: “Harden matching flows with race-condition coverage”

4.3Authorisation lives in one place

What a company may see about a candidate is a policy question with legal weight. Early on, those rules were re-implemented per endpoint — until two screens disagreed about how many applications a company had. I centralised visibility into one module every route consults, and put the master switch (whether companies can access candidate data at all) in the platform settings the council controls. When counts disagree, users stop trusting the numbers; when authorisation is scattered, you can't even say what the rule is.

commit: “Centralize company application visibility rules”

4.4Excel is a production surface

The council runs on Excel, so exports aren't a convenience feature — they're how the programme is administered and reported upward. I hit Excel's own cell-size limits, blob-storage URL generation failing mid-export, and downloads breaking across browsers. Each fix taught the same lesson: the file a stakeholder opens is as much “the product” as any screen, and it gets the same testing, logging and error handling.

commit: “Fix admin report export Excel cell limits”

What broke.

Before the largest release I audited the whole platform myself, because nobody else was going to. It landed as one commit. Here is what was actually in it — recounted from the diff, including where the recount disagrees with what I wrote at the time.

cc0899d · 1 April 2026 · 34 files · +628 −193

Recounted from the diff

UI state & correctness
14

Dashboards reporting a proxy metric instead of the real one; toggles that failed open while loading.

Error handling
12

An unhandled notification insert could turn a successful selection into a 500.

Data integrity & state machine
11

Closing a vacancy left its live applications hanging; cancelling a match left the application selected.

Leaks & hygiene
10

Blob URLs never revoked; debug logging that printed the head of the auth token.

Auth, session & credentials
9

Reset tokens stored in the clear; JWTs accepted without the claims the routes rely on.

Query correctness & pagination
6

Admin lists fetched every row, sorted in JavaScript, then sliced the page in memory.

Race conditions & transactions
5

Read-then-write on apply and on select — no transaction, no constraint to catch the loser.

Output encoding
1

Names and free text interpolated raw into transactional email; one escape helper, 26 call sites.

File upload
1

One filter for four fields meant a PDF could be uploaded as a logo and rendered as an image.

Privacy & tenant scoping
1

The sharpest one in the commit — see exhibit 3.2.

Reconciliation

counted 70 · claimed 91

Recounting the diff at one row per independent decision gives 70 fix sites. Count every repeated call site — 26 escaped interpolations, 5 leaked object URLs, 8 stripped debug logs — and the total passes 90; collapse the compound ones and it falls near 55. The commit's 91 sits inside that band, which makes it a good-faith count rather than a reproducible one. Roughly a tenth of the entries are hardening rather than defects: server-side pagination and a change-password endpoint are not bugs. And the four passes are not recorded anywhere in the tree — I remember doing them; the repository cannot confirm it.

fig. 3 — one commit, recounted · the pre-fix states below shipped fixed and are history, not advisories

3.1Reset tokens were stored in the clear

WasA password reset wrote 32 random bytes straight into the users table. Anyone who could read that table — a backup, a dump, an over-broad admin query — could take over any account without ever seeing an email.

NowOnly the SHA-256 hash is stored; the raw token exists solely in the emailed link, and the reset route hashes what it receives before looking anything up.

3.2One company could read another's vacancies

WasThe route that returns a vacancy by id guarded unpublished ones against candidates, and only candidates. Any authenticated company could walk the ids and read a competitor's drafts.

NowThe vacancy's owner is checked against the requesting company. The rule the endpoint always meant to enforce is now the rule it enforces.

3.3A cancelled match poisoned the vacancy forever

WasA unique constraint on the match's vacancy meant a vacancy could hold exactly one match for all time. Cancel a placement and re-match that vacancy and the database refused — permanently, and only in the flow nobody rehearses.

NowThe constraint is dropped, the relation is a collection, and the four call sites that assumed a single match were rewritten to match. This one needed a schema change and a migration: the bug was in the model, not the code.

3.4Confirming a placement was neither atomic nor exhaustive

WasThe confirm re-read nothing inside its transaction, so two concurrent confirms could both pass the outer check; it left other companies' selections live, so a candidate could be placed twice; and it left the losing applicants on the vacancy waiting in pending.

NowOne transaction re-checks the candidate's state, rejects the competing selections, and closes out the other applicants — and, on the race itself, it does not work. The re-check is a check-then-act: both transactions read the candidate before either writes, both are told ACTIVE, and both place. It closed the window where someone confirms twice in sequence and left open the one where two confirms arrive together, which was the bug. The guard that holds is a compare-and-set, and it arrived three weeks later. fig. 4 runs all three against a real Postgres.

Exhibit 3.4 says a candidate could be placed twice. Rather than ask you to take that on trust, here it is: a real Postgres, compiled to WebAssembly and running in this page, with the real schema and the real guards from three points in the repository's history. Move the lever, fire both confirmations into the same instant, and watch which ones the database is able to refuse.

Guard

c0e3e38 · the original confirm

The status check sits outside the transaction, and nothing re-checks it inside.

the race windowAlfa, Lda.Beta, S.A.y_01.statusACTIVEBLOCKEDreadSELECTEDreadSELECTEDbeginbeginconfirmmatchblockrejectcommitmatch1 rowblockcommitBeta never touches the track — nothing here ever asks whether the candidate is already taken

scroll the diagram sideways →

key — a hollow mark asks a question · a filled mark writes · the tall bar is T1's commit, which is the flip · a line dropping to y_01.status means that step asked the row what it currently says

matches

two placements

idapplication_idyoung_idvacancy_idnote
m_aapp_ay_01vac_a
m_bapp_by_01vac_b← the same candidate, placed again

Both confirms read SELECTED before either wrote, and nothing in the transaction looked again. Each wrote its own Match. The candidate is placed at two companies on the same day, and the second write is not an error — the database was never asked a question it could refuse.

Loads a real 5.6 MB Postgres, compiled to WebAssembly — only if you ask.

The three guards

no guard
c0e3e38
two placements
re-read inside the transaction
cc0899d
two placements
compare-and-set
61c423e
one placement, one 409

Method

PGlite is a single-connection Postgres, so there is no second client to race against. The interleave is produced with two-phase commit: T1 does its work and PREPAREs — holding its locks, uncommitted — while T2 reads the pre-T1 world, which is the race window itself; T2's writes then land after T1 commits, exactly where a real lock-wait would have released them. Under READ COMMITTED each statement takes its own snapshot, so this schedule is indistinguishable, from T2's side, from two genuinely concurrent clients. The MVCC, the constraints and the row counts are Postgres's own.

fig. 4 — one candidate, two companies, the same instant · real PostgreSQL 18 (PGlite) in your browser, not the production database · the real schema sanitised to shapes · pre-fix guards are history, not advisories

Where it stands today.

The platform is in production, serving candidates, companies and municipal staff through three portals. It holds 99 internship positions from 60 companies and 310 applications, and 62 placements have been confirmed on it — 60 of them still standing. Before each release I run a smoke-test suite against the live API; before the biggest one I audited the whole platform and fixed everything I found, because nobody else was going to. That audit is fig. 3, recounted from its own diff.

Those figures have a second source, which is the only reason to believe them. The council's jury received 60 entity applications and admitted 55, offering 90 positions; the five it excluded account for the nine-position difference, and it names them. Its final allocation, signed on 26 May, calls the placements the result of “o processo de matching realizado em plataforma digital própria do Programa” and fills the 60 places the executive approved on 17 March. The database says 60 active placements. Neither document mentions me — they are the council's account of its own programme, and they were written without reference to this page.

The stack is deliberately boring: Express, Prisma, PostgreSQL, a React SPA, and a shared types package so the API contract is one set of Zod schemas consumed by both sides. Boring bought me speed as a solo developer — every hour spent fighting a clever framework is an hour the council doesn't get.

Production API responding at time of render · checked 02:16 UTC · refreshed every 5 minutes

End of case study

francisco.cascalheira2006@gmail.com
← Back to the index