WayClub

Cross-tenant disclosure is the most serious risk in a shared database: one endpoint that forgets a WHERE clause would show one club’s members or proposals to another. The tenant also could not simply be the university, because a club must be able to use WayClub without its institution being a customer. And connections are pooled, so identity set for one request must never survive into the next.

The tenant root is the account. Every tenant-scoped row carries account_id, which is never null, and institution_id, which is null for a standalone club. Each request opens one transaction as a role that owns nothing and holds no RLS bypass, then runs select set_config('request.jwt.claims', $1, true). The third argument makes the setting transaction-local, so it resets at COMMIT or ROLLBACK and cannot leak across pooled connections. The auth guard resolves those claims from the database rather than copying them from the token. Policies read the account through app.current_account_id(), so a request without a tenant reads nothing. A composite foreign key binds each row’s (account_id, institution_id) pair to the institution’s own account. Because that is a constraint, not a policy, it applies to the table owner too.

• Filter by tenant in every query in application code, which fails as soon as one query forgets
• A separate schema or database per tenant, which makes cross-club features and migrations heavier
• Row-level security in PostgreSQL, keyed on a tenant id that each request sets inside its own transaction

Every query pays for the policy checks, and a missing policy fails closed, so an unfinished feature denies access instead of degrading gracefully. RLS is deliberately not forced on the table owner, because forcing it broke the SECURITY DEFINER helpers the policies rely on. The boundary rests on the request role having no ownership and no bypass, so tests read the catalogue to check that this still holds.

API tests show that a request for another tenant’s record returns 404, the same response as a record that never existed. A neighbouring test shows a same-tenant outsider gets 403. Database tests run against real PostgreSQL in CI.

A proposal is edited by several committee members and moved through states by reviewers, often at the same time. Two people saving from stale copies would silently overwrite each other’s changes, and two state transitions could both succeed from the same starting state. Registration has the same problem: two students taking the last place at once must not both get it.

Mutable records carry a version column. Every write sends the version the client last read, and the API updates with WHERE id = $1 AND version = $2, incrementing the version in the same statement. If no row matches, the API re-reads the record and returns HTTP 409 with the code version_conflict. In the proposal form, a conflict shows a banner that lists the fields that differ, theirs and mine, with an option to load their version. Registration takes a per-event transaction advisory lock (pg_advisory_xact_lock) before checking capacity, and cancellation and waitlist promotion take the same lock, so the check and the insert cannot interleave.

• Last write wins, which loses work without anyone noticing
• Pessimistic locks held while a person edits, which do not survive the gaps between HTTP requests
• A version column checked on every write, with a lock only around the capacity check

Clients have to carry versions and handle a conflict path. The registration lock serialises sign-ups for a single event, which is acceptable at club scale but has not been load tested.

A database test shows that an update with a stale version affects zero rows while the correct version succeeds. An API test sends a lifecycle transition with an old version and expects 409 version_conflict. A web test checks that the conflict banner shows both versions. Registration tests cover waitlisting at capacity and transactional promotion on cancellation.

Approvals only matter if nobody can later rewrite who approved what. Blocking edits in application code is not enough, because anyone with database access could still edit a row. The first hash-chain design also had a concurrency bug that a test suite running one transaction at a time could never reveal.

Triggers write audit rows for key changes such as role assignments, workflow stages, approvals and event status, so a code path cannot forget them. No role has UPDATE or DELETE on audit_event, and a BEFORE UPDATE OR DELETE trigger raises an exception even for the owner, so changing history needs a schema change rather than a quiet edit. Each row stores a SHA-256 hash of the previous row’s hash and its own fields, chained per account. The first version allocated chain position before the trigger took its lock, so two overlapping transactions could fork the chain. The fix moved chaining into a deferred constraint trigger that runs at COMMIT and allocates chain_index inside the per-account advisory lock, with a unique index on chain_index as a backstop.

• Write audit rows from application code and trust that nothing edits them
• Ship events to an external log service, which adds infrastructure and still needs ordering guarantees
• Keep the log in PostgreSQL, block changes with grants and triggers, and chain each row to the previous one by hash

The log is tamper-evident, not tamper-proof. Someone who can run DDL could disable the trigger and rewrite the chain, and the hash shows that only if a copy of the chain head is kept somewhere else. Audited writes within one account are serialised at commit.

A regression test opens two real connections, overlaps their transactions and checks that the chain does not fork. Another test checks that the request role cannot update or delete audit rows. In the same reproduction, moving the lock to commit time cut same-tenant audited writes from about 4.8 seconds to about 50 milliseconds.

Human approvals take days and need explicit states, conditional routing, deadlines, idempotent decisions and an audit trail, all of which fit naturally in rows. One decision per stage is a unique constraint, a decided stage matches no update policy, and a rejection without a comment violates a constraint. Resubmission appends new stage rows with a higher attempt number instead of overwriting, and the current stage is derived rather than stored, so there is no pointer to race over.

Routing rules live in this codebase and its tests, not in an established engine. An early version did not re-evaluate routing after a revision, so an event could become high-risk during revision and keep its low-risk route. Migration 010 fixed this, and a test now covers it.

A club should be able to adopt WayClub on its own. Standalone clubs get an internal review route, recorded as CLUB_INTERNAL and ADVISOR_REVIEW, never INSTITUTION_OFFICIAL, because an official approval is impossible without an institution row the account owns.

Two columns on every tenant-scoped table, and policies that handle both modes.

Filtering tenants with a WHERE clause in every query fails open: one forgotten predicate is a breach. With row-level security a missed filter returns an empty result instead of another club’s data. An external authorisation service was over-engineering for a pilot, and a database per institution stays an option for tenants with strict data-residency needs.

Policies have to be written and reviewed per table, every query pays a policy check, and support work must go through the same restricted path. Forcing row-level security on the table owner was measured to break the security-definer helpers, so it is enabled but not forced.