Tenant Audit Log — Institution-Visible Security Events

HECVAT question: “Are audit logs available to the institution that include AT LEAST: login, logout, actions performed, timestamp, source IP?”

Short answer today: No (partially for SSO logins only). We record most of these facts, but in platform-internal tables with 7-day retention, without tenant attribution, and the only institution-visible log is the last 20 SSO login attempts (without IP). Source IPs on the Lambda API are not trustworthy yet.

1. What is recorded today (verified 2026-09-23)

Store	Migration(s)	What it captures	Tenant-scoped?	Institution can see?	Retention
`app_logs`	`054_app_logs`, `090_app_logs_retention`, `127_app_logs_email_ip`, `135_app_logs_auth_event_index`	Structured app logs. Request logger writes every mutation (POST/PUT/PATCH/DELETE) and every 4xx/5xx with `method`, `path`, `status`, `user_id`, `user_email`, `client_ip` (`workers/api/src/index-aws.ts:~385-415`, `workers/api/src/server.ts:~510-545`)	No `tenant_id` column	No — RLS service-role only	7 days (pg_cron `app_logs_retention`)
`app_logs` rows `component='auth'`	written by `workers/api/src/routes/auth-events.ts`	`auth.signed_in`, `auth.signed_out`, `auth.signed_up` + user, email, IP, UA	No	No — platform admin page `/admin/login-history` (`workers/api/src/routes/admin.ts:~2650`)	7 days
`auth_login_history` view	`133` (created), `136` (dropped)	Was a view over Supabase `auth.audit_log_entries`; dropped as unreliable	—	—	—
`tenant_sso_logins`	`254_tenant_sso`	Every SAML attempt: user, NameID, email, outcome (`success`/`denied`/`error`), reason, IP, assertion ID	Yes	Partially — `GET /api/tenant-admin/sso` returns the last 20 rows, without IP (`workers/api/src/routes/tenant-sso-admin.ts`, `apps/web/src/app/tenant-admin/sso/SsoLoginsTable.tsx`)	Unbounded (no cron)
`admin_audit_log`	`025_admin_dashboard_expansion`	Platform admin actions via `logAdminAction()` (`workers/api/src/services/admin-audit.ts`); `ip_address` column exists but the writer never sets it	No	No — service role only	Unbounded
`user_audit_log`	`091_disable_ai_services`	Per-user preference changes (before/after, IP, UA) — only written from `workers/api/src/routes/preferences.ts`	No	User sees own rows	Unbounded
`account_deletion_audit`	`085`, `086`, `088`	Deletion execution; user id is hashed by design	No	No	Unbounded
`remediation_events`	`304_remediation_items`	Remediation workflow state changes (no IP)	No (user-scoped)	Owner only	Unbounded
`file_violation_override_log`	`123`	Override create/update/revoke, actor	No	No	Unbounded
`activity_events`	`040_photos_social_features`	Photos-app social feed — not relevant	—	—	—

Password, email-OTP, voice-OTP, Google/LinkedIn logins happen browser → Supabase Auth directly. Our API learns about them only because the SPA posts signed_in/signed_up to /api/auth-events (apps/web/src/lib/auth-context.tsx postAuthEvent, also apps/admin). The JWT is verified (requireAuth), so identity is trustworthy, but the event is client-reported, best-effort (keepalive fetch; omitted if the tab closes, the request is blocked, or a non-apps/web client logs in).
Failed password/OTP logins are not recorded anywhere we control.
Logout is recorded only for explicit, user-initiated sign-out in apps/web / apps/admin (signed_out posted before supabase.signOut()). Session expiry, token revocation, and sign-out in other apps are not recorded. Note sessions currently have no absolute timeout (docs/admin/access/sso-status.md, Phase 1 item 2), so “session ended” is rare in practice — itself a HECVAT finding.
SSO logins are recorded server-side and authoritatively in tenant_sso_logins. There is no SAML Single Logout.

1.2 “Actions performed”

