Architecture review — one page

Three surfaces, one database, one authorization model.

Starwood Community is a resident-community platform: a resident web portal, a property-management admin portal and a native mobile app, sharing one Postgres database. The defining choice is that authorization lives in the database, not in the applications — every client talks to the same API under the same row-level security policies, so a new surface cannot invent a new way to leak data.

figures measured at main · 02758f72 August 30, 2026 Re-check them: bash dev_workflow/architecture/verify-figures.sh

01The shape of the system

Three deployable clients and three private shared packages in one npm-workspaces monorepo, task-run by Turborepo, plus one managed backend. Types, domain logic and data access live in the packages, so nothing is duplicated across apps.

3client surfaces
3shared packages
224Postgres tables
798RLS policies
263SQL migrations
129klines of app TS/TSX
CLIENT SURFACES Resident Web Next.js 15 · React 19 · SSR Admin Portal Next.js 15 · React 19 · SSR Mobile App Expo 52 · React Native 0.76 SHARED PACKAGES (npm workspaces) @starwood/ui web components + design primitives @starwood/shared types · validators · pure domain logic @starwood/supabase-client queries · realtime · storage Supabase — one managed Postgres 17 backend PostgREST auto REST over tables/RPCs Auth (GoTrue) JWT · TOTP MFA Realtime postgres_changes channels Storage 2 buckets · 6 RLS policies Row-Level Security — 223 of 224 tables · 798 policies · 254 SECURITY DEFINER functions every client request is authorized here, not in application code HTTPS + JWT
The mobile app does not depend on @starwood/ui — that package is web-only. It shares types, domain logic and data access, and has its own React Native primitives.

One design system, two rendering models

@starwood/ui carries the web components and the token set. The product renders dark by default; light is applied by putting data-theme="light" on the document element, and every light rule is scoped to that attribute so the dark path is never touched. A small bootstrap script runs before first paint, so a resident who chose light mode does not see a dark flash on load. React Native has no CSS, so the mobile app cannot consume that stylesheet — it carries an equivalent palette through a theme context instead. Two implementations, one token vocabulary.

Contrast is measured, not assumed. A scanner in the design-review tooling composites real backgrounds through ancestors and alpha, then reports WCAG failures for every screen in both themes. It currently records zero failures — alongside 106 gradient-background cases it cannot judge automatically and flags for a human rather than counting as passes. The honest reading is "nothing measurable is failing", not "everything is verified".

02The four choices that shaped everything else

Each of these is reversible in principle, but each has downstream consequences worth stating plainly.

1 · Authorization in the database, not the application

Every table carries row-level security bar one documented exception. A client holds a user JWT and talks to PostgREST directly; Postgres decides what that user may read or write. Where a rule needs more than a row predicate — cross-table checks, aggregate counts that must not expose identities, atomic multi-step writes — it becomes a SECURITY DEFINER function with explicit REVOKE/GRANT.

Why it pays: three surfaces plus any future integration inherit the same rules. A bug in one client cannot widen access. What it costs: policies are SQL, so they need SQL-literate review and database-level tests; a policy change is a migration, not a hotfix; and because a rule lives in exactly one predicate, rewriting that predicate can delete it silently — see 5.10 for the time that happened.

2 · One monorepo, shared packages, no shared runtime

Types, validators and pure domain logic live in @starwood/shared and are imported from source — the workspace packages have no build step in development. Data access lives in one place. Apps stay thin.

Why it pays: a domain rule (say, how duplicate write-in poll answers are merged) is written once and unit-tested without a database, then reused by all three clients. What it costs: a change to a shared package can break three apps at once, so the type-check and test sweep has to cover the whole workspace.

3 · Supabase as the backend, rather than a bespoke API tier

There is no hand-written API server. PostgREST exposes tables and RPCs, GoTrue handles identity and MFA, Realtime streams row changes, Storage holds user files, and pg_cron runs scheduled work inside the database.

Why it pays: no API layer to write, secure, deploy or keep in sync with the schema — a meaningful saving at this feature count. What it costs: business logic that would live in a service layer lives in Postgres functions instead, and the platform is a genuine dependency. Portability rests on the fact that everything is standard Postgres plus a thin auth contract.

4 · Property is the tenancy boundary

The chain is organizations → properties → profiles. 153 of the 224 tables carry a property_id, and the standard policy compares it to the caller's own profiles.property_id. Residents see their building; property managers manage their building; super admins cross buildings.

Why it pays: one predicate, applied consistently, gives multi-tenant isolation without separate databases or schemas. What it costs: a table that forgets property_id silently opts out of tenancy, so new tables need review — which is what the database-level test suite is for.

03How a request is actually authorized

This is the part worth understanding in detail, because it is the same for every surface and every feature.

PATH A — ORDINARY READ OR WRITE Client holds user JWT PostgREST role = authenticated RLS policy predicate on auth.uid() Rows the user may see everything else is invisible PATH B — RULES A ROW PREDICATE CANNOT EXPRESS Client calls an RPC SECURITY DEFINER function pinned search_path · REVOKE from anon Re-validates itself tenancy · state · config Result or a named error The floor stays strict either way. A SECURITY DEFINER function never replaces RLS — the underlying table keeps its policies, so a client that bypasses the function still hits the wall.
Path B exists because some rules are not row predicates: aggregate counts that must not reveal who voted, multi-row writes that must be atomic, or visibility that depends on another table's configuration.
THE CHOICE WHAT IT BOUGHT WHAT IT COST, MEASURED 1  Authorization in the database Not in the application. 798 policies over 223 of 224 tables. One rule serves three clients and any integration that comes later. A bug in one client cannot widen access. 254 SECURITY DEFINER escape hatches, each re-checking by hand what RLS did for free. A rule lives in one predicate, so a rewrite can delete it silently (5.10) and a NULL comparison can neuter it (5.12). 2  One monorepo, no build step Shared packages imported from TypeScript source. 51 Zod schemas and the domain logic written once for four consumers, and 9,539 unit tests that need no database. Every suite transforms that graph itself, because main points at src/index.ts. That one-off cost is what broke the sweep until all seven configs raised their ceiling. 3  Supabase, not an API tier Clients speak to PostgREST rather than to code we own. PostgREST, GoTrue, Realtime, Storage and a pooler, with no API tier to build, deploy, monitor or keep patched. Roughly a service we never had to staff. Platform behaviour inherited rather than chosen: anon granted by name (5.12), realtime silent without a publication nothing mentions (5.3), and a fallback that cannot run either. 4  Property is the boundary Tenancy is a column, not a deployment. 153 tables carry property_id and 152 point at properties. One predicate shape everywhere, and a new feature inherits tenancy by naming one column. Staff legitimately span buildings, so a second mechanism exists — property_memberships behind 131 policies. It is the one place the boundary is designed to move, so it is guarded. Every cost in the right-hand column is a real incident from this repository, not a hypothetical. Three of the four choices are cheap to reverse on paper and expensive in practice; the fourth — tenancy as a column — is the one that would be a rewrite.
The left column is why these were chosen and the right is what has actually been paid so far. A CTO should read the right column first: it is the part that does not appear in an architecture diagram.

Roles

RoleScopeEnforced by
residentTheir own rows, plus what their property sharesRLS predicates on property_id and auth.uid()
property_managerEverything within their propertyRLS role check + admin-portal middleware
super_adminAll propertiesDedicated FOR ALL policies
system_adminOperational surfaces such as the log viewerAdmin-portal middleware + RPC grants

Both Next.js apps run edge middleware that rejects unauthenticated requests before a page renders, and the admin portal additionally requires one of the three privileged roles. That middleware is a convenience, not the boundary — the same check exists in the database, so a request that skips the app entirely is still refused.

One deliberate exception. 223 of 224 tables use RLS. The exception, app_logs, holds no grant at all for anon or authenticated — only postgres and service_role — so there is nothing for a policy to filter. Clients write to it through insert_app_log, and reading is system_admin only, through get_app_logs. Locked by grant rather than by policy: stricter than RLS, not weaker.

Staff tenancy — how a manager reaches a second property

Everything above describes a resident, whose tenancy is a single column. Staff are the harder case: a regional manager legitimately needs several buildings, and the mechanism that grants that is the one place where widening somebody’s reach is a normal operation rather than an attack.

