BUILT INTO EVERY GOODSPEED APP
Append-Only Audit Log
Every write to sensitive tables and admin action lands in an append-only audit_log table with a pii_class column for SOC 2 and HIPAA-overlay compliance.
- Tier: Core
- Status: Static
- Always enabled
WHY IT MATTERS
Compliance auditors, enterprise sales prospects, and security-conscious users all ask the same question when evaluating a new app: who touched that data and when? Without a durable, tamper-resistant record, the honest answer is "we don't know." Every generated Goodspeed app ships an audit_log table that answers this question. The table is append-only by design: row-level security is configured to allow INSERT via service_role but no UPDATE or DELETE from application code, which means once a record is written it cannot be silently altered through the same code paths that created it. The schema records actor_id (the UUID of the user, admin, or system that originated the action), actor_type (constrained to 'user', 'admin', 'system', or 'service_role'), action (a namespaced string like profiles.update or subscriptions.delete), target_table, target_id, target_data (a jsonb diff snapshot), and a pii_class column that marks rows as 'standard', 'phi', or 'highly_sensitive'. The pii_class column has a partial index on non-standard rows, so a HIPAA or FERPA overlay that queries only sensitive entries never does a full-table scan. Three additional indexes cover the three access patterns auditors actually use: filter by actor (who did this?), filter by target (what happened to this record?), and filter by action type across time. Most audit tables are an afterthought added when a compliance questionnaire arrives. The gas-template includes it from migration 008 so the first production write is already in the log.
Attribution is the hard part of audit logging. A naive implementation records every database write as originating from 'service_role', which tells you nothing about the actual user. The template solves this with a per-transaction GUC (PostgreSQL session variable) mechanism. Edge functions call setActorContext(actorId, 'user' | 'admin') from supabase/functions/_shared/audit-log.ts at the start of each request, after authentication. This writes the user UUID into request.actor_id and their role into request.actor_type as transaction-local config values, so they automatically expire at the end of the connection without leaking onto the next pooled request. The audit_trigger() function, which is attached via CREATE TRIGGER to each sensitive table, reads these GUC values first. If they are set, the actor comes from the authenticated calling user, not from the database role. If they are absent (cron jobs, direct DB operations), the trigger falls back to auth.uid(), and if that is also absent it records 'service_role' as the actor type. The result: writes from user-initiated Edge Function calls are attributed to the correct user even though the actual DB credentials are service_role. The writeAuditLog() helper in the same file provides an explicit call path for events that do not go through a trigger, such as auth events, admin actions via the admin API, or application-level business events like subscription changes.
HOW IT IS WIRED
Real code from the GAS template
The code below is drawn from supabase/functions/_shared/audit-log.ts in the gas-template repository. This is the code your generated app gets, not pseudocode, not a description of intent.
// supabase/functions/_shared/audit-log.ts
// Write helper for the audit_log table. Failures log but never throw.
export interface AuditLogParams {
actorId?: string | null;
actorType: 'user' | 'admin' | 'system' | 'service_role';
action: string;
targetTable?: string;
targetId?: string;
targetData?: Record<string, unknown>;
piiClass?: 'standard' | 'phi' | 'highly_sensitive';
ipAddress?: string;
userAgent?: string;
requestId?: string;
}
export async function writeAuditLog(p: AuditLogParams): Promise<void> {
if (!p.action || !p.actorType) {
log('warn', 'audit-log', 'invalid_params', { params: p });
return;
}
try {
const client = serviceClient();
const { error } = await client.from('audit_log').insert({
actor_id: p.actorId ?? null,
actor_type: p.actorType,
action: p.action,
target_table: p.targetTable ?? null,
target_id: p.targetId ?? null,
target_data: p.targetData ?? null,
pii_class: p.piiClass ?? 'standard',
ip_address: p.ipAddress ?? null,
user_agent: p.userAgent ?? null,
request_id: p.requestId ?? null,
});
if (error) log('warn', 'audit-log', 'insert_failed', { error: String(error), action: p.action });
} catch (e) {
log('warn', 'audit-log', 'exception', { error: String(e), action: p.action });
}
}Source: goodspeed-apps/gas-template → supabase/functions/_shared/audit-log.ts
HONEST LIMITS
When Append-Only Audit Log is the wrong choice
The audit_log table is built for write and mutation events: creates, updates, deletes, auth actions, admin operations. It is not designed for high-frequency read events. If your app logs every search query, every page view, or every item fetch to this table, you will generate millions of rows per week with low signal density. Table bloat drives up Postgres storage costs, slows index maintenance windows, and turns routine compliance queries ("show me all writes by this user last month") into full-table scans even with indexes. For read-pattern observability, use your analytics layer (PostHog or a separate events table with a shorter retention policy) rather than audit_log. A second scenario where this table is the wrong tool is bulk-import operations. If your app supports CSV imports or batch data migrations that write thousands of rows per job, attaching the audit_trigger() to the target tables will produce a corresponding audit_log row for every single row inserted. A 10,000-row import generates 10,000 audit rows, most of which carry identical actor, action, and metadata. For bulk operations, write a single audit_log row at the job level (action: 'bulk_import.completed', target_data: { row_count: 10000, source: 'csv' }) rather than relying on the row-level trigger. The trigger can be temporarily detached for the import transaction using ALTER TABLE ... DISABLE TRIGGER, with a single manual writeAuditLog() call wrapping the batch.
Tier: Core · Static
Evaluate your use case
Check whether append-only audit log aligns with your target audience, platform constraints, and regulatory environment before enabling it.
Audit the config
This feature is always enabled. If you need to disable it, remove the corresponding template files after generation.
Seek alternatives
If the built-in implementation does not fit, the generated codebase is standard React Native + Expo code. Any library in the Expo ecosystem can replace the default.
APPS USING THIS FEATURE
Every generated Goodspeed app includes append-only audit log. Browse the ideas catalog to see apps across all categories that ship with this feature wired in.
CAPABILITIES
Append-Only Audit Log capability breakdown
Concrete dimensions of what the built-in append-only audit log implementation covers. These reflect the actual template code, not a marketing summary.
| Item | Description | Strength |
|---|---|---|
| Storage backend | audit_log lives in Supabase Postgres. Row-level security restricts all access to service_role, so application code can only write via server-side Edge Functions, never directly from a client JWT. | Supabase Postgres |
| Actor attribution | setActorContext() writes the calling user UUID into a per-transaction GUC (request.actor_id). The audit_trigger() reads this GUC first, falling back to auth.uid() then 'service_role', so service-role writes still attribute to the correct user. | GUC per-transaction |
| PII classification | The pii_class column accepts 'standard', 'phi', or 'highly_sensitive'. A partial index covers non-standard rows, enabling HIPAA and FERPA overlays to query sensitive entries without full-table scans. | standard / phi / highly_sensitive |
| Trigger attachment | audit_trigger() is a reusable PL/pgSQL function. Attach it to any sensitive table with CREATE TRIGGER ... AFTER INSERT OR UPDATE OR DELETE ... EXECUTE FUNCTION public.audit_trigger(). No per-table code needed. | Reusable trigger function |
| Retention policy | Migration 008 seeds the retention_policies table with a 365-day default for audit_log (SOC 2 norm), with a note that HIPAA overlay extends this to 6 years. The retention cron reads this row to drive automated cleanup. | 365-day default (configurable) |
COMMON QUESTIONS
Does the audit log work on both iOS and Android, or is it server-side only?
The audit_log table and trigger infrastructure is entirely server-side. It lives in Supabase Postgres and is written to by Edge Functions and database triggers, not by client-side code. There is nothing to install in the React Native app. The client app calls Edge Functions as it normally would (creating a record, updating a profile, cancelling a subscription), and the server-side audit layer captures those events without the mobile app needing to know about the audit system at all. This is intentional: client-side audit logging is unreliable because the client can be interrupted, uninstalled, or modified. Authoritative compliance records must originate from a controlled server environment. The only mobile-side consideration is that the app should propagate a request ID header (a generated UUID per call) in its API requests so the audit row's request_id field links back to the originating request in your server logs.
How does pii_class work, and what happens to highly_sensitive rows?
The pii_class column is a text field with a CHECK constraint limiting it to 'standard', 'phi', or 'highly_sensitive'. When you call writeAuditLog() or when the audit_trigger() fires, the pii_class defaults to 'standard' unless you override it. For actions involving protected health information in a HIPAA-covered app writing to a health_records table, set pii_class: 'phi' in the writeAuditLog() call or set it explicitly in the trigger for that table. For actions involving highly sensitive data like payment card details or government IDs, use 'highly_sensitive'. A partial index on pii_class covers only non-standard rows, which keeps the index small and makes the compliance query pattern ("show me all PHI-touching operations in the last 30 days") fast without scanning standard rows. The RLS policy on audit_log allows only service_role reads, so highly_sensitive rows are never exposed through the anon or authenticated Supabase client regardless of what your application RLS policies say about other tables.
Does logging every database write make the app slower?
The audit_trigger() fires after the row is written (AFTER INSERT OR UPDATE OR DELETE), so it runs inside the same transaction but after the primary write has completed. The trigger is a synchronous database operation, not a queue or a network call, which means latency is the cost of a single INSERT into audit_log inside the existing transaction. On a local Supabase instance that INSERT typically adds 1-3 ms to the transaction. In production on a dedicated Postgres instance with warm connections the overhead is comparable. The main performance concern is tables with extremely high write throughput: if you are inserting 500 rows per second, the audit trigger generates a matching 500 inserts per second into audit_log. For those tables, use the explicit writeAuditLog() at the job or batch boundary rather than attaching a row-level trigger. The trigger is the right default for low-to-moderate write frequency tables where correctness and completeness matter more than marginal throughput gains.
How do I query the audit log to satisfy a compliance review or a user data access request?
The audit_log table has three purpose-built indexes to match the three query shapes compliance teams actually use. To answer 'what did this user do?': query WHERE actor_id = $user_id ORDER BY created_at DESC -- the idx_audit_log_actor index covers this. To answer 'what happened to this record?': query WHERE target_table = $table AND target_id = $record_id ORDER BY created_at DESC -- the idx_audit_log_target index covers this. To answer 'who performed admin resets in the last 30 days?': query WHERE action = 'admin.password_reset' AND created_at > now() - interval '30 days' -- the idx_audit_log_action index covers this. All three queries hit an index and avoid sequential scans up to hundreds of millions of rows with normal Postgres table sizes. For GDPR data access requests, the gdpr-data-export feature includes audit_log in its export query so users receive their complete activity history alongside their profile and application data.
GET IT BUILT INTO YOUR APP