Files
aevolution/migrations/0061_admin_read_views.sql
dingoandClaude Opus 5.5 54beceb823 feat(admin): read-only admin portal and API (ADR-0182 step 1, schema 61)
evo-admin-api serves the portal at srs.aeternalisgames.com and read-only JSON
for world 100 behind Keycloak (realm role aevolution-admin, audience
aevolution-admin, client aevolution-admin-portal): accounts with session,
status, lives, deaths, resets and mutation choices; one account's lives; the
world's regions; and a live 2D region view of cells, wildlife, pools and
bubbles. The portal is plain HTML/CSS/JS with PKCE sign-in, a strict CSP and
no third-party code.

- Schema 61: three bounded SECURITY DEFINER read functions, fenced in the
  privilege audit; no table or existing function changes.
- Cellular regions answer GET /admin/view on the ops port (NetworkPolicy:
  Prometheus and the admin portal only); CellularLives::extent_of reads the
  existing phenotype budget. No simulation, checkpoint or wire change.
- TokenVerifier takes an optional realm role and returns the email; the ops
  server's response bound is configurable (16 KiB default unchanged).
- DEV component admin-portal-dev (Deployment, Service, NetworkPolicies); the
  Ingress and the aevolution-admin-pg Secret are created out of band.
- CI scripts pinned to schema 60 now expect 61.

Co-Authored-By: Claude Opus 5.5 <noreply@anthropic.com>
2026-10-27 12:39:28 -04:00

83 lines
4.9 KiB
PL/PgSQL

-- Admin portal read views (ADR-0182, 2026-10-27). Three read-only, bounded SECURITY DEFINER
-- functions for the DEV admin API: a world's accounts with their lives and session control,
-- one account's lives, and a world's regions with their heads. Nothing is written; no table,
-- row, constraint or existing function changes. The runtime group may execute them; DEV's
-- admin API logs in within that group, as the grant broker does (ADR-0131).
-- Every account known to the world: Keycloak identities and accounts that reserved a life.
-- A life is a published, unrefused reservation; a death is an ended one. The control row is
-- the account's session claim (region and expiry), as the region last renewed it.
CREATE FUNCTION evolution.admin_accounts_v1(p_world evolution.u64)
RETURNS TABLE (account_id uuid, created_at timestamptz, issuer text, subject text,
control_region evolution.u64, control_expires timestamptz, lives bigint, deaths bigint,
active_life evolution.u64, active_region evolution.u64, mutation_choices bigint)
LANGUAGE sql STABLE SECURITY DEFINER SET search_path = pg_catalog, pg_temp AS $$
WITH accounts AS (
SELECT i.account_id FROM evolution.account_identities i
UNION
SELECT r.account_id FROM evolution.life_reservations r WHERE r.world_id = p_world
), lives AS (
SELECT r.account_id, r.identity, r.region_id, r.request_id, r.ended_commit
FROM evolution.life_reservations r
JOIN evolution.primitive_life_bindings b ON b.world_id = r.world_id
AND b.account_id = r.account_id AND b.request_id = r.request_id
WHERE r.world_id = p_world AND r.rejection = 0
)
SELECT a.account_id, i.created_at, i.issuer, i.subject, c.region_id, c.expires_at,
(SELECT count(*) FROM lives l WHERE l.account_id = a.account_id),
(SELECT count(*) FROM lives l WHERE l.account_id = a.account_id
AND l.ended_commit IS NOT NULL),
active.identity, active.region_id,
(SELECT count(*) FROM evolution.cellular_mutation_reservations m
WHERE m.world_id = p_world AND m.account_id = a.account_id)
FROM accounts a
LEFT JOIN evolution.account_identities i ON i.account_id = a.account_id
LEFT JOIN evolution.account_controls c ON c.world_id = p_world AND c.account_id = a.account_id
LEFT JOIN LATERAL (SELECT l.identity, l.region_id FROM lives l
WHERE l.account_id = a.account_id AND l.ended_commit IS NULL
ORDER BY l.request_id DESC LIMIT 1) active ON true
ORDER BY i.created_at NULLS LAST, a.account_id
LIMIT 10000
$$;
REVOKE ALL ON FUNCTION evolution.admin_accounts_v1(evolution.u64) FROM PUBLIC;
GRANT EXECUTE ON FUNCTION evolution.admin_accounts_v1(evolution.u64) TO :"runtime_role";
-- One account's life requests in order: published or not, refused or not, and when an
-- ended life's commit was published (its last tick and time).
CREATE FUNCTION evolution.admin_account_lives_v1(p_world evolution.u64, p_account uuid)
RETURNS TABLE (request_id evolution.u64, identity evolution.u64, region_id evolution.u64,
rejection smallint, born evolution.u64, ended_tick evolution.u64, ended_at timestamptz)
LANGUAGE sql STABLE SECURITY DEFINER SET search_path = pg_catalog, pg_temp AS $$
SELECT r.request_id, r.identity, r.region_id, r.rejection, b.born, e.end_tick, e.committed_at
FROM evolution.life_reservations r
LEFT JOIN evolution.primitive_life_bindings b ON b.world_id = r.world_id
AND b.account_id = r.account_id AND b.request_id = r.request_id
LEFT JOIN evolution.region_commits e ON e.commit_id = r.ended_commit
WHERE r.world_id = p_world AND r.account_id = p_account
ORDER BY r.request_id
LIMIT 1000
$$;
REVOKE ALL ON FUNCTION evolution.admin_account_lives_v1(evolution.u64, uuid) FROM PUBLIC;
GRANT EXECUTE ON FUNCTION evolution.admin_account_lives_v1(evolution.u64, uuid)
TO :"runtime_role";
-- A world's regions: lease (epoch, whether held and until when) and the head commit.
CREATE FUNCTION evolution.admin_regions_v1(p_world evolution.u64)
RETURNS TABLE (region_id evolution.u64, frame_id evolution.u64, lease_epoch evolution.u64,
lease_held boolean, lease_expires timestamptz, commit_sequence evolution.u64,
end_tick evolution.u64, committed_at timestamptz, build_identity text)
LANGUAGE sql STABLE SECURITY DEFINER SET search_path = pg_catalog, pg_temp AS $$
SELECT l.region_id, l.frame_id, l.epoch, l.holder IS NOT NULL, l.expires_at,
h.commit_sequence, h.end_tick, c.committed_at, encode(c.build_identity, 'hex')
FROM evolution.region_leases l
LEFT JOIN evolution.region_heads h ON h.world_id = l.world_id AND h.region_id = l.region_id
LEFT JOIN evolution.region_commits c ON c.commit_id = h.commit_id
WHERE l.world_id = p_world
ORDER BY l.region_id
LIMIT 1000
$$;
REVOKE ALL ON FUNCTION evolution.admin_regions_v1(evolution.u64) FROM PUBLIC;
GRANT EXECUTE ON FUNCTION evolution.admin_regions_v1(evolution.u64) TO :"runtime_role";
INSERT INTO evolution.schema_migrations(version,sha256) VALUES(61,:'migration_sha_61');