profiles PK id role property_id the home one One column, and for a resident that is the whole of tenancy. For staff it is only the building they belong to. property_memberships PK (pm_id, property_id) created_by, created_at Two columns and a composite key — the entire multi-property model. Each row is one extra building, and the key makes a duplicate grant impossible. auth_user_can_access_property() one function, and 131 policies call it super_admin or system_admin → true it is your home property → true a PM with a membership row → true anything else, or no session → false Fails closed on a NULL caller — see 5.12 for why that is worth saying. WHY NOBODY CAN GRANT THEMSELVES A BUILDING The table grants authenticated exactly one privilege: SELECT no INSERT · no UPDATE · no DELETE So no signed-in user writes this table — not a manager, and not a super admin either. The INSERT and DELETE policies exist for service_role alone; for everyone else the grant refuses before row-level security is ever consulted. The only way in is one audited function approve_property_access_request() A manager asks with request_property_access; an admin approves. The function checks the session, requires super_admin or system_admin, refuses a request that is not pending, then inserts — and writes an audit row and a notice. MEASURED, NOT ASSUMED Evaluated as a real property manager against the running database, inside a transaction that was rolled back: home property → true a second property → false after a membership row → true that manager granting it to themselves → refused, “permission denied for table property_memberships” The gap worth naming: the seed contains zero membership rows and the integration suite makes zero assertions about this path, so every automated run exercises only the home-property branch. The other two branches are covered by construction and by the probe above, not by a test.
This is the one place the tenancy boundary is designed to move, so it is worth seeing that moving it takes an admin, an audit row and a function — and that the table itself refuses everyone else at the grant layer, before any policy is consulted.

How 798 policies stay coherent

The obvious objection to putting authorization in the database is that it does not scale as a discipline — hundreds of hand-written predicates, each a chance to get one wrong. Here is the actual shape of them, measured rather than asserted, along with the places the convention has frayed. One number below is worth reading twice: 223 tables carry RLS but only 222 have a policy. The odd one out is property_lookup_attempts, which has RLS on and no policy at all — the strictest setting there is, since nothing matches. Only the SECURITY DEFINER rate limiter behind signup touches it.

798 policies over 222 tables median 3 per table, most 9 ALL 302 · SELECT 250 · other 246 0 policies that are simply true every one names an identity, a property, or a published flag 6 helper functions do the work all STABLE, SECURITY DEFINER, and search_path pinned 223/224 tables carry RLS the exception is locked by grant instead — see above WHAT EVERY POLICY IS BUILT FROM — A PARTITION, NOT A SAMPLE 558 built on an auth_* helper 136 own-row: auth.uid() compared directly 99 a raw subquery on profiles, no helper 5 deliberately open to any signed-in reader The 5 are product-level documents — accessibility audits, VPAT components, trust-centre pages. None has a property_id, so there is no tenant to scope to. The six helpers, by how much they carry auth_user_role auth_user_property_id auth_user_can_access_property auth_is_pm_for_property auth_is_channel_member auth_can_read_channel 501 221 131 6 3 1 THE NAMING CONVENTION IS THE INDEX A policy is named for who it serves, then what it permits, so the catalogue itself is searchable by audience: pm_ 323   super_ 171   resident_ 100 read_ 48   admin_ 32   insert_ 21   delete_ 14 AND WHERE IT HAS FRAYED 99 policies inline a profiles subquery a helper already expresses. 9 of 223 tables have no super_admin escape — six of them one co-op cluster, which is also the cluster whose functions lacked a pinned search_path until migration 264. One author, one standard, and it is not the house one. The answer to “does this scale as a discipline?” Seven in ten policies are a call to one of six audited functions rather than a hand-written predicate, so the number that could independently be wrong is closer to six than to 798. The exceptions are countable, named above, and each is a migration’s worth of work to retire.
Every number here is re-derived from the catalogs by verify-figures.sh rather than maintained by hand — including the frayed edges, which is the point: a convention you cannot measure is a convention you are guessing about.

04The data model

224 tables is too many to draw. What matters is that they all hang off the same two hubs, and that every feature cluster repeats the same shape.

organizations the operator properties tenancy boundary 152 tables reference it profiles identity · 1:1 with auth.users 193 tables · 265 keys 1 : N 1 : N FEATURE CLUSTERS — EVERY ONE SCOPED BY PROPERTY, AUTHORED BY A PROFILE Community posts · post_comments post_likes · post_saves Events events · event_rsvps event_ticket_tiers · event_comments Amenities amenities · amenity_bookings Maintenance work_orders · work_order_activity Messaging conversations · messages · connections Local perks merchants · merchant_offers merchant_reviews Polls — the pattern in miniature polls · poll_options · poll_votes · poll_responses Compliance & operations — the long tail Rent ledgers, inspections, emissions reporting, lead and water testing, tenant-rights workflows — the bulk of the 224 tables, same tenancy rule.
Two hubs carry the model: profiles answers “who wrote this”, properties answers “who may see it”. Almost every other table joins to both.

One cluster in full

Every feature cluster is built the same way, so one is worth reading closely. Polls carry the tenancy key and the author on the parent row, keep choices and free text in separate children, and push the rules that must not be bypassed into constraints rather than application code.

polls PK id FK property_id tenancy FK created_by author title, description poll_type single | multiple | free_text status active | closed expires_at, created_at allow_write_in allow_comment show_responses_to_residents show_response_authors the four booleans an admin sets poll_options PK id FK poll_id option_text, display_order is_write_in the "Other" row poll_votes PK id FK poll_id, option_id FK voter_id created_at poll_responses PK id FK poll_id, respondent_id kind write_in | comment | answer body created_at, updated_at 1:N 1:N 1:N option_id RULES HELD BY THE DATABASE UNIQUE (poll_id, option_id, voter_id) one vote per option per person UNIQUE (poll_id, respondent_id, kind) one write-in, one comment, one answer per person per poll UNIQUE INDEX .. WHERE is_write_in at most one "Other" option per poll CHECK on body length by kind 200 / 1000 / 2000 characters TRIGGER on poll_options a free_text poll may hold no options; no write-in row unless allow_write_in TRIGGER on poll_votes single-choice: one vote per person RLS + SECURITY DEFINER reads of other people's text go only through get_poll_responses() none of these depend on the client
Nothing here is enforced only in TypeScript. Each rule is a constraint, an index or a trigger, so a crafted REST call meets the same wall the UI does.

Schema at a glance

ObjectCountNote
Tables224223 with row-level security enabled
RLS policies798The authorization surface
Functions433Of which 254 are SECURITY DEFINER
Triggers194Invariants the client cannot skip
Check constraints1,244Validation that survives a crafted API call
Foreign keys556On delete: 311 cascade, 220 set null, 19 restrict, 6 no action
Indexes815154 of them partial — filtered reads on hot paths
Enum types216Status and category vocabularies
Migrations26357,450 lines of SQL, applied in order

The 224 tables are not 224 unrelated things. Grouped mechanically by name family, 37 families of three or more tables account for 154 of them, and only 34 are one-offs. The clusters drawn above are a handful of those families; the long tail is compliance and operations, built the same way.

Validation is deliberately duplicated: the same rule exists as a Zod schema in @starwood/shared (for a good error message in the UI) and as a constraint or trigger in Postgres (because the REST API is reachable without the UI). The database is the one that counts.

05Key flows

Thirteen processes worth tracing end to end, because they are the ones where a mistake would be expensive. Each pairs a rule that must hold with the place the system actually holds it.

FlowThe rule that must holdWhere it is enforcedData model
5.1 Booking an amenityTwo people cannot take the last slotRow lock in the RPC + a trigger every caller meets✓
5.2 Maintenance requestA status cannot skip or go backwardsBEFORE UPDATE trigger, one audited bypass✓
5.3 Live and timed workScheduled things happen with nobody watchingRealtime channels + four pg_cron jobs—
5.4 Signing inA skipped app cannot mean skipped checksMiddleware for UX, RLS for the boundary—
5.5 Event RSVPA full event waitlists rather than oversellsRow lock, capacity trigger, FIFO promotion✓
5.6 NotificationsA resident only hears what they asked forTrigger per event kind + a preference gate✓
5.7 Joining a propertyA stranger can look up a building, not harvest itNarrowed anon RPC + sliding-window rate limit—
5.8 Private messagesOnly the two people in a thread — plus staffRLS on both tables, and a path-bound attachment✓
5.9 Moving a resident outOne flag has to settle five tables, auditablyA trigger on profiles with a scoped bypass—
5.10 Seeing a postFour separate rules decide one row's visibilityA single policy — and the way it once lost a clause✓
5.11 Points and rewardsPoints are money, so the count must never driftAppend-only ledger, unique index, locks where sums rule✓
5.12 The guard that isn’tAn authorisation check that never firesNULL comparisons, SECURITY DEFINER, and two revokes—
5.13 Feature flags“Off” must mean hidden and unreachableOne shared route map, three surfaces, two defaults✓

5.1  A write that must not be wrong — booking an amenity

Two residents tapping the last slot at the same moment must not both get it. The booking RPC is the front door, but it is not the only door: row-level security also permits a direct insert from the REST API. So every rule is enforced twice — once in the RPC for a clean error message, once in a trigger that no caller can skip.