Only mutations are logged, as raw METHOD /path → status strings, for 7 days, platform-only. File downloads (GET) are not logged unless they error.
Tenant-admin actions — user add/remove, department changes, SSO config, domains, branding, LTI (workers/api/src/routes/tenant-admin.ts, departments.ts, tenant-sso-admin.ts) — are not written to any audit table.

1.3 Source IP trust — current defect

Every IP extractor prefers CF-Connecting-IP, then X-Forwarded-For: workers/api/src/utils/sso-http.ts clientIp(), routes/auth-events.ts clientIpFrom(), the request loggers in index-aws.ts / server.ts, plus client-logs.ts, preferences.ts, unsubscribe.ts, accessible-pdf.ts, pdf-handoff.ts, md-to-pdf.ts, org-chart/org-chart-share.ts.

The Lambda API (api-pdf.gevell.com) is served by API Gateway HTTP API (infra/cdk/lib/stacks/api-stack.ts), not through Cloudflare, and the default execute-api endpoint is not disabled. So CF-Connecting-IP is an ordinary client-supplied header there: any caller can set it and forge the logged IP. X-Forwarded-For is also client-prependable (API Gateway appends the real peer at the end, but we read index [0]). The .4 Node server (api.gevell.com) sits behind a Cloudflare Tunnel, where CF-Connecting-IP is trustworthy.

Until fixed, any IP we present to an institution is spoofable. Fixing this is a prerequisite, not an optional extra.

2. Target design

2.1 Event model — new table `tenant_audit_events`

A dedicated append-only table rather than a view over app_logs because: app_logs retention is 7 days and must stay short (volume), it has no tenant column, and its rows are free-form. A view can still be offered as a unified read model over tenant_audit_events + tenant_sso_logins so SSO history is not duplicated.

CREATE TABLE public.tenant_audit_events (
  id            BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  occurred_at   TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  tenant_id     UUID NOT NULL REFERENCES public.tenants(id) ON DELETE CASCADE,
  department_id UUID REFERENCES public.departments(id) ON DELETE SET NULL,
  actor_user_id UUID,                 -- no FK: must survive user deletion
  actor_email   TEXT,                 -- snapshot at event time
  actor_type    TEXT NOT NULL CHECK (actor_type IN ('user','tenant_admin','platform_admin','system','api_key')),
  event_type    TEXT NOT NULL,        -- e.g. 'auth.login', 'file.downloaded'
  outcome       TEXT NOT NULL DEFAULT 'success' CHECK (outcome IN ('success','failure','denied')),
  target_type   TEXT,
  target_id     TEXT,
  source_ip     INET,
  ip_trust      TEXT NOT NULL CHECK (ip_trust IN ('platform','edge','client_reported','unknown')),
  user_agent    TEXT,
  request_id    TEXT,
  details       JSONB NOT NULL DEFAULT '{}'  -- no document content, no secrets
);
CREATE INDEX ON public.tenant_audit_events (tenant_id, occurred_at DESC);
CREATE INDEX ON public.tenant_audit_events (tenant_id, event_type, occurred_at DESC);
CREATE INDEX ON public.tenant_audit_events (tenant_id, actor_user_id, occurred_at DESC);

Tenant is snapshotted at write time from profiles.tenant_id (a user moving tenants must not carry history across institutions).
ip_trust makes provenance explicit in the UI/CSV rather than implying all IPs are equal.
Append-only: REVOKE UPDATE, DELETE from authenticated/anon; only the retention job deletes. Optionally a hash chain (prev_hash) for tamper evidence — phase 2.

2.2 Events to capture (v1)

