Storage access audit log

​

Purpose

Signed storage URLs are bearer tokens, and Supabase logs do not identify the app actor behind a service-role request. Without an app-side record, an exposure review cannot answer who obtained which object, when, and through which route. private.storage_access_events closes that gap for the workspaces bucket.

​

What is recorded

Each row is one access decision:

• File names, query strings, IP addresses, user agents, emails and signed URLs are never stored.
• Folder and file names are omitted for every instrumented route, even without a hash key. Object and actor identifiers remain linkable audit data; restrict access accordingly. Regression coverage: storage-access-audit.test.ts includes synthetic email folder names with valid, absent and short keys.
• To check whether a known object was touched, compute the HMAC of its full name (<wsId>/<path>) with the audit key and compare it with object_hash.

​

Object hash key

object_hash is keyed so that a reader of the table cannot confirm guessed file names. An unkeyed SHA-256 of <wsId>/<path> could be tested against candidate names.

• Web reads the key from the environment variable STORAGE_ACCESS_AUDIT_HMAC_KEY. Use at least 32 random bytes and keep it in the deployment secret store. Never commit it, log it, or paste it into tickets or chat.
• If the key is missing or shorter than 32 bytes, rows store only the opaque folder marker and object id. The writer never falls back to an unkeyed hash.
• Rotating the key makes older rows unmatchable with the new key. Keep the previous key in the secret store for the 90-day retention window if lookups of older rows matter.

​

Instrumented routes

Other storage routes are not instrumented yet. API-key requests rejected by withApiAuth before the handler runs are not recorded. Add a route with withStorageAccessAudit or auditStorageAccess from apps/web/src/lib/storage/storage-access-audit.ts.

​

Write guarantees and limits

The writer is @tuturuuu/storage-core/storage-access-audit.

• Best effort. record() returns immediately and never throws. A failed insert is logged with console.warn and dropped. The user request never waits for or fails because of the audit.
• Bounded per instance:
  • at most 100 rows per call, which covers the largest SDK batch of 100 paths;
  • at most 32 concurrent writes (extra events are dropped and counted);
  • identical allowed events from one instance are de-duplicated for 60 seconds, so repeated image loads do not flood the table. Denials and errors are always written.
• Losable. Vercel Node functions have no waitUntil binding here, so a write can be lost if an instance is frozen right after responding. Treat the log as strong evidence when present, not as proof of absence.

​

Retention

• Rows older than 90 days are deleted by private.prune_storage_access_events(p_limit), at most 1,000 rows per call.
• The default-off Cron Control job database-storage-access-audit-retention calls it on the ten-minute trigger.
• There is no pg_cron schedule or Next route; see Cron Control.

​

Who can read it

• RLS is enabled with no policies, so anon and authenticated can neither read nor write the table.
• service_role can insert and select, but not update or delete. Only the retention function deletes.
• There is no product UI or API. Operators with database access query it for incident review.

To look up an object, compute its HMAC outside the database, then query by the hex digest only. Never pass the key to psql (-v, -c or SQL text). psql inlines variables into the statement, so the key would appear in ps output and in server statement and error logs.

1. Make the key available without echoing it. Either load STORAGE_ACCESS_AUDIT_HMAC_KEY into the shell environment from the secret store, or point AUDIT_KEY_FILE at a mode-600 file that holds exactly the key.
2. Compute the digest. The object name goes on stdin and the key never appears on a command line:
   printf '%s' '<wsId>/<path>' | node -e '
     const fs = require("node:fs");
     const { createHmac } = require("node:crypto");
     const key =
       process.env.STORAGE_ACCESS_AUDIT_HMAC_KEY ??
       fs.readFileSync(process.env.AUDIT_KEY_FILE, "utf8").replace(/\r?\n$/, "");
     const name = fs.readFileSync(0, "utf8");
     process.stdout.write(
       createHmac("sha256", key).update(name, "utf8").digest("hex") + "\n"
     );
   '
   
   The name must match the stored object exactly (<wsId>/<relative path>, and for listings a trailing /).
3. Query with the digest, which is not secret. Run it with an operator role:
   -- psql -v object_hash=<64-character digest> -v ws_id=<workspace uuid> ...
   select occurred_at, actor_kind, action, route, outcome, item_count
   from private.storage_access_events
   where ws_id = :'ws_id'
     and object_hash = :'object_hash'
   order by occurred_at desc;
   

​

Rollout

1. Apply 20261029040810_storage_access_audit_events.sql. It needs approval and ships in the next migration batch.
2. Set STORAGE_ACCESS_AUDIT_HMAC_KEY (a secret of at least 32 random bytes), then STORAGE_ACCESS_AUDIT_ENABLED=true, for the Web deployment. Until then the writer is a no-op, so the code is safe to deploy before the migration.
3. Enable database-storage-access-audit-retention in CRON_CONTROL_ENABLED_JOBS.

To stop writes, unset the flag. Rows already written stay until retention removes them.

​

Schema rollback

Prefer disabling the writer and retention job while retaining audit evidence. The rollback file in apps/database/rollbacks/20261029040810_storage_access_audit_events.rollback.sql removes the table and retention function. It deletes audit history: disable both writers and archive required evidence before an explicitly authorized schema reversal. Re-applying the migration recreates an empty, service-only audit table.