FRONT DOOR — WHAT THE APP CALLS Resident picks a slot book_amenity_slot() SECURITY DEFINER · validates in this order 1 signed in? NOT_AUTHENTICATED 2 has a profile? PROFILE_NOT_FOUND 3 end after start? INVALID_TIME_RANGE 4 SELECT .. FROM amenities .. FOR UPDATE locks the amenity row — concurrent bookers now queue, not race 5 amenity live, same property? AMENITY_NOT_FOUND · AMENITY_NOT_IN_USER_PROPERTY 6 time window legal? BOOKING_IN_PAST · TOO_SHORT · TOO_LONG · TOO_FAR_AHEAD 7 caller not already in this slot? ALREADY_BOOKED_BY_USER 8 slot under capacity? SLOT_FULLY_BOOKED → INSERT INTO amenity_bookings BACK DOOR — WHAT A CRAFTED CALL CAN DO REST call skips the RPC RLS policy insert_own_booking checks ownership and property — but knows nothing about capacity or duration BEFORE INSERT trigger check_booking_overlap() re-checks, for every caller: · capacity for the slot · this resident's overlap · min / max duration · advance window · dates in the past Booking row committed or a named error, identically, whichever door was used The trigger exists because the RPC is not the only way in. It was added after review found the direct-insert path skipped every duration and advance-window rule.
The row lock at step 4 is what makes the capacity check trustworthy: without it, two callers could both read "one seat left" before either wrote.

The data behind it — what a booking is, and which parts of it can never change

Two tables, and an unusually opinionated set of rules about them. Worth reading because the rules are the product: an amenity is not a calendar, it is a capacity with a duration policy attached.

amenities PK id FK property_id, created_by name, description, category, rules capacity CHECK > 0 access_mode bookable | open min_duration_minutes 30 max_duration_minutes 480 max_advance_days 90 cover_image_url, image_urls A CHECK rejects any image URL whose protocol is not safe, so a stored javascript: never reaches a client. amenity_bookings PK id FK amenity_id, profile_id booking_date, start_time, end_time status confirmed | cancelled qr_token UNIQUE, 16 random bytes checked_in_at, checked_in_by marked_no_show_at/_by notes, cancelled_by, created_at CHECK: end_time > start_time CHECK: checked in or no-showed, never both The QR token is the check-in credential, so the database mints it. Immutable after insert A second trigger rejects any UPDATE that moves: amenity_id profile_id booking_date start_time end_time qr_token Rescheduling is a cancel plus a new booking, so all eight rules run again on the new time. WHAT THE BEFORE-INSERT TRIGGER CHECKS, IN ORDER 1 status is confirmed — a cancelled row is exempt from every rule below 2 end_time > start_time    3 the amenity exists    4 duration within the amenity’s min and max 5 the date is not in the past    6 the date is within max_advance_days 7 this resident has no overlapping booking on this amenity    8 confirmed overlaps stay under capacity Each failure raises a distinct code — BOOKING_TOO_SHORT, BOOKING_IN_PAST, ALREADY_BOOKED_BY_USER — so a client can say which rule it hit. There is no unique constraint stopping a double booking — and there cannot be Capacity is “how many confirmed bookings overlap this time range”, which is a count across rows, not a property of one. No UNIQUE or EXCLUDE constraint expresses it, so it is a counted check — which is only safe because 5.1’s RPC takes a row lock on the amenity first. The same shape as 5.11’s spending ceilings, and the reason both needed a lock rather than a constraint.
Six of the fifteen columns cannot be changed once written. That is what makes the QR token usable as a check-in credential and the audit trail worth reading — a booking’s history is append-only in everything that matters.

5.2  A lifecycle that must not skip — a maintenance request

A work order is the one place where residents and staff share a record over days. Residents open one and read their own; only a property manager in that property can move it, and the database refuses any move that is not the next step.

RESIDENT OPENS IT · PROPERTY MANAGER MOVES IT submitted resident creates acknowledged PM has seen it in_progress work started completed resident can rate closed terminal any move backwards — rejected any skipped step — rejected Enforced by a BEFORE UPDATE trigger check_work_order_status_transition() Raises on anything that is not the next step. Not a client rule. Exactly one bypass, and it is deliberate Moving a resident out force-closes their open work orders. It announces itself with a transaction-local flag no other caller sets.
Residents may create and read their own; only a property manager in the same property may update one. Both rules are row-level security policies, not screens.
work_orders PK id FK property_id tenancy FK profile_id who reported it FK assigned_to who owns it status the state machine above category, urgency, title, description image_urls entry_permission, preferred_times how and when staff may enter the home vendor_name, vendor_phone, vendor_email vendor_notes, scheduled_date resolution_notes, resolved_at satisfaction_rating, satisfaction_comment created_at, updated_at work_order_activity PK id FK work_order_id, author_id activity_type note · status_change content, metadata (jsonb) created_at the thread and the audit trail in one table 1:N WHO WRITES WHAT Resident: opens one, reads their own, adds comments to it Property manager: reads and updates any in their property, logs activity WHY IT IS SHAPED THIS WAY One row, two audiences A resident sees their request; staff see the vendor and scheduling fields on the same row. RLS decides, not two tables. Entry permission is data "call first" or "only when present" is a column, so it travels with the job rather than living in a message thread. Activity is append-only in practice Every status change writes a row, which is what makes the auto-close during a move-out auditable rather than silent.
The satisfaction fields exist because the loop only closes when the resident says it did — the rating is captured on the same row as the work.

5.3  Work that happens with nobody watching

Two things must keep running when no browser is open: pushing changes to people who are looking, and doing timed work for people who are not. Both live in the database rather than in a separate service.

PUSH — A POST APPEARS WITHOUT A REFRESH Resident A publishes a post INSERT posts under RLS as usual Realtime postgres_changes on posts Everyone on that channel feed:<property_id> The channel is one per property and the subscription is filtered property_id=eq.<id> — a resident is never handed another building's traffic. TIMED — FOUR JOBS INSIDE POSTGRES pg_cron runs in the database, not in a worker nothing extra to deploy or monitor * * * * * publish-scheduled-posts flips a post from scheduled to published when its time arrives * * * * * archive-expired-posts retires content past its expiry * * * * * run-announcement-schedules publishes a due schedule as a post · FOR UPDATE SKIP LOCKED 0 9 * * 1 generate-weekly-digests in-app digest for quiet residents · email side is a stub Each job is a SECURITY DEFINER function with execute revoked from PUBLIC — a resident cannot trigger one by calling it. THE HALF THAT WAS MISSING UNTIL MIGRATION 265 The client side above was always right. The other half was not: Postgres streams changes only for tables belonging to a PUBLICATION, and supabase_realtime was created empty and never populated by any migration. Live updates had therefore never worked in any environment built from this repository, for as long as the feature has existed. It survived because the failure is silent by construction: the socket connects, the channel joins, the subscription reports “Subscribed”, and no event is ever emitted. A refresh always showed the new row, so it read as slow rather than as broken. 265 adds posts, messages and notifications. Replica identity stays at the primary key — nothing reads old_record, and FULL would cost WAL on every write. A second failure looks identical but is environmental: a realtime schema ahead of the container’s version makes every subscribe fail with an out-of-range error. Check both.
Scheduled publishing lives in the database on purpose: a post must go out at its appointed minute whether or not anyone is using the app. The announcement job takes its rows with FOR UPDATE SKIP LOCKED, so a slow run overlapping the next minute cannot double-publish.

5.4  Getting in the door

Two checks run on every request, and only one of them is the boundary.

Email + password GoTrue JWT issued aal1 TOTP factor enrolled? if yes — step-up challenge before any dashboard renders aal1 → aal2 Session cookie on every request carried by browser and app alike AND A THIRD CHECK NOBODY COUNTED — ON EVERY PROTECTED PAGE Beyond the two above, every protected page asks Supabase who the caller is and redirects to /login if it cannot say. That test was authError || !user — which makes “the lookup failed” and “there is no session” the same answer. They are not. supabase-js sets no timeout on its fetch, so GoTrue under pressure answers slowly or drops the connection, and the page then signs out a resident who was signed in. Four simultaneous sign-ins reproduce it: a session reaches /home and is bounced to a bare /login. The fix asks twice before believing a failure — a revoked session fails identically both times and still redirects, a blip resolves on the second ask — across all 34 pages carrying the pattern, since the bounce lands on whichever page the resident opens next. Neither of the two checks above was wrong. The defect was in the code that decides whether to consult them at all. Edge middleware — convenience Runs before a page renders on both web apps. · no session → redirect to /login · admin portal also requires a privileged role · sheds load rather than queueing under a burst Skipping it costs an attacker nothing — so it is not the wall. Row-level security — the boundary Runs inside Postgres on every query, from any client. · the JWT becomes auth.uid() · policies compare it to the row and to the property · a request that skips the app is refused identically This is the check that has to be right. every subsequent request carries it into both checks
Multi-factor is opt-in: a resident enrols TOTP from their profile, and an admin who has enrolled one cannot reach the portal on a single-factor session.

