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-03 against gik-platform develop at 843dfa1b, the snapshot master schema 1.1 reads; every count below is from that SQL.

Ordered by what we would fix first. The first three are the ones we would raise in a meeting; the rest are worth a ticket each.

1. Every profile, including email, is readable by the anonymous role

profiles grants full table privileges to anon, and its SELECT policy carries no role restriction and no predicate. The two statements, verbatim:

• 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.

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

Of 39 SECURITY DEFINER functions, 33 set search_path and six do 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.

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 is named Users can view their own memberships, which is not what it does; the name describes an intent the predicate does not implement.

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.

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.
• 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.