feat(db): first_party_clients allow-list table + is_first_party_caller() helper

[bryanfawcett]

Purpose

Establish the data layer for the first-party allow-list that powers tenant-aware behaviour across Mzizi (the framework), the registry API, the MCP server, and @nyuchi/mzizi-cli. This issue is data-only — no API, no middleware, no behaviour change yet. #79 builds on top.

Context

The portal exposes everything publicly today. We're adding an opt-in flag (requires_auth, #80) that hints to third-party consumers that authentication is recommended. First-party clients (any brand inside the Bundu ecosystem — Nyuchi, Mukoko, Shamwari, Nhimbe, etc.) bypass that hint entirely. Bypass is enforced at the API/MCP layer based on a token check against this allow-list, not in RLS — RLS can't distinguish caller-app on its own.

This is not a gate. The DB still exposes everything to anon and authenticated. The allow-list is one input to a tenant-aware decision made above the DB.

Convention alignment: the brand column on this table uses the same brand identities as brand_ecosystem.name — so a first-party client is identified as belonging to a specific ecosystem brand (nyuchi, mukoko, etc.), and that identity ties back to the canonical ownership convention. External adopters of Mzizi who deploy their own infrastructure would have their own equivalent allow-list; this one is for the Bundu ecosystem only.

Schema

CREATE TABLE public.first_party_clients (
  client_id     text PRIMARY KEY,
  brand         text NOT NULL REFERENCES public.brand_ecosystem(name),
  api_key_hash  text NOT NULL UNIQUE,    -- SHA-256 hex of the bearer token
  description   text,
  created_at    timestamptz NOT NULL DEFAULT now(),
  last_used_at  timestamptz,
  revoked_at    timestamptz
);

COMMENT ON TABLE public.first_party_clients IS
  'Allow-list of first-party API clients inside the Bundu ecosystem. A bearer token whose SHA-256 hash matches a non-revoked row grants the request a first-party tenant claim that bypasses requires_auth hints. Plain tokens are never stored. The brand column references brand_ecosystem.name so first-party identity is tied to the canonical ownership convention.';

CREATE INDEX first_party_clients_api_key_hash_idx
  ON public.first_party_clients(api_key_hash) WHERE revoked_at IS NULL;
CREATE INDEX first_party_clients_brand_idx
  ON public.first_party_clients(brand) WHERE revoked_at IS NULL;

Note: the FK to brand_ecosystem(name) enforces that first-party clients can only be registered for known ecosystem brands. Adding a new brand to brand_ecosystem (e.g. a new mini-app under Mukoko) makes it valid as a first-party identity automatically.

RLS — service-role only

ALTER TABLE public.first_party_clients ENABLE ROW LEVEL SECURITY;

-- No anon/authenticated policy. Default deny. Only service_role can read or write.
-- The is_first_party_caller() helper below is SECURITY DEFINER so callers
-- never need direct SELECT on this table.

REVOKE ALL ON public.first_party_clients FROM anon, authenticated;

SQL helper — is_first_party_caller(p_api_key_hash text)

CREATE OR REPLACE FUNCTION public.is_first_party_caller(p_api_key_hash text)
RETURNS TABLE(
  is_first_party boolean,
  client_id      text,
  brand          text
)
LANGUAGE sql STABLE
SECURITY DEFINER
SET search_path TO 'public'
AS $function$
  SELECT
    EXISTS (
      SELECT 1 FROM public.first_party_clients
      WHERE api_key_hash = p_api_key_hash
        AND revoked_at IS NULL
    ) AS is_first_party,
    (SELECT client_id FROM public.first_party_clients
       WHERE api_key_hash = p_api_key_hash AND revoked_at IS NULL LIMIT 1) AS client_id,
    (SELECT brand FROM public.first_party_clients
       WHERE api_key_hash = p_api_key_hash AND revoked_at IS NULL LIMIT 1) AS brand;
$function$;

REVOKE ALL ON FUNCTION public.is_first_party_caller(text) FROM PUBLIC;
GRANT EXECUTE ON FUNCTION public.is_first_party_caller(text) TO anon, authenticated;