5.5  A queue that manages itself — event RSVP

An event with a capacity has to do three things at once: refuse to oversell, hold the people who missed out, and give a freed seat to whoever waited longest — without anyone pressing a button.

Resident taps RSVP upsert_event_rsvp() SECURITY DEFINER · 14 named refusals signed in, active, acting as self NOT_AUTHENTICATED · PROFILE_NOT_ACTIVE · IDENTITY_MISMATCH SELECT .. FROM events .. FOR UPDATE the seat count is read and written under one lock event is real, live and not past EVENT_NOT_FOUND · EVENT_CANCELLED · EVENT_IN_PAST guests within the event's own rules GUESTS_NOT_ALLOWED · GUEST_COUNT_EXCEEDED ticket tier valid and still available TICKET_TIER_REQUIRED · INVALID_TICKET_TIER · SOLD_OUT seat plus guests still fit EVENT_AT_CAPACITY One of four states going — seat held, guests counted waitlist — queued, in arrival order maybe — no seat consumed cancelled — releases the seat The queue drains itself auto_promote_waitlist() AFTER DELETE OR UPDATE on rsvps Fires only when a seated RSVP leaves. Re-counts, then promotes the oldest waitlisted RSVP — first in, first seated. a promotion frees nothing, so the loop settles Capacity counts seated + 1 + guests, so a party of four cannot slip into one remaining seat — under the same row lock as the amenity booking. Three further triggers guard the same table RSVPs are refused on a cancelled event, a ticket tier must belong to the event it is bought for, and deleting a tier cancels the RSVPs that bought it and records what is owed.
Nothing in this flow needs an operator. A cancellation at 2am promotes the next person and the count stays exact.
events PK id FK property_id, created_by title, description, category, location start_time, end_time status, is_resident_hosted capacity null = unlimited ticket_type allow_guests, max_guests_per_rsvp is_recurring, recurring_pattern recurrence_end_date the highlighted fields are what the RSVP guards read event_rsvps PK id FK event_id, profile_id FK ticket_tier_id status, guest_count refund_owed_cents tier_deleted_at, tier_deleted_name event_ticket_tiers PK id  FK event_id name, description price_cents quantity_available tier bought HELD BY THE DATABASE enforce_event_rsvp_capacity seats + guests never exceed capacity auto_promote_waitlist a freed seat goes to the longest wait ..._event_not_cancelled no RSVP onto a cancelled event ..._tier_event_match a tier must belong to its own event tier deletion cancels affected RSVPs and records the refund owed
The refund and tier-deletion columns exist because a paid tier can be withdrawn after people have bought it — the row has to remember what it was worth.

5.6  How a domain event becomes a notification

Fifteen tables can notify a resident. None of it is written by application code — each is a trigger on the table where the thing actually happened, so a notification cannot be forgotten by a client and cannot be forged by one.

SOMETHING HAPPENS IN THE PRODUCT a neighbour comments someone RSVPs to your event a connection is requested a package arrives a move-out changes status …15 tables, 18 triggers A trigger on that table notify_on_post_comment() notify_on_event_rsvp() … Fires on the write itself, so the notification cannot be skipped by a client that forgets to send it. The resident's own choice should_notify(user, type) Maps the event type onto one of ten switches the resident controls, and reads their preferences row. No row yet? Default is on. Declined → nothing written the switch is the whole mechanism off INSERT INTO notifications user · property · type · title · link on The bell, in-app read · dismiss · snooze And that is where it stops today There is no email sender and no provisioned push channel, so a notification only reaches someone who opens the app. See the gaps. The ten switches: announcements · social · events · messages · work_orders · connections · amenities · engagement · deliveries · moves One row per resident in notification_preferences — only that resident, or a super admin, can read or change it.
Putting the fan-out in triggers is what makes "you were notified" a property of the data rather than of whichever client happened to perform the write.

The data behind it — 46 event types, 10 switches, and the 16 that answer to none of them

The flow above is only half the story. The other half is how many kinds of notification exist, how few controls a resident actually has over them, and which ones ignore those controls entirely.

notifications PK id FK user_id, property_id type CHECK — 46 allowed values title, body, link is_read NOT NULL false dismissed_at, snoozed_until created_at The type list is a CHECK constraint, not an enum, so adding a kind is a migration and an unknown kind cannot be written at all. notification_preferences PK profile_id one row per resident announcements social events messages work_orders connections amenities engagement moves deliveries updated_at Ten booleans, all NOT NULL DEFAULT true. Only the resident and a super admin can read or write it — four policies, no PM access. should_notify(user, type) A single CASE mapping type → switch. Two defaults, both “notify”: • no preferences row → true • type in no WHEN arm → true Failing open is right for a notice, but it means the map’s gaps are invisible: a new type is unmutable until somebody adds an arm, and nothing reports that it is. That gap is 16 of the 46 types. WHERE THE 46 TYPES GO 30 mapped to a switch events 11  ·  social 4  ·  moves 4  ·  connections 2  ·  amenities 2 engagement 2  ·  deliveries 2  ·  announcements 1  ·  messages 1  ·  work_orders 1 16 governed by nothing rent_due_soon · rent_overdue · rent_payment_posted · reward_redeemed · reward_fulfilled reward_cancelled · post_approved · post_rejected · post_pending_approval · and 7 more Some of the 16 are correct safety_alert and emergency_broadcast must not be mutable. Four more go to staff, not to the resident. The rent and reward ones are simply unclassified. A switch nobody chose is still a choice. AND TWO TRIGGERS SKIPPED THE GATE ALTOGETHER Of the 31 functions that write a notification, 27 call should_notify. Four skip it on purpose — two staff trails, one generic helper, one emergency. Two more skipped it by accident, and both wrote a type that is mapped: notify_on_post_like → social, notify_on_move_inspection_completed → moves. A resident who switched Social off still got a notice for every reaction. Migration 263 adds the guard to both; showing a control that does nothing is worse than showing none.
Both halves of this figure are the same lesson from opposite directions: the preference switch only means something where a WHEN arm and a caller-side check both exist, and neither the schema nor the type constraint can tell you where they don’t.

5.7  The one thing a stranger may ask — joining a property

Signup has a chicken-and-egg problem: someone with no account must resolve a building before they can create one. That means an endpoint reachable by anon, which is the only such surface in the system and therefore the one worth reading closely.

Anonymous has an access code role = anon lookup_property_by_access_code() SECURITY DEFINER · granted to anon Matches case-insensitively on the trimmed code. Rate gate before anything is read signed in: 30 / 60s per user · anon: 60 / 60s in total Attempts are logged to a table the caller cannot read — execute is revoked from PUBLIC, anon and authenticated. Returns (id, name) and nothing else — never the code the id is all signup needs Sign up against that id auth.users → profiles → normal RLS What this replaced, and why it is worth knowing The first version returned access_code alongside id and name, and there was a second function taking a property id. Together that meant an invite link of the form ?property=<uuid> disclosed the building's shared secret to anyone who followed it — and with no rate limit, short codes could simply be swept. The shape of the fix generalises Narrow the return type to what the caller actually needs; put the throttle inside the function rather than in front of it; and keep the throttle's own ledger unreadable. An access code is still a shared secret, so it remains the weakest credential in the system.
This is the only function in the system granted to anon. Everything else requires a session, which is why it carries its own rate limit rather than relying on one.

5.8  Private messages, and who can actually read them

Resident-to-resident chat is the most privacy-sensitive data in the product. Three tables and one storage rule decide who sees it.

conversations PK id FK property_id FK participant_one, participant_two created_at archived_by_one, archived_by_two exactly two people, archived per side messages PK id FK conversation_id, sender_id content, is_read, created_at attachment_url attachment_type, attachment_name 1:N WHO MAY READ A THREAD The two participants participant_one or participant_two = auth.uid() The property manager any thread in their property — labelled "moderation" in the schema Super admins a FOR ALL policy, as everywhere Nobody else a resident in the same building sees nothing of a thread they are not in ATTACHMENTS ARE PATH-BOUND The private bucket keys every object by its uploader: foldername(name)[1] = auth.uid() so nobody can write into another resident's folder. Reading needs more than the path A read also requires a message that actually carries the file, so guessing an object name is not enough. 5 MB cap · six policies across the two buckets · the public bucket is separate
The moderation policy is a deliberate product decision recorded in the schema — but residents are not told about it anywhere in the app. That is a disclosure question, not a technical one.

5.9  One flag, five tables — moving a resident out

Deactivating a resident is a single boolean write. Everything that has to follow from it happens in one transaction, in the database, so a half-finished move-out cannot exist.

