Exporting verification data for BI tools

Webhooks are great for event-driven systems. Adapters are great for claims platforms. But sometimes you just want a CSV in your data warehouse. VerifyAI's BI export is a one-shot generator that emits a signed-URL download of one dataset over a date range, ready to drop into Looker, Metabase, Tableau, or COPY INTO a warehouse table.

By the end you'll know:

1. How to request an export from the dashboard or server actions.
2. The schema of each exported CSV / JSON file.
3. How signed URLs and the storage bucket work.
4. What's coming in v2 (site / column filters, scheduled exports, direct connectors).

1. Prerequisite: storage bucket

Exports land in the verify-ai-exports Supabase Storage bucket. There is no migration that creates this bucket — it's an operational prerequisite. Provision it once (private, no public access) in the Supabase dashboard or via supabase storage CLI before the first export request. On managed VerifyAI it's already there. If you see an upload error from requestBIExport, an unconfigured bucket is the first thing to check.

The bucket is private. The export pipeline writes the file and returns a signed URL valid for 1 hour (createSignedUrl(path, 3600)). Anyone with the URL can download the file during that window but no longer. Re-request the export to get a fresh URL.

2. Requesting an export (dashboard)

Dashboard → Operations → Exports is the UI. Pick:

• Dataset — verifications, exceptions, or audit_log. One dataset per request.
• Format — CSV or JSON.

Click Generate export. The export runs synchronously today (v1) and the dashboard shows the row count + a download link as soon as the upload to Storage finishes. Per-dataset row cap is 50,000; exports that hit the cap return the most recent 50,000 rows. v2 splits oversized requests into chunks.

Date-range and site filters are exposed through the server action (below) but not yet in the dashboard form — the dashboard call defaults to the last 30 days, all assigned sites.

3. Requesting an export (server action)

For automation — e.g. nightly dump into a warehouse — call requestBIExport() from app/actions/operations.ts:

import { requestBIExport } from "@/app/actions/operations";
 
const res = await requestBIExport({
  scope: "verifications",            // "verifications" | "exceptions" | "audit_log"
  format: "csv",                      // "csv" | "json", default "csv"
  since: "2026-05-01T00:00:00Z",      // ISO timestamp, default 30 days ago
  until: "2026-05-31T23:59:59Z",      // ISO timestamp, default now
});
 
if (!res.success) throw new Error(res.error);
// { success: true, url: signedUrl, rowCount: 12_834 }
 
// Stream into your warehouse loader
await fetch(res.url!).then((r) => /* COPY INTO warehouse */);

Behind the scenes the action:

1. Checks caps.can.exportBI (org_owner, org_admin, or site_manager). Audit-log exports additionally require caps.can.viewAuditLog, which is org-level only.
2. Pulls rows from the dataset's underlying table (verify_ai_verifications, verify_ai_exceptions, or verify_ai_audit_log), capped at 50,000. For verifications and exceptions, site-scoped users are further restricted to caps.siteIds. Verifications exclude sandbox rows.
3. Uploads the file to the verify-ai-exports bucket at {customerId}/{timestamp}-{scope}.{ext} with upsert: true.
4. Returns a signed URL with a 1-hour TTL.
5. Writes a bi_export.create row to verify_ai_audit_log with { scope, format, rows, since, until } — see Audit log and SOC 2 readiness.

Per-site or column-level scoping is on the v2 roadmap (see below).

4. The exported schema

Each dataset emits a fixed column set. CSV uses the column names as the header row; JSON is a single array of objects with those keys.

verifications

| Column | Type | Notes | | ------------------- | ----------- | ----------------------------------------------------- | | id | UUID | Verification id. | | policy_id | TEXT | e.g. pol_forest1. | | status | TEXT | Lifecycle status (completed, etc.). | | is_compliant | BOOLEAN | | | confidence | NUMERIC | 0.0 - 1.0. | | violation_reasons | JSONB | Array, serialized as JSON in CSV cells. | | feedback | TEXT | Model-generated text. | | site_id | UUID | NULL if org-wide. | | source | TEXT | Capture origin. Sandbox rows are excluded. | | created_at | TIMESTAMPTZ | ISO 8601 UTC. |

exceptions

| Column | Type | Notes | | ----------------- | ----------- | ------------------------------------------- | | id | UUID | Exception id. | | verification_id | TEXT | The originating verification. | | site_id | UUID | NULL if org-wide. | | status | TEXT | open / assigned / resolved / dismissed. | | assigned_to | UUID | customer_members.id, nullable. | | resolution | TEXT | Free-form, set on resolve/dismiss. | | notes | TEXT | Optional reviewer notes. | | created_at | TIMESTAMPTZ | | | resolved_at | TIMESTAMPTZ | Stamped on resolve / dismiss. |

audit_log

| Column | Type | Notes | | --------------- | ----------- | ------------------------------------------- | | id | UUID | | | actor_email | TEXT | Denormalized actor email. | | action | TEXT | e.g. site.create, exception.resolve. | | resource_type | TEXT | e.g. verify_ai_site. | | resource_id | TEXT | UUID or other string identifier. | | metadata | JSONB | | | ip | INET | Best-effort client IP. | | created_at | TIMESTAMPTZ | |

Columns are append-only — we won't rename or reorder existing columns, and any new ones land at the end. Lock your loader to known columns rather than SELECT *.

5. Format choice

• CSV — simplest. Use for Excel, Looker Studio CSV uploads, one-off audits. JSONB columns (violation_reasons, metadata) are JSON.stringify'd into a single cell.
• JSON — a single JSON array of row objects (not newline-delimited). Choose JSON when consumers prefer typed structures over CSV-escaped JSON.

6. Loading into a warehouse

-- Snowflake (CSV)
COPY INTO verify.verifications
FROM 'https://...signed-url.csv'
FILE_FORMAT = (TYPE = CSV SKIP_HEADER = 1);
 
-- BigQuery (CSV is the most ergonomic option today, since the JSON
-- format here is a single array rather than newline-delimited).
LOAD DATA INTO `myproj.verify.verifications`
FROM FILES (
  format = 'CSV',
  skip_leading_rows = 1,
  uris = ['https://...signed-url.csv']
);

The signed URL is valid for 1 hour. For nightly loads, re-request the export inside the same job that loads it — don't store the URL across runs.

7. Roadmap

• Site / column filters in the UI — surface since, until, per-site scoping, and per-column toggles in the dashboard form. The underlying action already accepts the date range.
• Async + chunking — current per-export cap is 50,000 rows; v2 moves to a background job that streams across the full range and splits oversize windows into multiple files.
• NDJSON output — line-delimited JSON for native BigQuery / Snowflake loaders.
• Scheduled exports — cron a recurring export and have it post to S3 / GCS / Azure Blob on a schedule, no polling.
• Direct connectors — Fivetran / Stitch connectors so you don't manage signed-URL fetches at all.

What's next

• Organizing teams with sites and roles — scope an export to a single site.
• Audit log and SOC 2 readiness — every export request is recorded.
• Sending verifications to claims platforms — for event-driven destinations, an adapter is usually a better fit than periodic exports.