Category	Event types	Source (server-authoritative where possible)
Authentication	`auth.login` (method: password / email_otp / voice_otp / oauth / sso), `auth.login_failed`, `auth.logout`, `auth.session_expired`, `auth.password_reset_requested`, `auth.password_changed`	See 2.3
SSO	`sso.login`, `sso.denied`, `sso.error`	Read-through from `tenant_sso_logins` (view)
Documents	`file.uploaded`, `file.converted`, `file.downloaded` (original / accessible PDF / HTML / report), `file.shared`, `file.share_revoked`, `file.deleted`	API routes (files, export, share)
Reports	`acr.generated`, `audit.run`, `report.exported`	API routes
Membership	`user.invited`, `user.added`, `user.removed`, `user.department_changed`, `role.granted`, `role.revoked`	`tenant-admin.ts`, `departments.ts`, `teams.ts`
Configuration	`sso.config_updated`, `domain.added/verified/removed`, `branding.updated`, `lti.platform_*`, `program.allocation_changed`, `api_key.created/revoked`	tenant-admin routes; `admin-audit.ts` for platform actions on a tenant
Data lifecycle	`account.deletion_requested`, `account.deleted`, `data.export_requested`	deletion pipeline (`085`) — record in tenant log before hashing
Platform access	`support.access` — whenever a Gevell platform admin views/acts on tenant data	`admin.ts` routes via `logAdminAction` extension

The last row matters for HECVAT reviewers: institutions want to see vendor access.

Primary approach: a Postgres trigger on auth.sessions (GoTrue writes one row per session on sign-in and deletes it on sign-out/revocation):

AFTER INSERT → auth.login with source_ip = NEW.ip, user_agent = NEW.user_agent, ip_trust='platform' (the IP GoTrue itself observed behind Supabase’s edge).
AFTER DELETE → auth.logout (or auth.session_expired when not_after < now()).
The trigger function is SECURITY DEFINER, search_path='', looks up profiles.tenant_id, skips users not in a non-default tenant (keeps volume down), and never raises (wrap in EXCEPTION WHEN OTHERS → RAISE WARNING) so auth can’t break.

To verify before building (half-day spike): that our GoTrue version populates auth.sessions.ip / user_agent, that Supabase permits triggers on auth.sessions for our plan (they do for auth.users), and whether “Custom Access Token” / “Send Email” auth hooks give a better failed-login signal. Failed-login events likely require the client-side /api/auth-events path (login_failed with email only, marked ip_trust from the IP fix below) — document that limitation.

Fallbacks: keep /api/auth-events (client-reported) as a secondary signal, de-duplicated by session_id from the JWT.

SSO nuance: SSO sessions are minted server-side by the Lambda (generateLink + verifyOtp in workers/api/src/routes/sso.ts), so auth.sessions.ip for SSO is the Lambda’s address. For SSO the authoritative IP is the ACS request IP recorded in tenant_sso_logins — which depends on the IP fix below.

2.4 Source IP fix (prerequisite)

New helper workers/api/src/utils/client-ip.ts → trustedClientIp(c): { ip, trust }:
- Lambda (index-aws.ts via hono/aws-lambda): use requestContext.http.sourceIp from the API Gateway event (c.env.requestContext / c.env.event), which API Gateway sets from the TCP peer and cannot be forged by the client. Ignore CF-Connecting-IP and client X-Forwarded-For entirely.
- Node .4 (server.ts, behind Cloudflare Tunnel): CF-Connecting-IP is trusted only if the request arrived via the tunnel (cloudflared on loopback); otherwise use the socket address.
- If api-pdf is later moved behind Cloudflare, trust CF-Connecting-IP only after the origin is locked (Cloudflare Authenticated Origin Pulls / mTLS, or an API Gateway resource policy + secret header) and disableExecuteApiEndpoint: true is set in infra/cdk/lib/stacks/api-stack.ts.
Replace all ~12 ad-hoc extractors listed in 1.3 with the helper (rate limiting keyed on spoofable IPs is a separate, related bug — note it for the security backlog).
Unit tests: forged CF-Connecting-IP on Lambda is ignored; forged XFF prefix ignored.

2.5 Writer service

workers/api/src/services/tenant-audit.ts:

recordTenantEvent(c, event) resolves tenant/department/actor from context, IP via trustedClientIp, request_id from the request logger.
Batched: collects events per request and inserts once at the end of the request (same place the request logger flushes) — one insert, never N.
Errors are logged via c.get('log').error(...) and surfaced to the existing alerting; never swallowed, never fail the user’s request (except where the audit write is a security gate, as SSO replay reservation already is).
Do not reuse the in-memory SupabaseLogger buffer (packages/shared/src/supabase-logger.ts) — it can drop entries if a Lambda freezes before the 5 s flush; audit writes are awaited.

2.6 RLS

ALTER TABLE public.tenant_audit_events ENABLE ROW LEVEL SECURITY;

-- Tenant owner/admin/manager/viewer read their own tenant's events.
CREATE POLICY tae_tenant_admin_read ON public.tenant_audit_events FOR SELECT
  USING (public.can_manage(auth.uid(), 'org', tenant_id)
         OR EXISTS (SELECT 1 FROM public.tenant_admins ta
                    WHERE ta.user_id = auth.uid() AND ta.tenant_id = tenant_audit_events.tenant_id));

-- Department admins read only their department's events.
CREATE POLICY tae_dept_admin_read ON public.tenant_audit_events FOR SELECT
  USING (department_id IS NOT NULL AND public.can_manage(auth.uid(), 'dept', department_id));

-- Platform super admins read all.
CREATE POLICY tae_platform_read ON public.tenant_audit_events FOR SELECT
  USING (public.is_admin());

-- No INSERT/UPDATE/DELETE policies: writes are service-role only.

(can_manage — migration 250_can_manage_hierarchy; is_admin is in the keep-set of SECURITY DEFINER functions left executable after migration 284.) The API endpoint enforces the same scope in code (requireTenantAdmin), with RLS as defense in depth, matching the existing pattern in tenant-sso-admin.ts.

2.7 Retention

Data	Today	Proposed
`app_logs` (debug)	7 days	unchanged (it is not the audit log)
`tenant_audit_events`	—	13 months default (1 year + a month for annual reviews), configurable per tenant up to 7 years via `tenants.metadata.audit_retention_days`; nightly pg_cron job in batches of 10k
`tenant_sso_logins`	unbounded	same policy as `tenant_audit_events` (currently no cron — add one)
`admin_audit_log`, `user_audit_log`	unbounded	24 months, add cron

Storage estimate: ~300 bytes/row; a 5,000-user institution at ~40 events/user/month ≈ 200k rows/month ≈ 60 MB/month ≈ 0.8 GB per tenant-year. Watch Supabase Pro’s 8 GB allowance; if multiple large tenants land, archive rows older than 90 days to R2 as monthly gzipped NDJSON (still exportable) — record storage cost per the cost-tracking standard.

2.8 API