A manager flips profiles.is_active true → false AFTER UPDATE trigger handle_resident_moveout() only when the flag changed 1 · Close their open work orders — the only place the state machine bends set_config('app.wo_bypass_transition','on', true) — transaction-local For each order not already completed or closed: set it closed, append "Auto-closed: resident moved out" to the resolution notes, stamp resolved_at — and write a work_order_activity row recording old status, new status and the reason. set_config(.., 'off', true) immediately after, so the bypass cannot leak to later statements. 2 · Hide their posts is_hidden = true hidden_reason = 'moveout' the reason is what makes it reversible later 3 · Cancel bookings confirmed amenity bookings dated today or later past bookings are history and are left alone 4 · Drop connections rows where they are either requester or addressee deleted, not archived Reactivation is not a full undo, on purpose Flipping the flag back un-hides only the posts whose hidden_reason is 'moveout' — anything hidden by moderation stays hidden. Closed work orders, cancelled bookings and removed connections do not come back. Why this is a trigger and not application code Three surfaces can deactivate a resident. Putting the cascade behind the write means none of them can perform half of it, and an admin tool that flips the flag directly gets the same behaviour. It is also the reason the work-order state machine needs a bypass at all.
The bypass is switched on and off inside the same function, so the one place the status rules bend is bounded to these statements and recorded in the activity log.

5.10  Four rules in one predicate — whether you see a post

The feed is where the authorization model is under the most pressure: tenancy, moderation, drafts and scheduling, and private channels all decide the same row. They are not four checks in the application. They are one policy, and reading it is the fastest way to understand both the strength and the cost of this design.

read_property_posts — EVERY CLAUSE MUST HOLD 1 · Is it your building? property_id = auth_user_property_id() the tenancy predicate every table shares 2 · Has it been hidden? is_hidden = false OR is_hidden IS NULL OR auth_user_role() = 'property_manager' managers keep seeing hidden posts — they have to, to unhide them 3 · Is it a draft or scheduled? then only its author, or a property manager an unfinished post is invisible to the building 4 · Published — but is it due, and is the channel yours? scheduled_at is null or already passed and the channel is public, or you are a member, or you are a PM a future-dated post is not merely hidden by the UI — it is not returned posts PK id FK property_id, author_id FK channel_id null = the whole building title, content, image_url, category is_hidden, hidden_reason gate 2 — and what move-out sets status, scheduled_at, expires_at gates 3 and 4 — driven by pg_cron is_announcement, is_pinned, priority target_audience, target_building target_floor, target_units announcement targeting, applied by the app created_at Four features, one row, one policy Moderation, drafts, scheduling and channels were each added later. None of them got its own table — they got clauses. The cost of this design, stated plainly Migration 028 added the moderation clause. Four later migrations recreated this policy to add drafts (106), expiry (114), channels (171) and scheduled publishing (229). Each carried its own new logic forward and dropped the is_hidden clause. Hiding a post stopped hiding it — and because nothing in the app filters is_hidden either, the policy was the only thing enforcing it. Migration 240 put the clause back. A rule that lives in one place is only as safe as the last person to rewrite that place.
This is the strongest argument on the page for testing policies as behaviour rather than reviewing them as code: every rewrite compiled, passed review and shipped.

5.11  When the data is money — points and rewards

Residents earn points for taking part and spend them on gift cards funded from the property's own budget. That makes the ledger financial data, and it is modelled the way an accountant would rather than the way a feature usually is.

point_ledger PK id FK profile_id, property_id delta + earn / − spend reason, created_by, created_at source_type, source_id No balance column, anywhere A balance is a SUM over this table. A stored one drifts the first time two awards race — and once it has drifted, nothing says which number was right. point_earning_rules property_id, source_type, is_active points_per_event max_per_window, window_days the rate and the ceiling, set per property point_budgets property_id, period_start, period_end allocated_points what the property has put up; spend is derived from the ledger, never stored alongside it reward_catalog property_id, name, description cost_points, stock, is_active what a property offers, priced in points reward_redemptions profile_id, reward_id, property_id points_spent, status fulfilled_by, fulfilled_at the claim, and who honoured it THREE INVARIANTS, AND WHAT HOLDS EACH ONE 1 · History is never rewritten Triggers block UPDATE and DELETE on the ledger outright. A mistake is fixed by writing a compensating row, so the error and its correction both remain visible to whoever audits it later. 2 · An award happens once UNIQUE (profile, source_type, source_id) A retried request or a double-submitted survey pays once. Manual awards carry no source_id — twice is a decision. 3 · Every earn path has a ceiling A rate and a cap per rolling window, per property. Paying for poll votes without one makes the cheapest route to a gift card "vote in everything". and the property budget caps the total TWO DEFECTS THE FIRST LIVE RUN FOUND Both ceilings were read-then-write Read the total so far, decide there is room, insert. Two interleaved transactions both read the old total. Measured live: a 100-point window cap paid out 200; a budget with 100 left went 100 over. Fixed by serialising: an advisory lock per resident, and FOR UPDATE on the budget row. Anonymous callers could mint points Two mistakes lined up; either alone was survivable. 1. Four migrations revoked only FROM PUBLIC, so anon kept the grant Supabase issues it by name. 2. Authorisation sat inside IF caller IS NOT NULL, so an unauthenticated call skipped every check in it. Live, with the bundled anon key: 500 points minted, and another resident’s challenge score set to 999. The rule both defects teach: a constraint the database enforces holds under concurrency — a SELECT followed by an INSERT does not.
The idempotency guard never broke during the race, because it is a real unique index rather than a read. That is the distinction worth carrying into any new feature that counts something.

5.12  The guard that isn’t — a NULL-comparison authorisation class

Fixing 5.11 raised an obvious question: if two functions trusted an unauthenticated caller, how many others do? Sweeping the schema for the shape found four more, all confirmed against a live PostgREST using the public anon key — the key that ships inside the client bundle — with no Authorization header at all. Migration 262 closes them.

WHY THE CHECK DOES NOTHING The code everyone reads as a check IF v_owner != auth.uid() THEN RAISE EXCEPTION ‘not yours’; END IF; For an anonymous request auth.uid() is NULL, so the comparison is uuid != NULL → NULL, not true. A NULL condition does not take the branch, so nothing raises. Why that is worse than it sounds Execution falls past the guard into the body — and the body is SECURITY DEFINER, so it runs with the owner’s rights and RLS never applies. The one control that would have caught it is the control the function opted out of. The guard reads like a check and behaves like a comment. WHAT AN UNAUTHENTICATED CALLER COULD DO — MEASURED, NOT INFERRED submit_work_order_feedback wrote a 1-star rating and free text onto another resident’s work order — HTTP 204 get_app_logs returned the whole application log: user_id, property_id, action, user_agent count_app_logs returned 65 — same admin-only check, defeated the same way get_event_attendees returned resident names and avatars — this one had no caller check at all Probed against the local development stack; the work-order row was restored immediately. Two functions matched the same text pattern and were not vulnerable — see below. THE FIX, IN TWO PARTS — NEITHER SUFFICIENT ALONE Belt — fail closed on a NULL caller An explicit IF v_caller IS NULL THEN RAISE, and IS DISTINCT FROM in place of != — the NULL-safe operator. This is exactly why the two lookalikes were safe: get_directory_profiles already gated on auth_user_property_id() IS DISTINCT FROM prop_id Braces — take the grant away, both of them 5.11 showed REVOKE .. FROM PUBLIC leaves anon’s named grant. The complement is also true, and was measured here: revoking only FROM anon leaves Postgres’s default EXECUTE-to-PUBLIC, and anon inherits through it. Closed only when both are revoked.
After migration 262 an anonymous call to all four returns 401 permission denied at the grant, before the body runs, while an authenticated resident is unaffected. Both were re-probed to confirm it.

5.13  One row of booleans, three surfaces — per-property feature flags

Not every property wants every feature. A flag on property_settings switches one off, and the interesting part is what “off” has to mean: hidden in the navigation of two different apps and unreachable if somebody types the URL. Getting those to agree is the whole design.

