The shape of the problem
JD Marc runs nine departments — Procurement, Projects, HR, Finance, Logistics, Legal, IT, Client Relations, and Executive — and each one kept its own version of the truth. Spreadsheets that didn’t talk to each other, a shared inbox for anything that crossed department lines, and a reporting cycle that ran once an hour, if someone remembered to trigger it. Finance couldn’t see a procurement request until it was exported. HR couldn’t tell if an onboarding was stuck in IT without emailing to ask.
The brief was “one dashboard.” The actual problem underneath it was nine different data shapes that needed to live somewhere without nine migrations every time one of them changed — Procurement’s records look nothing like HR’s, and a rigid schema built for launch day would have needed surgery within a month.
The decision that shaped everything else
I built a single generic record layer instead of nine schemas: one module_records table, keyed by department and module, with the department-specific fields living in a jsonb column. Each department module declares its own field shape as configuration, not as a migration, and that config validates the record before it’s written.
The honest trade-off: I gave up some of Postgres’s native column-level type safety. What I got back was a tenth department costing zero migrations — just a new module definition. For a build with nine unknowns on day one, that traded correctly.
Access control at the row, not the screen
Permissions are enforced with row-level security policies in Postgres, not with conditionals in the interface. A department_memberships table maps each user to a department and a tier — Staff, HR, or Admin. A Staff row only ever sees records from its own department. HR sees across every department, but only for modules tagged HR-visible — onboarding status, not procurement pricing. Admin sees everything.
Because that boundary sits in the database, a bug in a React component can’t leak another department’s records. The frontend doesn’t enforce the rule; it just can’t see past it.
From hourly to sub-second
The original handoff between departments was the batch export — a job that ran once an hour and pushed a snapshot into a shared sheet. I replaced it with Supabase Realtime subscriptions on module_records, filtered by the same row-level policies that already govern reads, so a live update never has to be re-checked for permission — it was scoped before it left the database. Chat and WebRTC signalling for department-to-department handoffs sit on the same realtime infrastructure, rather than a second system bolted on beside it.
Three bugs that taught more than the launch did
The parts of this project worth talking about in an interview aren’t the features — they’re the three times production behaved in a way the code didn’t predict.
Writes that had worked minutes earlier started failing with “column not found” errors, intermittently, with no code change behind them. The cause wasn’t application logic — it was PostgREST’s schema cache going stale after a migration, serving a description of the table that no longer matched it. The fix was a NOTIFY pgrst, 'reload schema' call, and the real fix was folding that call into the deploy script so it stopped being a step a person could forget.
Early on, each widget on a department dashboard fired its own filtered query against module_records. A nine-widget page meant nine round trips, each one re-filtering data the previous query had already fetched most of. I moved to one indexed query per page load, with the UI slicing the result client-side — which also simplified the realtime layer to one subscription per page instead of nine.
Users kept getting silently signed out on refresh during long sessions — worse on a shaky connection, and worse still for the people who keep Procurement and Finance open in adjacent tabs, which this app actively encourages. Each tab had been managing its own token refresh independently; under a dropped connection they’d disagree with each other about whether the session was still valid. Centralising refresh logic so tabs share one source of truth for the session fixed it.
What it added up to
Sub-second reporting meant a Finance approval shows up the moment it’s submitted, not on the next hourly cycle. Row-level access meant onboarding a new department stopped being a security review every time. Automation on top of the shared schema removed about 40% of the manual handoff steps between departments — mostly re-entering data that already existed somewhere else in the system. None of that was the point of the project on day one; all of it came from the schema decision underneath it.
What I’d change next
A few modules — Finance approvals especially — have grown busy enough that they’re outgrowing “generic.” The next piece of work is promoting the highest-traffic modules out of the shared jsonb table into first-class typed tables with real foreign keys, now that their shape has actually stabilised. That’s not a regret about the original decision — the generic layer did exactly what a first version with nine unknowns needed it to do. It just isn’t the right shape forever, and knowing when to graduate out of it is part of the job.