Supabase Integration
Store and query Observation records in Postgres — JSONB document with promoted columns
Observations are JSON-serializable by design, which makes Postgres + JSONB a natural store. This guide shows the pattern we recommend: the record as a JSONB document, with the fields you query promoted to generated columns.
The table
create table observations (
id text primary key,
record jsonb not null,
-- promoted columns, generated from the document (always in sync)
status text generated always as (record->>'status') stored,
event_date date generated always as ((record->'temporal'->>'date')::date) stored,
latitude double precision generated always as ((record->'location'->>'latitude')::double precision) stored,
longitude double precision generated always as ((record->'location'->>'longitude')::double precision) stored,
country text generated always as (record->'location'->>'country') stored,
created_at timestamptz not null default now(),
updated_at timestamptz not null default now()
);
create index observations_event_date_idx on observations (event_date);
create index observations_status_idx on observations (status);
create index observations_record_gin on observations using gin (record jsonb_path_ops);Why this shape:
- The document is the source of truth. The standard's schema evolves by versioned releases; your table doesn't need a migration when an optional field is added.
- Generated columns can't drift. They're computed from the document on write — no dual-write bugs.
- The GIN index covers ad-hoc containment queries on everything you didn't promote.
Validate at the boundary, store what was validated
import { createClient } from '@supabase/supabase-js';
import { parseEnrichedObservation, type EnrichedObservation } from '@disclosureos/schema';
const supabase = createClient(process.env.SUPABASE_URL!, process.env.SUPABASE_ANON_KEY!);
export async function saveObservation(input: unknown) {
const result = parseEnrichedObservation(input);
if (!result.success) {
return { error: result.issues };
}
const obs = result.data!;
const { error } = await supabase
.from('observations')
.upsert({ id: obs.id, record: obs, updated_at: new Date().toISOString() });
return { error };
}The database stores only records that passed the full contract — core plus slots. Postgres never becomes the place where invalid data hides.
Querying
Promoted columns for the hot paths:
const { data } = await supabase
.from('observations')
.select('id, record')
.eq('status', 'published')
.gte('event_date', '2004-01-01')
.order('event_date', { ascending: false });
const observations = (data ?? []).map((row) => row.record as EnrichedObservation);JSONB containment for everything else — e.g. records with any origin claim, or military witnesses:
// any record claiming instantaneous acceleration
const { data } = await supabase
.from('observations')
.select('id, record')
.not('record->observableAssessments->technology->instantaneous_acceleration', 'is', null);
// containment via the GIN index
const { data: military } = await supabase
.from('observations')
.select('id, record')
.contains('record', { witnesses: { militaryWitnesses: true } });Scoring on read
Scores are derived data — compute them in the app layer (and cache if needed) rather than storing them in the record:
import { rankByCompellingness } from '@disclosureos/scoring';
const ranked = rankByCompellingness(observations);If you need scores queryable in SQL, store them in a separate observation_scores table stamped with scoringVersion, and treat it as a rebuildable cache — never as part of the record.
Row Level Security
Standard Supabase practice applies. A minimal published-only read policy:
alter table observations enable row level security;
create policy "public read of published"
on observations for select
using (status = 'published');
create policy "authenticated write"
on observations for insert to authenticated
with check (true);status is a generated column, so the policy follows the document automatically.
Schema versioning
Stamp records with the contract version on write (schemaVersion is a core field), so future migrations know exactly what they're reading:
import { ENRICHED_OBSERVATION_SCHEMA_VERSION } from '@disclosureos/schema';
const obs = { ...base, schemaVersion: ENRICHED_OBSERVATION_SCHEMA_VERSION };For database-side validation in other languages or in Postgres functions, the composed JSON Schema artifact is the same contract this guide validates against in TypeScript.