property_settings PK property_id feature_events feature_polls feature_amenities feature_safety feature_merchants feature_payments feature_messaging feature_directory feature_work_orders feature_prospect_tours Residents may read their own property’s row. Only a manager may write it. packages/shared/feature-flags The one place routes and flags are married /events → feature_events /amenities → feature_amenities /messages → feature_messaging /safety → feature_safety … 12 prefixes in all Longest prefix wins. Paths are decoded up to three times and stripped of route groups and dot segments before matching. consumer-web middleware Blocks the route → /home?unavailable=<key> web app shell + command palette Hides the nav entry and the search action mobile tab bar + More screen Same filter, same map, different renderer WHY THE MAP CANNOT LIVE IN EACH APP A flag that hides a link but leaves the route reachable is not a feature flag, it is a cosmetic one — the first person to type the URL walks straight through. Hidden navigation and blocked routes have to come from the same table, or they drift apart silently. TWO DEFAULTS, POINTING OPPOSITE WAYS Nine resident features fail open: a missing row, a null, a failed query all resolve to enabled, because a database blip must not lock a resident out. feature_prospect_tours fails closed: the same blip must not publish a public booking page nobody asked for. THE BUG THIS REPLACED Seven of these columns have existed since migration 095, and the admin portal has shipped a form for them ever since. Nothing read them. A property manager could switch off Directory or Messaging, see the form save, and residents kept full access — no client and no policy consulted the column. Migration 244 added two more and 250 the tenth; the module above is what made any of them do something. A setting that saves but does nothing is the worst kind. Configuration for nine of them — and a real gate for the tenth The nine resident flags are enforced in app code only. Nothing stops a caller with a valid token reading events through the API directly; RLS governs that, as it always did. feature_prospect_tours is different, because its pages are anon-callable: migration 254 put the check inside the booking RPCs after a prospect was shown to book a tour at an opted-out property. Where a flag must actually hold, it has to live where anon cannot route around it. The redirect explains itself, and does it twice over. /home maps the key to a resident-facing name through a fixed table, so the URL chooses which of ten names appears but never the words — and it re-reads the flags before believing the claim, on the settings query the page was already making. A stale or forged ?unavailable= resolves to nothing rather than telling a resident a live feature is switched off.
The distinction in the last box is the one worth pressing on: nine of these flags decide what a property is offered, not what its data permits, and that is a legitimate choice. The tenth needed to be a real gate, and only became one after somebody proved it was not.

06Runtime and delivery

Realtime push

Feed, messages and unread counts subscribe to postgres_changes channels, so updates arrive without polling. Channels are authorized with the same user JWT the REST calls use.

Scheduled work pg_cron

Four jobs run inside the database — publishing scheduled posts, archiving expired ones and running announcement schedules every minute, plus a weekly digest on Monday mornings. No external worker to operate.

File storage 2 buckets

community-images is public with a 5 MB cap. message-attachments is private: its policies require the first segment of an object's path to equal the uploader's user id, so nobody can write into another resident's folder, and reads additionally require a message that actually carries the file.

Identity GoTrue

Email/password, with optional TOTP multi-factor that residents enrol from their profile. profiles is 1:1 with auth.users; an admin who has enrolled a factor must clear a step-up challenge before the portal opens.

How a file is authorized

Storage is the one place where the authorization rule is written against a string rather than a column, so it is worth seeing in full. The two buckets take deliberately opposite postures, and the private one’s read rule is more subtle than it looks.

community-images public  ·  5 MB cap  ·  avatars, post and amenity photos read  anyone holding the URL — the bucket is public by design write  any signed-in user, to any path in the bucket delete  only inside your own folder Write is the one rule here without a path check, so a user can put a file in someone else’s folder. message-attachments private  ·  5 MB cap  ·  files sent in a 1:1 thread read  your own folder, or a message you can see carries it write  only into the folder named for your own user id delete  only inside your own folder Nothing is public: every read is evaluated, every time, against the rule below. THE TRICK — THE PATH IS THE OWNERSHIP CLAIM, IN 4 OF THE 6 RULES (storage.foldername(name))[1] = auth.uid()::text An object is just a key. Requiring its first segment to equal the caller’s own id turns the key into a statement of ownership the database can check — no join, no metadata table. 30000000-…-0001/quote.pdf  ← only that user may write here And the read rule delegates rather than repeats EXISTS (SELECT 1 FROM messages m WHERE   m.attachment_url = objects.name …) Note what that subquery never mentions: the reader. It asks only whether a message carries this file. WHY THAT IS SAFE, AND HOW WE KNOW A subquery inside a policy is still subject to the referenced table’s own RLS. messages has it, so “a message carries this file” silently means “a message I am allowed to read carries this file”. The storage rule inherits the conversation rule instead of restating it — which is why the two can never disagree, and why a property manager’s moderation access reaches attachments too, without anyone writing that down twice. Verified in the database rather than reasoned about, by inserting an attachment and evaluating the predicate as three different people: sender → true recipient → true outsider → false Eight integration tests drive the real Storage API over HTTP as well, so both the rule and the path it guards are covered.
Six policies, two buckets, and one idea: let the file system’s own naming carry the ownership claim, then borrow the rest from the table that already knows who may see what.

Environments and ports

SurfaceLocalPlanned production target
Resident web:3000Vercel or equivalent Node host
Admin portal:3001Vercel or equivalent Node host
Mobile:8081 dev · :8082 web previewEAS build → App Store / Play
Supabase API Kong:54321Hosted Supabase project
Postgres:54322 · pooler :54329Managed, transaction pooling
Studio / Mail:54323 · :54324Supabase dashboard

Local development runs the real Supabase stack under Docker — twelve containers, including Storage, Realtime, GoTrue and the mail catcher — so the local column is a genuine mirror of the hosted one rather than an approximation. That is why the integration suite can assert on storage policies and realtime channels at all.

One trap worth knowing before you debug anything. A no-Docker fallback exists at dev_workflow/architecture/local-supabase.sh — native PostgreSQL, PostgREST, and a small Node process standing in for /auth/v1 — for machines that cannot run Docker. It must never run alongside the real stack. It binds 127.0.0.1:54321 on IPv4 while Kong binds the IPv6 wildcard, so when both are up the fallback silently wins and becomes the database the apps talk to. That has already produced a feed showing ten polls where the real database had three, a “realtime is broken” false alarm (the fallback has no Realtime), and a storage test failing for want of a Storage API. The preflight script now fails loudly when the port is shadowed, which is the only reliable way to notice.

No production environment exists yet. The right-hand column is the documented plan (deployment_plan.md): hosted Supabase, Vercel for the two web apps, EAS with TestFlight and Play internal testing for mobile. Both web apps already expose api/health for hosted smoke checks. The DigitalOcean droplet behind starwood-community.com serves the stakeholder preview over nginx with Let's Encrypt TLS — a static host, not an application environment.

07Operations, observability and data rights

The parts that matter once real residents are on the platform rather than seed data.

Structured event log app_logs

Both a client (logEvent) and a server (logEventServer) helper write structured events — action, entity, property, level, error — from every surface into one table. Reading it is tightly held: the viewer function refuses any role but system_admin, and a separate function returns a resident only their own entries. Nothing else can touch the table at all. This is how a production incident gets diagnosed today.

Notifications in-app

A notifications table fans out from domain events, with per-category notification_preferences each resident controls. Delivery is in-app; push registration is scaffolded but not yet provisioned (see gaps).

Resident data rights GDPR-shaped

A resident can export their data and delete their account from their own profile. Deletion runs through delete_own_account(), which re-checks the account password before it will proceed and is revoked from anon — so a stolen session alone cannot destroy an account.

Load shedding middleware

The admin portal's middleware caps concurrent authentication work and sheds beyond it rather than queueing, so a burst of traffic degrades predictably instead of exhausting the auth path. Postgres sits behind a transaction pooler.

What “delete my account” actually does

Both halves of a resident’s data rights are implemented, and the erasure half is more interesting than it looks: the behaviour is not written in a function, it is spread across 265 foreign keys, and one of them stops the whole thing.

Portability  GDPR Article 20 GET /api/account/data-export A server route, not a database function, because the answer spans 17 tables: profile and interests, posts, comments and likes, events and RSVPs, messages, connections, work orders and their activity, bookings, poll votes, notifications, logs. Erasure  GDPR Article 17 delete_own_account(password) Re-checks the account password against its bcrypt hash before it will proceed, so a stolen session alone cannot destroy an account. Revoked from anon. It deletes one row — the auth.users record — and the schema decides the rest. WHAT THE SCHEMA DECIDES — 265 FOREIGN KEYS POINT AT A PROFILE 70 ON DELETE CASCADE The resident’s own content goes with them — posts, votes, bookings, RSVPs. 181 ON DELETE SET NULL The record survives without the person. A work order’s history stays readable. 10 ON DELETE RESTRICT These refuse. The delete fails and the account stays. Plus 6 NO ACTION. ONE OF THEM USED TO BE REACHABLE BY A RESIDENT All ten remaining RESTRICTs sit on actor columns — issued_by on emergency broadcasts, inspector_id on move inspections, recorded_by on board decisions — which a resident never fills. Until migration 266 there was an eleventh that was not: safety_alerts.resident_id  RESTRICT  →  SET NULL A resident who had ever raised a safety alert could not delete their account; the delete failed with a raw constraint error. The row now survives and the personal link goes — anonymise rather than delete, which is already how the same table treats acknowledged_by and resolved_by. A building’s safety history should not vanish because somebody closed their account. The check that guards this now asserts the invariant rather than the instance: no RESTRICT reference to a profile on a column a resident can fill.
Erasure behaviour is emergent here — nobody wrote “what happens to a departing resident” in one place, and the answer is the sum of 265 independent decisions made one migration at a time. That is worth knowing before someone exercises the right.
Schema changes are forward-only. 263 numbered SQL migrations apply in order; there are no down migrations. A correction is written as a new migration that supersedes the old one — which is why several files in the sequence exist purely to tighten a policy or a constraint that shipped too loose.

