Database Handbook

Supabase schema, migrations, and data types in KAP

Author

Kav AI

Published

April 18, 2026

1 Overview

KAP uses Supabase (managed Postgres, Auth, Storage, Realtime) as the primary data store for the web app, agent tooling, and reporting. Application code talks to Postgres through PostgREST (@supabase/supabase-js on the server and client) under Row Level Security (RLS).

This handbook summarizes how the database fits the product, where migrations live, and how TypeScript types stay aligned with the live schema.

1.1 Product data types (conceptual)

Day-to-day features are organized around three user-visible data types; table names in Postgres may differ until a future rename.

Concept Primary table / API today
Datasets datasets (campaigns are dataset rows under an organization)
Notes location_notes (REST and routes under /api/.../notes)
Reports pdf_reports (PDF URLs plus KRML fields: report_id, rendered, metadata, optional template_name)

Work-order-style workflows use notes with structured metadata (for example kind: "work_order") rather than a separate work_orders table unless reporting needs justify one later.

1.1.1 KRML — shared report format (not engine-specific)

KRML (Kav AI Report Markup Language) is a platform-level convention: structured report sections, rendered Markdown (and optional PDF binaries via url), and JSON metadata stored on pdf_reports. Any part of the stack may produce or consume KRML-shaped rows—web exports, batch jobs, AG-UI chat report pipelines, or agent runtimes—as long as they honor the same table and metadata shapes (e.g. kind: "krml_report", layout templates with kind: "krml_layout_template").

The Hermes agent runtime currently ships the reference krml_* tool implementations (krml_render_report, krml_save_report, etc.) under ai/src/kavai/systems/hermes/. That is one integration path, not the definition of KRML. New engines or services should reuse pdf_reports and the same field semantics rather than inventing parallel report tables.

1.2 Schema changes (migrations)

  • Location: supabase/migrations/ at the repository root.
  • Apply: Use your team’s standard path (Supabase CLI db push, Dashboard SQL, or CI) so supabase_migrations.schema_migrations stays in sync with the repo.
  • Idempotency: Prefer IF NOT EXISTS / DROP IF EXISTS patterns where replays or branches are common.

New tables and columns should follow existing RLS patterns: gate access through organization membership (organization_members + datasets.organization_id) unless the row is intentionally public.

1.3 TypeScript types

  • Generated file: web/lib/database.generated.ts (and companions if your project splits types).
  • Refresh: From web/, run npm run db:types after migrations are applied (requires Supabase CLI and project linkage as documented in the web package).

Do not hand-edit generated tables for long; regenerate after DDL changes so inserts and selects stay type-safe.

1.4 pdf_reports column semantics (KRML)

Persisted reports and templates live in pdf_reports (legacy hermes_* tables were removed in favor of this single store):

  • Report rows: report_id, name (title), rendered (Markdown), status, facility, metadata (typically includes kind: "krml_report").
  • Layout templates: rows with template_name set and metadata.kind: "krml_layout_template" (section_order, section_config).

Hermes registers LLM-callable tools named krml_* (krml_save_report, …) implemented in ai/src/kavai/systems/hermes/tools/krml_renderer.py and skills/krml_composition.py. Web or other services should use the same columns and metadata conventions when writing report rows so all consumers stay interoperable.

1.6 Quick reference (common tables)

The live project may include additional tables (Auth, Storage, Realtime, extensions). Application tables frequently touched by KAP include:

  • organizations, organization_members, profiles
  • datasets, images, data_groups
  • location_notes, annotations
  • pdf_reports, chat_sessions, messages
  • gas_readings, equipment, investigations (and related domain tables)

Use Supabase Studio or information_schema when you need an authoritative column list for your environment.