SECURITY DEFINER so callers resolve a hash without ever reading the underlying table directly. Function only accepts pre-hashed input — never the raw token. Hashing happens in the API/MCP middleware before the call.

touch_first_party_client_last_used(p_client_id text) — best-effort tracking

CREATE OR REPLACE FUNCTION public.touch_first_party_client_last_used(p_client_id text)
RETURNS void
LANGUAGE sql
SECURITY DEFINER
SET search_path TO 'public'
AS $function$
  UPDATE public.first_party_clients
     SET last_used_at = now()
   WHERE client_id = p_client_id;
$function$;

REVOKE ALL ON FUNCTION public.touch_first_party_client_last_used(text) FROM PUBLIC;
GRANT EXECUTE ON FUNCTION public.touch_first_party_client_last_used(text) TO anon, authenticated;

Called fire-and-forget by the middleware after a successful first-party check. Useful for stale-key detection.

Initial allow-list seed

Bryan's CLI key seeded on first-party DX setup. Operationally, new keys are added via service-role INSERT — not via API for now. Key management UI is a future issue, out of scope.

-- Example, seeded once we have the actual key hash:
-- INSERT INTO public.first_party_clients (client_id, brand, api_key_hash, description)
-- VALUES ('nyuchi-cli-bryan', 'nyuchi', '<sha256-hex>', 'Bryan local @nyuchi/mzizi-cli');

Register in mcp_tool_registry

These tools should appear in the registry so mcp_describe lists them, and the dispatcher (#82) can route to them:

INSERT INTO public.mcp_tool_registry (tool_name, category, description, sql_function, stability, tool_kind, cache_ttl_seconds, added_in_version)
VALUES
  ('is_first_party_caller', 'auth',
   'Check whether a SHA-256-hashed bearer token corresponds to a registered first-party client. Returns is_first_party + client_id + brand.',
   'public.is_first_party_caller', 'stable', 'read', 60, '4.1.0'),
  ('touch_first_party_client_last_used', 'auth',
   'Record last-used timestamp on a first-party client. Fire-and-forget; never block the request on this.',
   'public.touch_first_party_client_last_used', 'stable', 'write', 0, '4.1.0')
ON CONFLICT (tool_name) DO UPDATE SET
  description = EXCLUDED.description, updated_at = now();

Acceptance criteria

• first_party_clients table exists with the schema above, RLS enabled, no anon/authenticated policies.
• FK to brand_ecosystem(name) enforces that brand values are canonical ecosystem brands.
• anon role cannot SELECT * FROM first_party_clients (verify via SET LOCAL ROLE anon).
• is_first_party_caller('<known-hash>') from anon returns is_first_party=true with the right client_id and brand.
• is_first_party_caller('<unknown-hash>') returns false, NULL, NULL.
• Revoked rows (revoked_at IS NOT NULL) are not matched.
• touch_first_party_client_last_used() updates last_used_at and is callable as anon.
• pg_proc.prosecdef is true on both functions; search_path is locked to 'public'.
• Both functions registered in mcp_tool_registry under category auth.

Out of scope

• API/MCP middleware that calls these helpers — feat(api+mcp): first-party tenant middleware (resolveTenant) + whoami MCP tool — auth moves to WorkOS #79.
• The requires_auth opt-in column itself — feat(api+mcp+db): components.requires_auth opt-in flag with first-party bypass + 401 reason contract #80.
• @nyuchi/mzizi-cli — feat(cli): @nyuchi/mzizi-cli — first-party CLI with login/logout/whoami/add/search/list #81.
• Self-service UI for managing keys — future.

Verification

SET LOCAL ROLE anon;
SELECT * FROM is_first_party_caller(encode(sha256('test-token'::bytea), 'hex'));
-- Expected: is_first_party=false, NULL, NULL

-- After seeding a real row:
SELECT * FROM is_first_party_caller(encode(sha256('<the-real-token>'::bytea), 'hex'));
-- Expected: is_first_party=true, client_id='nyuchi-cli-bryan', brand='nyuchi'

[linear]

FRD-27