08How correctness is defended

Four layers, each catching what the layer above cannot.

Where a rule can live

Every section above is really one question asked repeatedly: given a rule, which layer should hold it? The layers are not interchangeable — each can express something the one above cannot, and each costs something the one above does not. This is the whole schema counted by where its rules ended up.

1  Client validation  51 Zod schemas in 21 files Turns a mistake into a sentence a resident can act on. Cannot refuse anything — the REST API is reachable without the UI, so this layer is a courtesy. 2  Declarative constraints  1,788 NOT NULL · 1,244 CHECK · 313 unique indexes · 556 foreign keys The only layer that holds under concurrency for free: two transactions cannot both win a unique index. Prefer this whenever the rule fits. Cannot see another row. “No overlapping booking” and “under 100 points this week” are sums across rows, so no constraint expresses them. 3  Triggers  194 Can read the rest of the table and refuse, and — the point — run for every caller, so a rule here cannot be skipped by writing to the table directly. Cannot serialise. A trigger that counts still reads before it writes, so two concurrent inserts can both pass it. That is 5.1 and 5.11. 4  Row-level security  798 policies on 222 tables Decides, per row and per role, what exists at all. Invisible to the client, applied to every query including one inside another policy — which is how storage borrows the messaging rule. Cannot express a count, and cannot return a number the caller is not allowed to see the rows behind. 5  SECURITY DEFINER functions  254 The escape hatch: multi-row atomicity, aggregates over rows the caller cannot read, and rules that depend on another table’s configuration. Costs the floor. It runs as the owner, so RLS no longer applies inside it and every check must be rewritten by hand — which is exactly how 5.12 happened. The underlying table keeps its policies, so a client that bypasses the function still hits the wall. The function is a door, never a hole in the fence. AND THE RUNG BELOW THE LAST ONE Of those 254 functions, 29 take a lock before they count — 27 a row lock, and award_points and redeem_reward an advisory lock as well, because their ceiling is per resident rather than per row. Those 29 are precisely the rules that could not be constraints: amenity capacity, RSVP capacity, waitlist promotion, storage assignment, point windows, reward stock. Everywhere else, a constraint is doing the work and no lock is needed.
Read top to bottom this is a ladder of increasing power and decreasing safety. The discipline the page argues for is to take the highest rung the rule fits on — and to notice that only 29 rules in the entire system genuinely needed the bottom one.

The failure this document is organised against

One pattern accounts for most of what went wrong in this codebase, and it is not bugs in features. It is a check that passes on the exact condition it was written to catch. Nine of them surfaced in a single day, written by six different people, and none announced itself — each looked like working machinery right up to the moment somebody measured it.

THE CHECK WAS MEANT TO CATCH WHY IT PASSED ANYWAY REVOKE .. FROM PUBLIC anon reaching an RPC Supabase grants anon by name; PUBLIC is a different grant IF x != auth.uid() someone acting as another anon makes it NULL, and a NULL branch is not taken channel.subscribe() realtime being wired up reports “Subscribed” against an empty publication feature_* toggles a manager switching a feature off the form saved the column; nothing read it for 149 migrations expect(body).toContain(‘404’) a broken Perks route Next inlines its flight payload, so every page contains it testMatch on a spec file that same route, at all no project named the file, so it had never run once server newer than BUILD_ID a stale or half-finished build stat failed → 0, and “newer than 0” is always true [^;]{0,400} in a parser every discarded error nine statements were longer, and got dropped not counted grep for the page’s claim a figure going stale matched the sentence making the claim, not the number in it What they have in common In eight of the nine the check never formed an opinion about its subject at all, and silence was read as assent: passing and not-running look identical from outside. The ninth is the odd one — the parser answered, just wrongly, which is why a second parser caught it and watching for silence would not have. Two failures, two instruments. Eight of these wanted make it fail once. The ninth wanted build it twice — a second implementation, disagreeing. Neither substitutes for the other: silence-watching sits quietly while a confident wrong number goes past, and a second opinion is wasted on a check that says nothing. The habit that would have caught them Not review — all nine survived that. Make each check fail once, deliberately, before trusting it. Move the BUILD_ID aside; delete the fix and watch the test go red; call the function as anon. A check you have never seen fail is a claim, not a control. Necessary, not sufficient. The Perks assertion would have failed the first time it ran — on a working page, because the payload contains “404”. Seeing it go red proves it can discriminate, not that it discriminates on the axis you meant. The likely fix would have been to weaken it. You still have to know why it is red. This is why every figure on this page is re-derived rather than written down. Two of the nine are in the checking tool on this page, which is the strongest evidence available that the discipline is necessary rather than decorative: the thing built to stop figures rotting shipped two checks that could not have caught their own subject. A number nobody re-computes is a number nobody is checking, however carefully it was written down.
Six authors, one repository, one day. The count is not a claim about this team — it is what happens whenever a check is written and never made to fail on purpose.
The same defect points both ways, and the second direction is harder to see. Every row above is a check that could not fail. The companion mistake is a check that does fail and gets explained away — or one that fails again for a new reason and is read as confirmation. Both happened here on the same day, in opposite directions, and the common error is the same: treating a second observation as independent when it is not.
  • A real bug attributed to a bad environment. Two end-to-end failures survived the removal of every environmental cause, and this page still called them a navigation timeout for a day. They were the authError || !user defect in 5.4. The environment was genuinely broken the whole time, which is what made the excuse so durable — every explanation offered was true.
  • An environmental artifact nearly attributed to a real bug. An integration run showed a capacity trigger failing to block a waitlist→going update, which would be a genuine bypass. Re-running that test alone failed again, which looked like confirmation. It was not: the test depends on the one before it to create the row it updates, so in isolation the UPDATE matches nothing, returns no error, and fails for an unrelated reason. The full block passes.
Isolation is not a neutral instrument. Re-running a failure on its own is the standard way to separate code from environment, and in an order-dependent block it manufactures a different failure wearing the same clothes. What settles the question is a signal that can only mean one thing — failures moving to different tests across runs is environmental in a way no single re-run can establish.
LayerScaleWhat it catches
Unit & component
Vitest · React Testing Library		433 files
9,539 tests		Pure domain logic and rendering, with no database. Fast enough to run on every change.
Source contract
assertions over source text		within the aboveThat a security guard still exists — for example that a query goes through the hardened RPC and not a direct table write. Cheap insurance against a well-meaning refactor.
Integration
Vitest against local Supabase		37 files
1,286 tests		RLS behaviour through the real REST API, as each role. This is where tenancy and privacy rules are proven.
End-to-end
Playwright		3 specs · 3 projects
47 tests		Full user journeys on both web surfaces, plus a concurrent-load project.
A worked example. Polls recently gained free-text answers with an admin toggle to hide who wrote them. Hiding the author in the read path was not enough: a write-in answer is backed by a vote for a visible “Other” option, and vote rows were readable property-wide — so one extra query re-identified the author. The fix hides individual votes only on polls that asked for anonymity, and moves tallies into a counts-only function. It is covered by 87 assertions executed against a throwaway Postgres built from the real migrations; deleting the fix makes them fail with the leaked user id, which is how we know the tests bite.

09Known gaps

Stated plainly, because they are the things worth deciding on.

