Notes and recommendations

Findings from reading the schema as decided, not the database as deployed. Each one was verified against the migration SQL this explorer is generated from, and the constraint — or its absence — is cited so you can check the claim rather than take it on trust. Re-verified 2026-09-30 against gik-platform develop at bbacfcde, the snapshot master schema 1.2 reads; every count below is from that SQL.

Findings 1 and 3 have since been fixed on develop, both substantially as recommended here, and are struck through rather than deleted — the reasoning is the record of why the change was made. Finding 4's membership half was settled by taking this document's fallback option (rename, don't scope). main (31262c9f) still carries the 1.1 shapes, so all three fixes are merged and unreleased.

Ordered by what we would fix first. Of the three we would have raised in a meeting, two are now closed; the rest are worth a ticket each.

1. ~~Every profile, including email, is readable by the anonymous role~~ — fixed on develop

> Resolved in master schema 1.2 (gik-platform develop bbacfcde, carried by > migration 20260918180100_profiles_column_grants.sql). anon is revoked > outright, the SELECT policy is now TO "authenticated", and authenticated > holds a column-scoped GRANT SELECT omitting deleted_metadata — which closes > the deleted-user half of this finding as well. The fix went further than the > recommendation below: a column grant rather than a view, so select("*") on > this table now fails with 42501 and every caller must name its columns. > Unreleased — main still carries the old grants.

profiles granted full table privileges to anon, and its SELECT policy carried no role restriction and no predicate. The two statements, as they stood at 1.1:

• GRANT ALL ON TABLE "public"."profiles" TO "anon";
• CREATE POLICY "auth" ON "public"."profiles" FOR SELECT USING (true);

A policy written without a TO clause applies to PUBLIC — every role, including anon. Combined with USING (true), any caller holding the project's anonymous key can read every profile row, email included. Nothing about this requires an account.

It is worse for deleted users. profiles uses tombstones rather than deletion: the visible PII columns are anonymised at delete time, and the original values are stashed in deleted_metadata on the same row. RLS is row-level, not column-level, so that column is readable under the very same policy. A user who deletes their account keeps their real name and email in a field the anonymous role can read.

Recommendation. Decide what a profile is meant to expose publicly. If the answer is a display name and avatar — which is all the "resolve a name for attribution" argument actually needs — expose those columns through a view, or scope the policy TO authenticated and serve public attribution from a narrower surface. deleted_metadata should not be reachable through a row policy at all; move it somewhere only service_role can read.

Taken, in the second form: the policy was scoped TO authenticated, and deleted_metadata was put out of reach with a column grant instead of being moved.

2. Eligibility is not enforced anywhere in the database

organization_restrictions and resource_restrictions describe who may receive what. Nothing in the database makes those rows binding: there is no CHECK constraint, trigger, RLS policy, or function anywhere in the schema whose definition mentions eligibility. The restriction tables do carry RLS, but it governs who may read and write restriction rows — not whether a restriction is honoured when an offer or order is created.

The only enforcement is client-side, in application code, and it checks a single field. A direct API call, a service-role write, or a bug in one screen bypasses the rules entirely, and the database will accept the row.

Recommendation. Decide deliberately whether eligibility is a rule or a hint. If it is a rule it needs a database-layer gate — a trigger, or an RPC all writes go through — because a client-side check is unenforceable by construction. If it is a hint, say so in the schema documentation so the next engineer does not assume protection that is not there. Either answer is fine; the current state is that the model implies enforcement it does not have.

The 1.1 schema shows what the alternative looks like in this same codebase. The unlisted rule on resources (GIK-336) is held by two triggers that refuse or correct the write wherever it comes from, and create_offer_with_items re-validates inside its transaction what RLS cannot see, because RLS gates rows and not the relationships between them. Eligibility has none of that, and the new partner reads widen the gap: the category, state, disaster and faith filters that search_need_items_by_embedding and portfolio_matches_for_org accept are the partner's own choices, not the restriction rows anyone declared, so a supplier's portfolio can rank a need from an NPO its own resource_restrictions say may not receive from it.

3. ~~Six SECURITY DEFINER functions do not pin search_path~~ — fixed on develop

> Resolved in master schema 1.2 (gik-platform develop bbacfcde). All six > now carry SET search_path TO 'public'. Unreleased — main is still unpinned.

Of 39 SECURITY DEFINER functions at 1.1, 33 set search_path and six did not:

• matching.enqueue_match_run
• matching.process_pending_match_runs
• needs.create_need_with_items
• onboarding_rpc.stamp_email_on_session
• onboarding_rpc.lookup_organization_by_domain
• profiles.handle_auth_user_update

The three added since 1.0 — ensure_match_worker_secret, close_unlisted_resource_on_offer_close and search_need_items_by_embedding — all pin it, so these are the same six as before.

Such a function runs with its owner's privileges and bypasses the caller's RLS. Without a pinned search_path, an unqualified name inside it resolves against whatever the caller's search path happens to be — the standard privilege- escalation shape for this class of function.

Recommendation. Add SET search_path = '', or an explicit schema list, to all six, matching the thirty-three that already do it. Additive and low risk.

Taken, as an explicit SET search_path TO 'public' on all six.

4. Three tables are fully readable by every signed-in user

Each of these is TO authenticated USING (true) — any authenticated account reads every row, not only the rows relating to them:

• organizations — the full organisation directory.
• organization_restrictions — every organisation's eligibility rules.
• organization_members — every membership in the system. The policy was named Users can view their own memberships, which is not what it does; the name described an intent the predicate does not implement. Renamed organization_members_select_authenticated in 1.2 — the misdescription is gone, the platform-wide read is not.

This may be deliberate for a marketplace where participants need to see one another. It is worth confirming, because the membership policy's name suggests at least one of the three was not.

Recommendation. Confirm each is intended, and rename the membership policy to match its behaviour whichever way you decide. A policy whose name contradicts its predicate is a trap for the next person auditing this.

Half taken: the rename landed in 1.2. Whether the platform-wide read across all three tables is intended is still unconfirmed — and platform-recommendations.md §2 frames the rename as the fallback option, taken instead of scoping the policy. That is a live decision that appears to have been made by implementation rather than by a call, so it is worth recording deliberately rather than leaving it to be re-litigated.

5. organization_partners.status defaults to a value its own CHECK rejects

The column defaults to 'active'. Its CHECK admits only pending, accepted, and rejected. A plain INSERT relying on the default therefore fails outright.

This stays invisible only because the INSERT policy independently forces every application-written row to carry an explicit pending or accepted. It surfaces the moment anything inserts without naming a status — a migration, a backfill, a service-role script.

Recommendation. Change the default to 'pending', matching the sibling organization_invites.status, which already defaults that way.

6. Two foreign keys point at Supabase auth where every sibling points at profiles

The naming is inverted in both directions, which makes this easy to miss:

Every other actor column targets public.profiles — created_by (seven of them), uploaded_by, triggered_by, invited_by_profile_id, deleted_by, converted_by_profile_id, and a created_by_profile_id on a different table that resolves correctly.

The join still works, because a profile's primary key is its auth user's id. But the constraint does not require a profile row to exist, and its ON DELETE SET NULL fires when the auth user is deleted — while profiles deliberately uses tombstones rather than deletion. So these two columns follow a different lifecycle from the rest of the schema.

Recommendation. Repoint both at public.profiles. The column names already say that is what they meant.

7. Three tables permit duplicates the model treats as impossible

None has a unique index, so the database will accept a second row where the domain expects one:

• organization_followers — nothing prevents the same follow twice. organizations.followers_count, which this table is meant to summarise, is only as accurate as whatever recomputes it.
• organization_partners — nothing prevents two organisations accumulating more than one partnership row. A CHECK bars an org from partnering with itself; nothing bars a duplicate pair.
• organization_locations.is_primary — a plain boolean DEFAULT false NOT NULL with no guard. Two locations on one organisation can both claim primary.

Recommendation. Add a unique index to each: the follow pair, the partner pair, and a partial unique on (organization_id) WHERE is_primary. All three are additive and ship without application changes — though each needs a duplicate sweep first.

What the schema gets right

Worth stating, because these are the parts most often wrong:

• RLS is enabled on all 43 tables. No table is left ungoverned.
• api_keys uses column-level grants rather than the project-wide GRANT ALL, deliberately and with a comment explaining why, so the secret material is not reachable through the table grant.
• profiles now does the same (1.2), for the same reason and in the same shape: REVOKE first, then a column-scoped GRANT SELECT that omits deleted_metadata. What was a one-table exception is becoming the house pattern for any table holding a column that RLS, being row-level, cannot hide.
• Membership and admin checks are re-evaluated per query through helper functions rather than read from a cached permission, so a stale role row cannot grant access on its own.
• The unlisted invariant is enforced at the database boundary (1.1). Two triggers on resources refuse an unlisted row from anything but the offer path, refuse a catalog row becoming unlisted or an unlisted one becoming public, and hold a closed one closed; create_offer_with_items is SECURITY INVOKER, so RLS still governs every row it writes, and it re-checks membership, need ownership, self-offer and resource ownership inside the transaction. This is the pattern item 2 asks for.

A note on what this document is

These are observations about the schema, drawn from the migrations. They are not a penetration test, and they say nothing about the application code above the database or the project's Supabase settings, where some of these may already be handled. Items 1 and 2 are the ones we would ask about first: item 1 because it exposes data today without an account, item 2 because the model implies an enforcement it does not have.