GET /api/tenant-admin/audit-events?from&to&eventType&actor&outcome&ip&cursor&pageSize — cursor-paginated (occurred_at,id), standard envelope { data, error, meta }, default last 30 days, max page 200.
GET /api/tenant-admin/audit-events/export.csv?...same filters — streamed CSV (no in-memory buffering), RFC 4180 quoting, formula-injection guard (prefix cells starting = + - @ with '), UTC ISO-8601 timestamps plus the IP-trust column, max 1M rows/export, logs its own audit.exported event.
GET /api/v1/audit-events (optional, phase 2) — same query via tenant API key for SIEM ingestion (Splunk/Sentinel pull), documented in docs/api/openapi.yaml.
Route files need tests (workers/api/src/__tests__/routes/tenant-audit.test.ts).

2.9 Tenant-admin UI — `apps/web/src/app/tenant-admin/audit-log/`

Add a card/link on apps/web/src/app/tenant-admin/page.tsx next to SSO.

Filter form: date range (two labelled type="date" inputs), event category <select>, actor email search, outcome, IP. A real <form> with a Submit button (no auto-apply on every keystroke — avoids 3.2.2 surprises and API churn).
Results: semantic <table> with <caption>, th scope="col", local-time display via Intl.DateTimeFormat with the UTC ISO value in a <time datetime>; row “Details” opens a disclosure (button with aria-expanded), not a hover tooltip.
Result count and loading state announced in a role="status" region (“Loaded 50 of 1,204 events”); skeleton/spinner for requests > 2 s per the progress-indicator standard.
“Load more” button (not infinite scroll) so keyboard/screen-reader users keep their place and the footer stays reachable.
“Export CSV” button downloads with the current filters; announces completion.
WCAG 2.1 AA: labels, 44×44 px targets, 3px #046307 focus ring, 4.5:1 contrast in both themes, table reflows to stacked cards below 640 px (1.4.10), no color-only outcome indicators (text + icon).
New gated axe spec apps/web/e2e/tenant-admin-audit-log.a11y.spec.ts (authenticated).

3. Tests

Area	Tests
IP helper	Lambda ignores forged `CF-Connecting-IP`/XFF; Node trusts CF header only via tunnel
Writer	tenant snapshot, batching (single insert per request), error logged not swallowed
Trigger	`auth.sessions` insert/delete produce login/logout rows; default-tenant users skipped; trigger never raises (supabase local stack test)
RLS	tenant A admin cannot read tenant B; dept admin limited to dept; member (non-admin) sees nothing; anon denied (SQL tests via local Supabase)
Routes	filters, cursor pagination, CSV quoting + formula guard, streaming, viewer role allowed read, export event recorded
Instrumentation	each event type in 2.2 emitted by its route (table-driven)
UI	vitest for filter form; Playwright axe (gating) + keyboard walk
Retention	cron SQL deletes only rows older than the tenant’s window

4. Rollout

IP fix first (independent, security value on its own) — Lambda deploy + .4 rebuild (npm run rebuild; routes on .4 need it — see memory note on Forms API host).
Migration: table, RLS, indexes, retention cron, auth.sessions trigger (after spike).
Writer + instrumentation, behind TENANT_AUDIT_LOG_ENABLED; verify volumes on the default tenant excluded.
API + UI; pilot with the first SSO institution.
Update docs/user (tenant admin guide: “Viewing your audit log”) and admin docs.
Backfill: import existing tenant_sso_logins via the unified view (no copy needed); app_logs history is not backfilled (7-day window, no tenant attribution).

5. Effort estimate

Work item	Days
Trusted-IP helper + replace ~12 extractors + tests	1.5
(Infra, optional) lock origin / disable execute-api endpoint	0.5–1
Spike: `auth.sessions` trigger feasibility	0.5
Migration (table, RLS, indexes, trigger, retention crons)	1.0
Writer service + instrumentation of ~25 event types	3.0
API (list + streamed CSV) + route tests	1.5
Tenant-admin UI + a11y spec	2.0
RLS/trigger SQL tests, docs, pilot	1.0
Total	≈ 11–12 engineer-days

A minimum slice that makes the HECVAT answer “Yes” — IP fix, login/logout via trigger, SSO view, tenant-admin membership/config actions + file download/delete events, list

CSV UI — is ≈ 7 days.

6. Questionnaire wording

Before (accurate today):

Partially. The platform records authentication events (sign-in, sign-out), all state-changing API actions, and single sign-on attempts with timestamp, user and source IP address. Institution administrators can currently view recent SSO sign-in attempts in the admin console; the broader activity log is retained for operational purposes and is available to institutions on request through support. A self-service, institution-scoped audit log with export is on our roadmap for [target quarter].

(Do not claim source IP is authoritative until the IP fix in 2.4 ships; if asked, say IP addresses are recorded as reported at our API edge.)

After (once this plan ships):

Yes. Institution administrators have a self-service audit log in the admin console, scoped to their institution (department administrators see their department only). It records sign-in (including SSO and failed sign-in attempts), sign-out and session expiry, document upload/conversion/download/sharing/deletion, report generation, user and role changes, SSO and configuration changes, and any access by Gevell support staff. Each entry includes UTC timestamp, user, action, target, outcome, source IP address (taken from our infrastructure, not client-supplied headers) and user agent. Logs are append-only, retained for 13 months by default (longer by agreement), and can be filtered and exported to CSV or pulled via API for SIEM ingestion.