CI covers three layers of four, and installs without a lockfile. .github/workflows/ci.yml runs on every push and pull request: type-check and all seven unit suites, a full replay of every migration plus seed.sql against a real PostgreSQL, and — on a dedicated runner — the whole browser suite against a real Supabase started from those same migrations. That is more automation than this page could claim a day ago. The gap left is the one that matters most: the integration suites, which are what actually prove the tenancy and privacy rules, still run nowhere but a laptop. The browser job shows the blocker is surmountable — it already starts Supabase on the runner — so the remaining work is pointing a job at npm run test:integration rather than solving anything new. And because package-lock.json is gitignored, CI installs with npm install rather than npm ci and resolves dependencies fresh on every run, so a transitive update can turn a build red with no change to this repository. That is not hypothetical: it is why the workflow pins Node 22 — realtime-js began requiring a native WebSocket, and only a stale local node_modules was hiding it.
Mobile release path is not wired up. apps/mobile/eas.json does not exist and the Expo project id in app.json is empty, so no build can be produced yet. Push-notification registration is scaffolded but not provisioned for production.
Where each layer actually stands. The suites are 9,539 unit tests across 433 files, 1,286 integration tests across 37 files, and 47 end-to-end tests. The unit layer could not answer the same way twice, and the cause turned out to be neither contention in the abstract nor product behaviour. The first test to import a module pays the whole cost of transforming it and its dependency graph; under a full parallel sweep that one-off cost ran past vitest's 5-second default and failed whichever test was scheduled first, in whichever file lost the race. Warming one file only moved the failure to the next, so the ceiling itself was the problem. Mobile showed it first — 2, 2 and 0 failures across identical runs, then six consecutive clean sweeps once the timeout was raised. The instructive part is what came after: consumer-web passes in full when run on its own and later returned 10 failures inside a loaded sweep, every one “timed out in 5000ms” and each in a different file. Same class, different suite, and the fix had only been applied to mobile. All seven suite configurations now carry it, and a sweep of all seven is green. The seventh is a re-run rather than an addition, which is worth stating because summing the seven is the obvious thing to do and gives the wrong answer: vitest.source-contracts.config.ts selects 52 files that the mobile config’s own lib/__tests__/** glob already covers, so its 993 checks are 993 of the 1,176 the sixth run reported, not 993 more. Compared by test name, all 993 appear in the sixth run and none is unique to the seventh. Distinct: 9,539 across 433 files. A naive sum reports 10,532 across 485, which is that same 993 counted twice. Raising a ceiling weakens no assertion — it stops timing a transform — and a genuinely hung test still fails.
The layer we lean on hardest reports failures badly. Integration is where tenancy and privacy are actually proven, and a great many of its call sites destructure { data } and throw the error away. When one fails you get the assertion’s disappointment (expected null to be false) instead of the database’s explanation — the difference between a minute and an hour. Which of them matter divides cleanly: 109 RPC calls and 94 writes lose a real message, because a raised function and a refused INSERT both put the diagnosis in error and null in data. The remaining 424 are reads, where an RLS denial returns empty data and no error at all, so there is nothing to discard. That leaves 203 sites worth changing rather than the six hundred a blunt count suggests, and they concentrate: five files hold half the RPC ones. Do the writes first — a refused DELETE returns [], which is indistinguishable from the row not existing or the filter being wrong, so a test meaning “this was refused” can pass for the right reason and for three wrong ones. This is a diagnostics problem, not a correctness one — every one of these tests still fails when it should.
What the other two layers do. Integration is the most valuable and, as of this evening, the one demonstrating why it needs a pipeline. It exercises the real Supabase stack under Docker, which is what lets it assert on things a bare database cannot reach — eight of its tests drive the Storage HTTP API directly, including the one that matters most, proving an attacker cannot reach another resident’s attachment by inserting a self-authored message pointing at their path. It runs 1,286 of 1,286, and it went briefly to 1,280 this evening in a way worth keeping on the record. A seed change made an amenity open rather than bookable — correctly, the customer had asked for that distinction — while six booking tests still named that amenity by id. Nothing was wrong with the product and nothing was wrong with the tests; a constant went stale, and it was resolved on the seed side because a ten-desk co-working room is something a resident should be able to book, one open amenity being enough to show the contrast.
What that incident actually exposed. The commit went green through all three CI jobs and broke six integration tests, and the only reason it surfaced within the hour is that somebody ran the suite by hand. The gap is narrower than “integration is not in CI”, and worth stating precisely: seed.sql is an input to the integration suite, and the only job that touches it checks that it loads — not that anything still passes against it. A seed edit is exactly the class of change that clears one and breaks the other. Until an integration job exists, every seed change is unverified against the layer that proves tenancy and privacy. End-to-end is now the layer with the most automation behind it: CI runs all 47 on a dedicated runner against a real Supabase started from these migrations, and it passes there. Locally it is 47 of 47 on a quiet machine and less on a busy one. Getting a trustworthy number out of it took a day, and the way I got it wrong is the more useful half. Most of the variation was environment. One run of 34 was an admin build carrying a complete set of manifests and no BUILD_ID: next start then serves a half-built app, every authenticated admin test bounces to /login, and the failure count is a signature rather than a symptom. Another was the dev servers being killed underneath the run. Underneath both sat memory rather than CPU, described below. But not all of it, and I said otherwise. After each environmental cause was removed, two failures persisted in the concurrent-load project, and this page called them a 60-second navigation timeout — environment again. They were a real defect. Every protected page resolved the caller with authError || !user and redirected to /login on either, so a lookup that merely failed was indistinguishable from a session that was genuinely gone. supabase-js sets no timeout on its fetch, so GoTrue under pressure answers slowly or drops the connection, and the page signed out a resident who was signed in. Four simultaneous sign-ins reproduce it. The fix asks twice before believing a failure — a revoked session fails identically both times and still redirects, a blip resolves on the second ask — and it had to be applied to all 34 pages carrying the pattern, because the bounce lands on whichever page the resident opens next. So the honest lesson is sharper than the one I first drew. An end-to-end failure is a claim about the environment or the code, nothing in the suite distinguishes them, and a genuinely bad environment is the most effective way to dismiss a real bug — because every environmental explanation you offer is also true.
And the cause underneath both of those turned out to be memory. Not CPU, which is the metric everyone reaches for. On this machine, macOS swapping stalls the Docker VM the whole stack runs inside, and the symptoms arrive in an order worth knowing because the useful ones come first and the alarming ones come last. Measured over two days on one machine, so treat it as a shape rather than a law:
  1. Warm route times. A second-pass render over 1.5s, against 300ms–1.3s healthy. This moves first — /home was measured at 8.6s while everything else still looked fine.
  2. PostgREST logging Thread killed by timeout manager. The best signal of the four, because it is server-side and needs no test running to produce it.
  3. GoTrue losing its database connection, and /auth/v1/token returning 504. By here it is visible to whoever is using the app.
  4. Playwright reporting content that never appeared. Last, and the least informative: it reads as a product fault, which is exactly how it costs an hour.
The trap is that the first and last both look like the product being slow or broken, which is why the passive one is the one to name in a runbook. And the reason this is not simply “the machine was busy”: load average did not distinguish the two states. At the same load, with swap under control, those same routes measured 304ms to 1.3s; with swap at 98% they measured 3 to 9 seconds and auth intermittently 504’d. A CPU-only health check passes in both.
What the tests caught, and what they missed. Getting here meant fixing the product as well as the tests: hidden posts had stopped being hidden after a later migration recreated the read policy without the moderation clause (see 5.10); a lease-renewal audit trigger ran as the caller and so blocked every property manager from creating a renewal; residents could not withdraw from an event once it was cancelled; the moves and deliveries notification preferences were never consulted; and the weekly-digest function rejected the scheduled job meant to call it. One integration file had never executed at all — a block comment containing A*/V* closed itself early and the parser dropped the file, hiding 31 tests. Against that, the defects in 5.11, 5.12 and 5.13 were found by interrogating a running database, not by any suite: 9,539 unit tests had nothing to say about who may call a function, because they never call one. The notification bug in 5.6 is the sharpest example — both triggers worked, wrote correct rows, and would satisfy any test asserting that a notification appears. What was wrong is that one never failed to appear, and nothing was asserting an absence. That is the honest limit of this pyramid, and the reason the integration layer is the one worth growing.
The anon grant is still open by default on most SECURITY DEFINER functions. Of the 254 SECURITY DEFINER functions, 146 are still executable by the unauthenticated anon role at the grant layer, 92 of them directly callable rather than trigger bodies. Every one that is safe today is safe because its own body checks the caller — one layer, not two, and 5.12 is what happens when that layer has a typo in it. The four functions proven exploitable are closed, and the points RPCs before them, but each was closed individually after being found. The right end state is the inverse: revoke from PUBLIC and anon by default, then grant back the handful that genuinely serve signed-out callers. That is a mechanical change to roughly 90 functions plus a test that fails when a new migration reintroduces the grant, and it would retire this entire class rather than the four instances of it.
Residents are not told that staff can read their messages. A property manager can read every 1:1 thread in their property. That is deliberate — the schema labels the policy "moderation" — and there are good reasons to want it. But nothing in the resident-facing app says so, and a neighbour-to-neighbour chat is exactly where people assume privacy. This is a disclosure decision that should be made on purpose rather than inherited from a comment in a migration, and in some jurisdictions it is not only a product choice.
Nothing leaves the building by email. The weekly digest computes correctly and raises an in-app notification, but its rows sit at email_status = 'pending' waiting for an SMTP worker that does not exist in the repo. Combined with the unprovisioned push channel above, that means every notification today requires the resident to open the app — which caps what a digest, or an urgent announcement, can actually achieve.
Two local stacks, and only one of them is the platform. Development runs real Supabase under Docker, which is what the integration suite asserts against. A no-Docker fallback (dev_workflow/architecture/local-supabase.sh) exists for machines that cannot run Docker: native PostgreSQL, PostgREST, and a Node process standing in for /auth/v1. It is enough for RLS work and nothing more — no Storage, no Realtime — and running it beside the real stack shadows port 54321 and silently replaces the demo database, as described in section 06. Treat it as a last resort, not an equivalent. The schema checks are independent of both: verify-migrations.sh and verify-figures.sh each build their own throwaway PostgreSQL and replay all 263 migrations into it.

What I would do next, in order