| 1 | -- Cloud facts publication storage (facts/v1). |
| 2 | -- |
| 3 | -- This is the missing DDL for storage that three existing contracts already |
| 4 | -- assume; it introduces no new catalog, publication service, provider, or |
| 5 | -- execution loop: |
| 6 | -- |
| 7 | -- * web/scripts/facts-publish.mjs — writes public.facts_key and |
| 8 | -- public.facts_release with the service-role key, looks channels up by |
| 9 | -- (scope = 'global', slug), reads back id/payload_sha256, and revokes by |
| 10 | -- (channel_id, facts_version). |
| 11 | -- * web/lib/cloud-facts.ts — reads exactly one row of public.facts_current |
| 12 | -- over PostgREST with the publishable key: |
| 13 | -- channel=eq.<slug>&scope=eq.global&limit=1, selecting channel, |
| 14 | -- release_id, facts_version, schema_version, envelope_version, applies_to, |
| 15 | -- key_id, payload_b64, sig_b64, sigs, payload_sha256, published_at, |
| 16 | -- not_after. |
| 17 | -- * docs/CLOUD_FACTS.md — "facts_current must be a read-only view with |
| 18 | -- explicit SELECT grants, RLS and policies limited to published public |
| 19 | -- channels." |
| 20 | -- |
| 21 | -- Trust model: the database is an untrusted transport. Signatures are verified |
| 22 | -- by clients against keys pinned in the binary (crates/config/src/cloud_facts/ |
| 23 | -- keys.rs) and in web/lib/cloud-facts/keys.ts. public.facts_key is an operator |
| 24 | -- registry, never a trust root, so it is not exposed to anon/authenticated at |
| 25 | -- all and no row here can make a fixture key trusted. |
| 26 | -- |
| 27 | -- Rollback: public.facts_channel.max_facts_version is a high-water mark that |
| 28 | -- never decreases. facts_current serves the head version only, so revoking the |
| 29 | -- head makes the channel serve nothing (HTTP 404 "no-facts") instead of |
| 30 | -- silently re-serving an older accepted version. The fix for a bad release is |
| 31 | -- publishing a higher facts_version; clients enforce their own version floor |
| 32 | -- as well. |
| 33 | -- |
| 34 | -- Re-applying this file is safe (no statement errors on a database that |
| 35 | -- already has these objects), but it does NOT reconcile a pre-existing table |
| 36 | -- whose columns or constraints differ: compare against the live schema before |
| 37 | -- applying to a project that already carries these tables. |
| 38 | |
| 39 | -- --------------------------------------------------------------------------- |
| 40 | -- Tables |
| 41 | -- --------------------------------------------------------------------------- |
| 42 | |
| 43 | create table if not exists public.facts_channel ( |
| 44 | id uuid primary key default gen_random_uuid(), |
| 45 | scope text not null default 'global' |
| 46 | constraint facts_channel_scope_check check (scope ~ '^[a-z0-9][a-z0-9-]{0,31}$'), |
| 47 | slug text not null |
| 48 | constraint facts_channel_slug_check check (slug ~ '^[a-z0-9][a-z0-9-]{0,31}$'), |
| 49 | -- Fail closed: a new channel is invisible to anon until it is deliberately |
| 50 | -- made public. |
| 51 | visibility text not null default 'private' |
| 52 | constraint facts_channel_visibility_check check (visibility in ('public', 'private')), |
| 53 | -- Highest facts_version ever accepted for this channel; maintained by |
| 54 | -- public.facts_release_guard() and never decreased. |
| 55 | max_facts_version bigint not null default 0 |
| 56 | constraint facts_channel_max_version_check check (max_facts_version >= 0), |
| 57 | created_at timestamptz not null default now(), |
| 58 | constraint facts_channel_scope_slug_key unique (scope, slug), |
| 59 | -- Only global-scope channels may be world-readable; organization-specific |
| 60 | -- trust is not implemented (docs/CLOUD_FACTS.md). |
| 61 | constraint facts_channel_public_is_global_check check (visibility = 'private' or scope = 'global') |
| 62 | ); |
| 63 | |
| 64 | create table if not exists public.facts_key ( |
| 65 | key_id text primary key |
| 66 | constraint facts_key_key_id_check check (key_id ~ '^cwf-[a-z0-9-]{1,32}$'), |
| 67 | scope text not null default 'global' |
| 68 | constraint facts_key_scope_check check (scope ~ '^[a-z0-9][a-z0-9-]{0,31}$'), |
| 69 | algorithm text not null default 'ed25519' |
| 70 | constraint facts_key_algorithm_check check (algorithm = 'ed25519'), |
| 71 | -- Standard base64 of a raw 32-byte Ed25519 public key. Informational only. |
| 72 | public_key text not null |
| 73 | constraint facts_key_public_key_check check (public_key ~ '^[A-Za-z0-9+/]{43}=$'), |
| 74 | status text not null default 'active' |
| 75 | constraint facts_key_status_check check (status in ('active', 'retired')), |
| 76 | created_at timestamptz not null default now() |
| 77 | ); |
| 78 | |
| 79 | create table if not exists public.facts_release ( |
| 80 | id uuid primary key default gen_random_uuid(), |
| 81 | channel_id uuid not null |
| 82 | references public.facts_channel (id) on update cascade on delete restrict, |
| 83 | -- Positive and inside the JavaScript safe-integer range the clients require. |
| 84 | facts_version bigint not null |
| 85 | constraint facts_release_facts_version_check check (facts_version between 1 and 9007199254740991), |
| 86 | schema_version integer not null default 1 |
| 87 | constraint facts_release_schema_version_check check (schema_version between 1 and 1000), |
| 88 | envelope_version integer not null default 1 |
| 89 | constraint facts_release_envelope_version_check check (envelope_version between 1 and 1000), |
| 90 | applies_to text not null |
| 91 | constraint facts_release_applies_to_check check ( |
| 92 | length(applies_to) between 1 and 200 |
| 93 | and applies_to ~ '^(\*|(>=|<=|>|<|=|\^|~)?\s*\d+(\.\d+){0,2}(-[0-9A-Za-z.-]+)?(\s*,\s*(>=|<=|>|<|=|\^|~)?\s*\d+(\.\d+){0,2}(-[0-9A-Za-z.-]+)?)*)$' |
| 94 | ), |
| 95 | key_id text not null |
| 96 | references public.facts_key (key_id) on update cascade on delete restrict, |
| 97 | -- Exactly the signed bytes the clients verify: canonical base64, no newline |
| 98 | -- wrapping, decoding to at most MAX_PAYLOAD_BYTES (512 KiB). |
| 99 | payload_b64 text not null |
| 100 | constraint facts_release_payload_b64_check check ( |
| 101 | octet_length(payload_b64) between 4 and 699052 |
| 102 | and octet_length(payload_b64) % 4 = 0 |
| 103 | and payload_b64 ~ '^[A-Za-z0-9+/]+={0,2}$' |
| 104 | and octet_length(decode(payload_b64, 'base64')) <= 524288 |
| 105 | ), |
| 106 | -- Base64 of a 64-byte Ed25519 signature. |
| 107 | sig_b64 text not null |
| 108 | constraint facts_release_sig_b64_check check (sig_b64 ~ '^[A-Za-z0-9+/]{86}==$'), |
| 109 | -- Extra rotation signatures; element shape is enforced in the guard trigger. |
| 110 | sigs jsonb not null default '[]'::jsonb |
| 111 | constraint facts_release_sigs_check check (jsonb_typeof(sigs) = 'array' and jsonb_array_length(sigs) <= 7), |
| 112 | -- Operator-visible decode of payload_b64. Never exposed to anon: the signed |
| 113 | -- bytes are the only representation a client is allowed to consume. |
| 114 | payload jsonb not null |
| 115 | constraint facts_release_payload_object_check check (jsonb_typeof(payload) = 'object'), |
| 116 | -- Derived from the decoded signed payload, so it cannot disagree with it. |
| 117 | payload_sha256 text generated always as (encode(sha256(decode(payload_b64, 'base64')), 'hex')) stored, |
| 118 | published_at timestamptz not null, |
| 119 | not_after timestamptz |
| 120 | constraint facts_release_not_after_check check (not_after is null or not_after > published_at), |
| 121 | status text not null default 'published' |
| 122 | constraint facts_release_status_check check (status in ('published', 'revoked')), |
| 123 | revoked_at timestamptz, |
| 124 | revoke_reason text, |
| 125 | published_by text not null default '' |
| 126 | constraint facts_release_published_by_check check (length(published_by) <= 200), |
| 127 | notes text not null default '' |
| 128 | constraint facts_release_notes_check check (length(notes) <= 4000), |
| 129 | created_at timestamptz not null default now(), |
| 130 | constraint facts_release_channel_version_key unique (channel_id, facts_version), |
| 131 | constraint facts_release_revocation_check check ( |
| 132 | case status |
| 133 | when 'published' then revoked_at is null and revoke_reason is null |
| 134 | else revoked_at is not null and length(coalesce(revoke_reason, '')) between 1 and 500 |
| 135 | end |
| 136 | ), |
| 137 | -- payload must be the JSON carried by payload_b64, byte for byte after |
| 138 | -- decoding, so an operator cannot store a second, divergent representation. |
| 139 | constraint facts_release_payload_bytes_check check ( |
| 140 | convert_from(decode(payload_b64, 'base64'), 'utf8')::jsonb = payload |
| 141 | ), |
| 142 | -- Outer columns must repeat what the signed payload says; the clients reject |
| 143 | -- any envelope whose metadata disagrees with its payload. |
| 144 | constraint facts_release_payload_meta_check check (( |
| 145 | payload -> 'facts_version' = to_jsonb(facts_version) |
| 146 | and payload -> 'schema_version' = to_jsonb(schema_version) |
| 147 | and payload -> 'applies_to' = to_jsonb(applies_to) |
| 148 | and payload ->> 'published_at' ~ '^\d{4}-\d{2}-\d{2}T\d{2}:\d{2}:\d{2}(\.\d{1,3})?Z$' |
| 149 | and (payload ->> 'published_at')::timestamptz = published_at |
| 150 | and case |
| 151 | when not_after is null then payload ->> 'not_after' is null |
| 152 | else payload ->> 'not_after' ~ '^\d{4}-\d{2}-\d{2}T\d{2}:\d{2}:\d{2}(\.\d{1,3})?Z$' |
| 153 | and (payload ->> 'not_after')::timestamptz = not_after |
| 154 | end |
| 155 | ) is true) |
| 156 | ); |
| 157 | |
| 158 | comment on table public.facts_channel is |
| 159 | 'Cloud facts (facts/v1) delivery channels. Only visibility = ''public'' global-scope channels reach anon through public.facts_current.'; |
| 160 | comment on column public.facts_channel.max_facts_version is |
| 161 | 'Never-decreasing high-water mark of accepted facts_version values; blocks re-publication of an older version after revocation.'; |
| 162 | comment on table public.facts_key is |
| 163 | 'Operator registry of Ed25519 verifying keys. Informational only: trust comes from the keys pinned in the clients, never from this table, so it is not exposed to anon or authenticated.'; |
| 164 | comment on table public.facts_release is |
| 165 | 'Signed facts/v1 envelopes. Append-only apart from revocation; the signed columns are immutable once inserted.'; |
| 166 | comment on column public.facts_release.payload is |
| 167 | 'Decoded signed payload for operators. Not granted to anon: clients must consume payload_b64 and verify it.'; |
| 168 | |
| 169 | -- --------------------------------------------------------------------------- |
| 170 | -- Guard trigger: monotonic versions, channel agreement, signed-row immutability |
| 171 | -- --------------------------------------------------------------------------- |
| 172 | |
| 173 | create or replace function public.facts_release_guard() |
| 174 | returns trigger |
| 175 | language plpgsql |
| 176 | security invoker |
| 177 | set search_path = '' |
| 178 | as $facts_release_guard$ |
| 179 | declare |
| 180 | channel_row public.facts_channel%rowtype; |
| 181 | extra_sig jsonb; |
| 182 | begin |
| 183 | if tg_op = 'UPDATE' then |
| 184 | if new.id is distinct from old.id |
| 185 | or new.channel_id is distinct from old.channel_id |
| 186 | or new.facts_version is distinct from old.facts_version |
| 187 | or new.schema_version is distinct from old.schema_version |
| 188 | or new.envelope_version is distinct from old.envelope_version |
| 189 | or new.applies_to is distinct from old.applies_to |
| 190 | or new.key_id is distinct from old.key_id |
| 191 | or new.payload_b64 is distinct from old.payload_b64 |
| 192 | or new.sig_b64 is distinct from old.sig_b64 |
| 193 | or new.sigs is distinct from old.sigs |
| 194 | or new.payload is distinct from old.payload |
| 195 | or new.published_at is distinct from old.published_at |
| 196 | or new.not_after is distinct from old.not_after then |
| 197 | raise exception 'signed columns of facts_release % are immutable; publish a higher facts_version instead', old.id |
| 198 | using errcode = 'restrict_violation'; |
| 199 | end if; |
| 200 | if old.status = 'revoked' and new.status is distinct from 'revoked' then |
| 201 | raise exception 'facts_release % cannot be un-revoked; publish a higher facts_version instead', old.id |
| 202 | using errcode = 'restrict_violation'; |
| 203 | end if; |
| 204 | return new; |
| 205 | end if; |
| 206 | |
| 207 | -- Serialize concurrent publications to the same channel. |
| 208 | select * into channel_row from public.facts_channel where id = new.channel_id for update; |
| 209 | if not found then |
| 210 | raise exception 'facts_channel % does not exist', new.channel_id |
| 211 | using errcode = 'foreign_key_violation'; |
| 212 | end if; |
| 213 | |
| 214 | if new.payload -> 'channel' is distinct from to_jsonb(channel_row.slug) then |
| 215 | raise exception 'signed payload channel % does not match channel %', |
| 216 | coalesce(new.payload ->> 'channel', '<missing>'), channel_row.slug |
| 217 | using errcode = 'check_violation'; |
| 218 | end if; |
| 219 | |
| 220 | if new.facts_version <= channel_row.max_facts_version then |
| 221 | raise exception 'facts_version % is not above the published high-water mark % for channel %', |
| 222 | new.facts_version, channel_row.max_facts_version, channel_row.slug |
| 223 | using errcode = 'unique_violation'; |
| 224 | end if; |
| 225 | |
| 226 | for extra_sig in select value from jsonb_array_elements(new.sigs) loop |
| 227 | if jsonb_typeof(extra_sig) <> 'object' |
| 228 | or coalesce(extra_sig ->> 'key_id', '') !~ '^cwf-[a-z0-9-]{1,32}$' |
| 229 | or coalesce(extra_sig ->> 'sig_b64', '') !~ '^[A-Za-z0-9+/]{86}==$' then |
| 230 | raise exception 'extra signature % is not a well-formed {key_id, sig_b64} pair', extra_sig |
| 231 | using errcode = 'check_violation'; |
| 232 | end if; |
| 233 | end loop; |
| 234 | |
| 235 | update public.facts_channel set max_facts_version = new.facts_version where id = new.channel_id; |
| 236 | return new; |
| 237 | end; |
| 238 | $facts_release_guard$; |
| 239 | |
| 240 | comment on function public.facts_release_guard() is |
| 241 | 'Keeps facts_release versions monotonic per channel, binds the signed payload channel to the channel row, validates extra signature shape, and freezes signed columns after insert.'; |
| 242 | |
| 243 | drop trigger if exists facts_release_guard on public.facts_release; |
| 244 | create trigger facts_release_guard |
| 245 | before insert or update on public.facts_release |
| 246 | for each row execute function public.facts_release_guard(); |
| 247 | |
| 248 | -- --------------------------------------------------------------------------- |
| 249 | -- Read view: one row per public channel, head version only |
| 250 | -- --------------------------------------------------------------------------- |
| 251 | |
| 252 | drop view if exists public.facts_current; |
| 253 | create view public.facts_current |
| 254 | with (security_invoker = true, security_barrier = true) as |
| 255 | select |
| 256 | c.slug as channel, |
| 257 | c.scope as scope, |
| 258 | r.id as release_id, |
| 259 | r.facts_version, |
| 260 | r.schema_version, |
| 261 | r.envelope_version, |
| 262 | r.applies_to, |
| 263 | r.key_id, |
| 264 | r.payload_b64, |
| 265 | r.sig_b64, |
| 266 | r.sigs, |
| 267 | r.payload_sha256, |
| 268 | r.published_at, |
| 269 | r.not_after |
| 270 | from public.facts_channel c |
| 271 | join public.facts_release r |
| 272 | on r.channel_id = c.id |
| 273 | and r.facts_version = c.max_facts_version |
| 274 | where c.visibility = 'public' |
| 275 | and r.status = 'published' |
| 276 | and r.published_at <= now() |
| 277 | and (r.not_after is null or r.not_after > now()); |
| 278 | |
| 279 | comment on view public.facts_current is |
| 280 | 'Public read surface for facts/v1: at most one row per public global channel, always the head facts_version. A revoked, future-dated or expired head yields no row rather than an older version.'; |
| 281 | |
| 282 | -- --------------------------------------------------------------------------- |
| 283 | -- Row level security |
| 284 | -- --------------------------------------------------------------------------- |
| 285 | |
| 286 | alter table public.facts_channel enable row level security; |
| 287 | alter table public.facts_key enable row level security; |
| 288 | alter table public.facts_release enable row level security; |
| 289 | |
| 290 | drop policy if exists facts_channel_public_read on public.facts_channel; |
| 291 | create policy facts_channel_public_read on public.facts_channel |
| 292 | for select to anon, authenticated |
| 293 | using (visibility = 'public'); |
| 294 | |
| 295 | drop policy if exists facts_release_public_read on public.facts_release; |
| 296 | create policy facts_release_public_read on public.facts_release |
| 297 | for select to anon, authenticated |
| 298 | using ( |
| 299 | status = 'published' |
| 300 | and published_at <= now() |
| 301 | and (not_after is null or not_after > now()) |
| 302 | and exists ( |
| 303 | select 1 from public.facts_channel c |
| 304 | where c.id = facts_release.channel_id and c.visibility = 'public' |
| 305 | ) |
| 306 | ); |
| 307 | |
| 308 | -- public.facts_key deliberately carries no policy: RLS with no policy denies |
| 309 | -- every anon/authenticated row, and the registry is not a trust root. |
| 310 | |
| 311 | -- --------------------------------------------------------------------------- |
| 312 | -- Explicit grants (no wildcards, no writes for anon or authenticated) |
| 313 | -- --------------------------------------------------------------------------- |
| 314 | |
| 315 | grant usage on schema public to anon, authenticated, service_role; |
| 316 | |
| 317 | revoke all on public.facts_channel from public, anon, authenticated; |
| 318 | revoke all on public.facts_key from public, anon, authenticated; |
| 319 | revoke all on public.facts_release from public, anon, authenticated; |
| 320 | revoke all on public.facts_current from public, anon, authenticated; |
| 321 | revoke all on function public.facts_release_guard() from public; |
| 322 | |
| 323 | -- security_invoker views check base-table privileges as the caller, so anon |
| 324 | -- needs column privileges for exactly the columns facts_current reads. |
| 325 | grant select (id, scope, slug, visibility, max_facts_version) |
| 326 | on public.facts_channel to anon, authenticated; |
| 327 | grant select ( |
| 328 | id, channel_id, facts_version, schema_version, envelope_version, applies_to, |
| 329 | key_id, payload_b64, sig_b64, sigs, payload_sha256, published_at, not_after, status |
| 330 | ) on public.facts_release to anon, authenticated; |
| 331 | grant select on public.facts_current to anon, authenticated; |
| 332 | |
| 333 | -- The publisher (web/scripts/facts-publish.mjs) uses the service-role key. |
| 334 | -- No delete on facts_release: publication history is retained, and a bad |
| 335 | -- release is revoked, never erased. |
| 336 | grant select, insert, update, delete on public.facts_channel to service_role; |
| 337 | grant select, insert, update on public.facts_key to service_role; |
| 338 | grant select, insert, update on public.facts_release to service_role; |
| 339 | grant select on public.facts_current to service_role; |
| 340 | |
| 341 | -- Channels are created by an authorized operator, one row at a time, and stay |
| 342 | -- private until publication is separately approved: |
| 343 | -- insert into public.facts_channel (scope, slug) values ('global', 'stable'); |
| 344 | -- update public.facts_channel set visibility = 'public' |
| 345 | -- where scope = 'global' and slug = 'stable'; |
| 346 | |
| 347 | notify pgrst, 'reload schema'; |
| 348 |