last1.id Mode 2A — Week 2 Gate A Report (W2A)¶

Date: 2026-08-24
Gate: A — Pre-migration verification (read-only)
Status: COMPLETE — STOP for Ryan Gate A approval before W2B (pg_dump / restore)
Supabase project: nztgswcpnwggymncrnyx (https://nztgswcpnwggymncrnyx.supabase.co)
Constraints honored: No Supabase writes, no dumps, no restore, no app deploy, no DNS/OAuth changes.

Executive summary¶

Gate A confirms the last1.id repo tracks all 71 migrations and that live Supabase holds non-zero test/dev data (not the “zero users” assumption in Y4). Public-schema row counts were captured via read-only PostgREST (service role). Auth-schema table-by-table counts, live RLS inventory (pg_policies), storage.* metadata, and supabase_migrations.schema_migrations history require direct Postgres read access, which is not present in BWS or Railway env vars today.

Recommendation for Gate A approval: Accept the REST/API audit as sufficient to authorize W2B. Gate B pg_dump uses the hosted Postgres host derived from BWS SUPABASE_URL / project ref nztgswcpnwggymncrnyx plus the Supabase database password (dashboard “Database password” field, not a separate “Database URL” setting). Pre-migration Supabase secrets are discarded at Week 7 teardown; no rotation required now.

Metric	Y1/Y11 audit expectation	Gate A finding
Repo migrations	71	71 (`Last-1-Enterprises/last1.id` @ `358f083`)
Public tables	~35	36 in repo; 35 PostgREST-exposed; 1 (`consent_audit_log`) queryable but not in OpenAPI
RLS-enabled tables	~32	32 in repo migrations; 37 `CREATE POLICY` statements
Auth users	“zero users” (Y4)	12 `auth.users` (GoTrue admin API)
Profile photos (Storage)	minimal	3 objects, 4,716 bytes total
`identity_profile_photos` rows	—	6 rows (3 with `storage_path`, 3 external/`image_url` only)

Access methods used (read-only)¶

Method	Result
Railway GraphQL → `SUPABASE_SERVICE_ROLE_KEY` (`sb_secret_…` format)	Works for PostgREST, Storage, GoTrue admin (Gate A + API paths)
BWS `SUPABASE_URL` (Last1.id lane, ref `nztgswcpnwggymncrnyx`)	Present — API base `https://nztgswcpnwggymncrnyx.supabase.co`; Postgres host for Gate B is `db.nztgswcpnwggymncrnyx.supabase.co` (derived, not a separate BWS key)
BWS legacy `SUPABASE_SERVICE_ROLE_KEY` (JWT `eyJ…`, same project)	401 — ignore; Railway `sb_secret` used instead; all hosted secrets destroyed post-migration (Week 7)
Railway service env	No `DATABASE_URL` / `SUPABASE_DB_PASSWORD` — app uses Supabase HTTP API only (expected)
Direct `psql` / `pg_dump`	Not run in Gate A — needs database password at Gate B execution (Supabase dashboard password field; not labeled as a URL in settings)

Repo migration inventory (`last1.id`)¶

Source: shallow clone /tmp/last1-id-audit, branch main, commit 358f083.

Item	Count / detail
SQL files in `supabase/migrations/`	71
First migration	`20260314000001_identities.sql`
Last migration	`20260625120000_onset_oauth_app_client.sql`
`CREATE TABLE` (public)	36 tables (see list below)
`ALTER TABLE … ENABLE ROW LEVEL SECURITY`	32 tables
`CREATE POLICY` (case-insensitive)	37 policies across 6 migration files
Custom function	`enforce_proof_request_subject_binding_immutable()`
Custom trigger	`trg_proof_requests_subject_binding_immutable` on `proof_requests`

Migration replay strategy (Gate B): Repo-tracked migrations are complete and should replay cleanly onto sovereign-db-last1id / last1id_prod for schema + RLS. Data still requires pg_dump from hosted Supabase (auth + public + storage metadata). Live supabase_migrations.schema_migrations row count vs 71 repo files must be verified in Gate B once Postgres access exists.

Public tables defined in repo (36)¶

admin_audit_logs, admin_telemetry_events, app_client_members, app_clients, compliance_bundle_runs, consent_audit_log, consent_grants, credential_issuers, credentials, identities, identity_app_links, identity_keys, identity_profile_photos, issuer_keys, last1_app_org_bridge, linked_auth_providers, oauth_authorization_codes, oauth_refresh_tokens, oauth_token_audit_log, org_admin_memberships, org_audit_logs, org_cohorts, org_participants, org_policy_templates, org_programs, org_proof_requests, organizations, platform_services, presentation_replay_nonces, presentation_requests, proof_requests, reputation_snapshots, system_admin_memberships, trust_events, trust_policies, webhook_deliveries

Live Supabase — `auth.*` schema¶

Source	Finding
GoTrue admin API (`/auth/v1/admin/users`)	12 users
Per-table `auth.*` row counts	Blocked — requires direct Postgres (`auth.sessions`, `auth.identities`, `auth.refresh_tokens`, etc.)

Auth user emails (admin API, 2026-08-24)¶

Pentest/synthetic-heavy set plus founder accounts:

lastvet-xss-pentest@mailinator.com, xss-test-pentester@protonmail.com, pentest_fresh_vuln01@exploit.dev
ssrftest1783385196@example.com, ssrf_pentest_01@example.com, pentest@mailnull.com
ssrftest1783384817@example.com, ssrftest1783384761@mailinator.com
jose@last1.org, ryan@last1.enterprises, curryrg@gmail.com, dev@last1.enterprises

Note: Y4 assumed zero production users. Treat as no real veteran/production traffic, but migration must preserve these rows (OAuth codes, refresh tokens, credentials, pentest artifacts).

Live Supabase — `public.*` row counts¶

Captured via PostgREST Prefer: count=exact + service role (2026-08-24 UTC).

Table	Rows
admin_audit_logs	2
admin_telemetry_events	0
app_clients	11
compliance_bundle_runs	0
consent_audit_log	3
consent_grants	18
credential_issuers	5
credentials	220
identities	18
identity_app_links	18
identity_keys	17
identity_profile_photos	6
issuer_keys	2
last1_app_org_bridge	13
linked_auth_providers	13
oauth_authorization_codes	32
oauth_refresh_tokens	26
oauth_token_audit_log	26
org_admin_memberships	4
org_audit_logs	0
org_cohorts	0
org_participants	0
org_policy_templates	0
org_programs	0
org_proof_requests	0
organizations	13
platform_services	4
presentation_replay_nonces	0
presentation_requests	0
proof_requests	0
reputation_snapshots	7
system_admin_memberships	4
trust_events	68
trust_policies	6
webhook_deliveries	4

Totals: 35 PostgREST-exposed tables audited + consent_audit_log (3) = 36 tables with counts; 543 rows in non-empty public data (excluding zero-row tables).

FK spot-check (REST)¶

Check	Result
`identities` with `auth_user_id` NOT NULL	10 / 18 identities linked to auth users
`auth.users` count	12
Gap (2 auth users without public identity, or identities without auth)	Expected to reconcile in Gate B via SQL join on `identities.auth_user_id` → `auth.users.id`

Tables in repo but not PostgREST-exposed¶

Table	REST probe
`app_client_members`	404 (exists in migrations; count needs Postgres)

RLS policies inventory¶

Layer	Gate A status
Repo	32 RLS-enabled tables; 37 policies (incl. service-role full-access batch + auth-table policies in `20260430133000_enable_rls_for_sensitive_auth_tables.sql`)
Live `pg_policies`	Not queried — requires Postgres

Gate B must confirm live policy count and names match repo (target ~37 policies on ~32 tables; Y11 cites ~74 migration lines / ~32 tables).

Storage — `profile-photos` bucket¶

Item	Value
Bucket	`profile-photos` (public, 5 MB limit, jpeg/png/webp)
Object count	3
Total size	4,716 bytes (378 + 3,018 + 1,320)

Storage path	Size	MIME
`identities/21f171bb-…/provider-71883748-….jpg`	378	image/jpeg
`identities/2a805b21-…/provider-e89e89c6-….jpg`	3,018	image/jpeg
`identities/a3d34613-…/provider-143301fb-….jpg`	1,320	image/jpeg

identity_profile_photos vs Storage: 6 DB rows; only 3 reference storage_path (matching the 3 Storage objects). Others use external image_url (Google avatar URLs or pentest SSRF probe URLs). Gate B MinIO migration scope = 3 files only.

storage.* Postgres metadata (buckets/objects rows): Not queried — requires Postgres; Gate B will dump storage schema metadata (not file bytes via pg_dump).

Custom functions and triggers¶

Object	Repo	Live DB
`enforce_proof_request_subject_binding_immutable()`	Yes (`20260404000000_proof_requests.sql`)	Verify in Gate B (`pg_proc`)
`trg_proof_requests_subject_binding_immutable`	Yes	Verify in Gate B (`pg_trigger`)

No other custom functions/triggers found in the 71 repo migrations.

Migration history state¶

Item	Gate A status
Repo files	71 tracked in `supabase/migrations/`
Live `supabase_migrations.schema_migrations`	Not queried — requires Postgres
Supabase CLI / dashboard-only migrations	Unknown until live history compared to repo file list

Working assumption: All schema is repo-driven (71 files span 2026-03-14 → 2026-06-25). Gate B first SQL on source should be SELECT version, name FROM supabase_migrations.schema_migrations ORDER BY version.

Gate B prerequisites (not blockers for Gate A approval)¶

Postgres password for pg_dump — Supabase Dashboard → Database → password (there is no separate “Database URL” row; host is db.nztgswcpnwggymncrnyx.supabase.co, project/API base already in BWS SUPABASE_URL). Required at Gate B execution for:
Full auth.* row counts
storage.* metadata counts
Live RLS inventory (pg_policies)
supabase_migrations.schema_migrations parity vs 71 repo files
Schema + data dump/restore
Service role for API/Storage — Continue using Railway sb_secret_… (or equivalent) until Week 7 teardown. No rotation of legacy BWS JWT keys pre-migration.
Optional: Read-only SQL snapshot script in scripts/last1-id/ for repeatability before Gate B dump.

Gate B preview (not executed)¶

When Ryan approves Gate A:

pg_dump Supabase: auth, public, storage (metadata)
Restore to 192.168.1.95 / last1id_prod
Apply/reconcile RLS from repo or extracted policies
Verify row counts match this report
Copy 3 profile photo objects to R940 MinIO with checksum verification
Verify GoTrue on VM 114 reads migrated auth.users
STOP for Gate B approval

Ryan Gate A checklist¶

[ ] Acknowledge 12 auth users / 543 public rows / 3 storage objects (updates Y4 “zero users” assumption)
[ ] Confirm 71 repo migrations are the canonical schema source
[ ] Confirm Gate B may use BWS SUPABASE_URL project ref + Supabase database password for pg_dump (no new BWS URL secret required)
[ ] Approve proceeding to W2B dry-run migration (Supabase remains authoritative; no cutover)

STOP — Awaiting Ryan Gate A approval.