Designed a function‑first PostgreSQL data layer
Designed a PostgreSQL function‑first data layer — 1,275 stored
functions across 34 schemas — so every read and write goes through a
function the database can grant, rather than through a table.
2025

Situation. Business logic has a way of leaking. A bit ends up in the
API, a bit in some SQL a handler runs inline, and before long the
same rule is written two or three slightly different ways and
there's nowhere you can point to and say "this is what the system
does with its data." That's how bugs and security holes get in.

Task. The goal was one home for all of it: every read and write
going through the database, the API a thin adapter that doesn't know
the business rules, and the whole thing lockable down tight.

Action. The data layer is function‑first. The schema is split by
domain — identity, organization, review, message, notification and
29 more, 34 in all — and every operation the app can do is one of
1,275 PostgreSQL functions it calls; there's no direct table access
from Go at all. Then the database enforces it. The role the API logs
in as, mariner, has EXECUTE on the app functions and USAGE on the
schemas and nothing else — no SELECT, no INSERT, no way to touch a
table directly — which comes to roughly 4,000 explicit grants rather
than a blanket one. The functions run SECURITY DEFINER, owned by a
separate non‑login function_owner role with a pinned search_path,
and the superuser account stays reserved for migrations and cron,
well away from the running app.

Result. The logic lives in one place you can actually audit, the API
stays thin and boring in the good way, and the access boundary is
enforced by Postgres itself rather than by everyone remembering the
rules. If the API were somehow compromised, it still couldn't do
anything the functions don't allow. At 1,275 functions the
discipline costs something real — a new field is a migration and a
function change, not a line in a query — and that friction is the
price of the boundary holding.

Categories:
1Platform Architecture	/categories/platform-architecture/	data.engineer.company	70
1Backend Engineering	/categories/backend/	data.engineer.company	70
1Databases	/categories/databases/	data.engineer.company	70
1PostgreSQL	/categories/postgresql/	data.engineer.company	70
1SQL	/categories/sql/	data.engineer.company	70
1Data Governance	/categories/data-governance/	data.engineer.company	70
1Security	/categories/security/	data.engineer.company	70

Services:
1Platform & Solution Architecture	/services/platform-architecture/	data.engineer.company	70
1Backend & API Development	/services/backend-development/	data.engineer.company	70
1Database Design & Modeling	/services/database-design/	data.engineer.company	70
1Data Governance & Quality	/services/data-governance/	data.engineer.company	70

hThis page on the web	URL:https://data.engineer.company/portfolio/designed-a-function-first-postgresql-data-layer/	data.engineer.company	70
