Wiki Data

Data Infrastructure for Legal-AI Products

Scope

This pack covers data pipeline design, warehouse choices, analytical modeling, and the special-purpose stores a legal-AI product requires: audit logs, prompt-completion archives, user analytics, and entity extraction stores. It applies to both the internal analytics layer (understanding how the product is used) and the operational data layer (state that the AI system reads and writes at runtime).

Architecture overview

A legal-AI data stack has three conceptually distinct tiers:

Operational DB (Postgres / Supabase)
        │
        ▼
Streaming / CDC layer (Kafka, Debezium, or pg_logical)
        │
        ▼
Raw storage / lake (S3, GCS)
        │
        ▼
Warehouse (Snowflake / BigQuery / dbt-modelled Postgres)
        │
        ▼
BI layer (Metabase, Superset, or direct SQL notebooks)

For early-stage products (< 10 k MAU), a single Postgres instance with dbt models running against it is often sufficient. Graduate to Snowflake or BigQuery when query times on the operational database begin affecting product latency or when CDC fan-out is needed.

Data stores and their purposes

1. Operational database (Postgres)

The primary source of truth for application state:

• Users and workspaces — accounts, billing state, permissions, plan tiers
• Matters / projects — legal work items, associated documents, status
• Documents — metadata records (content stored in object storage or vector store)
• Skills / routing — active skill configurations, feature flags per workspace
• Conversations — session-level chat records (foreign key to user + matter)

Schema design notes for legal-AI:

• Always include created_at, updated_at, and a deleted_at soft-delete field — legal records must not be hard-deleted.
• Row-level security (RLS) in Postgres/Supabase is the right default for multi-tenant legal data; do not rely solely on application-layer access control.
• Every table that holds client data should carry a jurisdiction column — enables data residency filtering and compliance reporting.

2. Prompt-completion store

All LLM interactions must be logged for:

• Audit / accountability — legal professionals may need to show which AI responses informed a work product
• Quality monitoring — flagging hallucinations, tracking skill hit rates
• Billing — token-based cost attribution per workspace or matter

Minimum schema:

prompt_completions (
  id          uuid PRIMARY KEY,
  workspace_id uuid NOT NULL,
  matter_id   uuid,
  user_id     uuid NOT NULL,
  skill_id    text,               -- which skill routed this
  model       text,               -- e.g. claude-sonnet-4-5
  prompt_tokens  int,
  completion_tokens int,
  latency_ms  int,
  created_at  timestamptz NOT NULL DEFAULT now(),
  jurisdiction text,
  -- do NOT store raw prompt/completion in main table; link to encrypted blob
  blob_ref    text                -- S3/GCS key of encrypted content
)

Privacy design: Never store raw prompt text in the operational database unencrypted. Store a reference to an encrypted blob in object storage. Apply retention policies per workspace (configurable, minimum 90 days for legal-grade accounts, deletion on request).

3. Audit log store

Immutable event stream of all actions taken on the platform:

• Document uploads, downloads, deletions
• Skill invocations with outcome (success / flagged / escalated)
• Permission changes, user additions, billing events
• Data exports

Use an append-only table or a dedicated audit service (e.g. write to a separate Postgres schema with no DELETE or UPDATE grants to the application role). For compliance-grade deployments in UAE PDPL or KSA PDPL contexts, the audit log may itself need to be retained for a regulatory minimum period.

4. Vector / embedding store

Used for retrieval-augmented generation (RAG) over firm documents:

• Pinecone, pgvector, or Weaviate depending on scale
• Each embedding record should carry: document_id, chunk_index, jurisdiction, matter_id, workspace_id, model_version (for re-embedding when model changes)
• Access control at query time: filters must enforce workspace_id isolation — cross-workspace retrieval is a critical security failure mode in multi-tenant legal products

5. Analytics warehouse

Feed from the operational DB via CDC or nightly export:

• dbt models transform raw event tables into fact/dimension schema
• Key marts: fct_sessions, fct_skill_invocations, dim_users, dim_workspaces, fct_documents_processed
• Run on BigQuery (large scale) or a dbt-core local project against Postgres (early stage)

dbt conventions for legal-AI analytics

models/
  staging/       -- 1:1 with source tables, light cleaning only
  intermediate/  -- joins, window functions, business logic
  marts/
    growth/      -- activation, retention, expansion metrics
    product/     -- feature usage, skill hit rates, latency
    finance/     -- MRR, ARR, billing events (see [[wiki-finance]])

Key metrics to model:

• Skill utilisation rate — skill_invocations / active_sessions by skill_id
• Matter completion rate — sessions that produce a document or explicit user-confirmed output
• Escalation rate — skill-level flag rate (AI declines, routes to lawyer)
• Time-to-first-value — minutes from signup to first completed skill invocation

Privacy and data residency

MENA jurisdictions impose data residency requirements that affect warehouse choices:

For multi-region deployments: use Supabase projects or cloud regions per jurisdiction boundary; never co-mingle data from jurisdictions with conflicting residency rules in a single warehouse without proper anonymisation.

Observability and alerting

• Pipeline failures should alert within 5 minutes via PagerDuty or equivalent.
• Row count anomaly detection on key tables (prompt_completions, audit_log) — a sudden drop signals ingestion failure, not reduced usage.
• Data freshness SLAs: operational metrics < 5 min lag; warehouse models < 2 hr lag during business hours.

Caveats & currency

Data privacy laws in MENA are evolving rapidly. The frameworks cited above were current as of early 2026; check the relevant authority's official website (UAE TDRA, Saudi NCA, DIFC Commissioner of Data Protection) before implementing cross-border data flows. dbt and warehouse tool version requirements change frequently; pin versions in your packages.yml and test upgrades in a staging environment.

Related skills

• [[wiki-engineering]]
• [[wiki-memory]]
• [[wiki-frontend]]
• [[wiki-haqq-product]]
• [[wiki-finance]]