Brainforge Knowledge

optional-postgrest-audit-logging

3 min read

Optional PostgREST audit logging (Supabase)

Version: 1.0
Date: April 2, 2026
Owner: Platform / AI Team

1. Purpose

Pattern for append-only audit rows written from Node services via Supabase PostgREST (POST /rest/v1/<table>). Logging must be optional (missing env ⇒ no-op), non-blocking for the user-facing path, and safe under RLS when using the service role key only on the server.

In-repo example: Brainforge Slack Assistant — brainforge_assistant_interaction_log (see apps/slack-apps/brainforge-assistant/src/interaction-log.js and apps/slack-apps/brainforge-assistant/ENVIRONMENT.md).

2. Environment variables

  • Use service-specific names when a deploy might target a different Supabase project than the Forge (e.g. ASSISTANT_INTERACTION_LOG_SUPABASE_URL + ASSISTANT_INTERACTION_LOG_SUPABASE_SERVICE_ROLE_KEY).
  • When the target is always Internal AI Core, document that operators may mirror platform vars: same values as BF_SUPABASE_URL and BF_SUPABASE_SERVICE_KEY.
  • Never commit keys. Load from deployment secrets or local .env (gitignored).

3. Implementation checklist

PracticeWhy
Gate on both URL and keyIf either is unset, return immediately; do not throw.
Fire-and-forget on hot pathsWrap async insert so failures never reject the Slack/API handler; log warnings only if useful.
Truncate large text fieldsCap user_message, model_prompt, answer_text, etc., to avoid huge rows and PostgREST limits.
Normalize structured fieldsCap array length (e.g. citations); coerce unknown shapes to safe JSON.
HTTPS client with timeoutUse a bounded timeout (e.g. 12s); destroy the request on timeout.
Handle IncomingMessage errorsAttach res.on('error', …) so an aborted response does not crash the process.
Prefer Prefer: return=minimalReduces payload on success.
Headersapikey, Authorization: Bearer <service_role>, Content-Type: application/json.

4. Database and RLS

  • Migration in apps/platform/supabase/migrations/ (or the target project’s migration flow) with explicit columns, checks, and indexes for query patterns (created_at, foreign keys to Slack ids if needed).
  • RLS enabled with no broad anon / authenticated policies if rows contain sensitive text; service role bypasses RLS for server inserts (and optional internal read tools).
  • Treat tables as append-only by convention; document whether deletes are allowed for GDPR/support.

5. Privacy, retention, and access

  • Document what is stored (e.g. user prompts, model output, Slack ids).
  • Align retention and who may query with internal policy (People Ops / Security / Platform).
  • For assistants: see apps/slack-apps/brainforge-assistant/ENVIRONMENT.md (optional audit section).

6. Verifying locally

  • Do not rely on source .env for a large monorepo .env; malformed or complex lines can leave variables empty in bash while Node still parses them. Prefer:
    • node --env-file=/path/to/.env -e "…" or
    • require('dotenv').config({ path: '…' }) in a small script.
  • Smoke test: POST one row with curl or a script, then SELECT via Supabase MCP execute_sql or SQL editor; delete test rows if policy allows.

7. Assistant-specific troubleshooting (brainforge_assistant_interaction_log)

When debugging the Slack assistant (apps/slack-apps/brainforge-assistant), use this quick triage flow:

  1. Confirm both logging env vars are set on the assistant service:
    • ASSISTANT_INTERACTION_LOG_SUPABASE_URL
    • ASSISTANT_INTERACTION_LOG_SUPABASE_SERVICE_ROLE_KEY
  2. Confirm migration apps/platform/supabase/migrations/010_brainforge_assistant_interaction_log.sql was applied to that same Supabase project.
  3. Check service logs for:
    • Assistant interaction log: invalid ASSISTANT_INTERACTION_LOG_SUPABASE_URL
    • Assistant interaction log failed: PostgREST <status>
    • Assistant interaction log timeout
  4. Ensure URL/key point to the same project (cross-project URL/key pairs are a common cause of silent insert failures).

Row semantics used by runtime:

  • One row per Q&A turn (source is app_mention, direct_message, or slash_command).
  • status='ok' means model generation + Slack delivery succeeded.
  • status='error' means the turn failed. If the model produced output but later steps failed, metadata.failureStage = "post_model" and partial model fields are preserved.

Useful queries:

-- Recent logging failures
SELECT created_at, source, slack_channel_id, slack_user_id, error_message, metadata
FROM brainforge_assistant_interaction_log
WHERE status = 'error'
ORDER BY created_at DESC
LIMIT 50;
-- Failures after model output was produced
SELECT created_at, source, model, azure_response_id, metadata
FROM brainforge_assistant_interaction_log
WHERE status = 'error'
  AND metadata->>'failureStage' = 'post_model'
ORDER BY created_at DESC
LIMIT 50;

Graph View

Backlinks