coven_database/
coven_schema.rs

1//! coven's bookkeeping schema.
2//!
3//! coven owns its device-local bookkeeping tables — `protocol_state`,
4//! `materialized_commits`, `snapshot_coverage`, `store_writes`,
5//! `outbound_membership_mutation`, `outbound_store_snapshot`,
6//! `local_blob_refs`, `local_cleanup_intents`, exact prepared Store objects, and
7//! row-bound blob locators — all
8//! created STRICT by `apply_coven_schema`, which coven
9//! runs against the connection it owns during open. The host does not implement
10//! any of this; app SQL goes through `CovenHandle::write` or `CovenHandle::read`.
11
12use crate::coven_schema_definitions::{
13    BLOB_MAKE_REMOTE_INTENTS_COLUMNS, BLOB_MAKE_REMOTE_INTENTS_V0_COLUMNS, CLOUD_OUTBOX_COLUMNS,
14    CLOUD_OUTBOX_V0_COLUMNS, OBJECT_OWNERSHIP_TRIGGERS,
15};
16use crate::{query_mapped_rows, DbError};
17
18macro_rules! coven_tables {
19    ($visit:ident) => {
20        $visit!(
21            protocol_state,
22            "
23    key TEXT PRIMARY KEY,
24    value TEXT NOT NULL
25"
26        );
27        $visit!(
28            retained_replay_baselines,
29            "
30    singleton INTEGER PRIMARY KEY CHECK (singleton = 1),
31    generation INTEGER NOT NULL CHECK (generation >= 0),
32    exact_cut TEXT NOT NULL CHECK (json_valid(exact_cut)),
33    schema_version INTEGER NOT NULL CHECK (schema_version >= 0),
34    routing_hash TEXT NOT NULL CHECK (length(routing_hash) = 64),
35    image_payload_hash TEXT NOT NULL CHECK (length(image_payload_hash) = 64),
36    authority_hash TEXT NOT NULL CHECK (length(authority_hash) = 64)
37"
38        );
39        $visit!(
40            retained_replay_blob_leases,
41            "
42    namespace TEXT NOT NULL,
43    blob_id TEXT NOT NULL,
44    PRIMARY KEY (namespace, blob_id)
45"
46        );
47        $visit!(
48            circle_bootstrap_coverage,
49            "
50    circle_id TEXT PRIMARY KEY,
51    control_coord TEXT NOT NULL CHECK (json_valid(control_coord)),
52    activation_commit TEXT NOT NULL CHECK (json_valid(activation_commit)),
53    exact_cut TEXT NOT NULL CHECK (json_valid(exact_cut)),
54    image_hash TEXT NOT NULL CHECK (length(image_hash) = 64),
55    bootstrap_ref BLOB NOT NULL CHECK (length(bootstrap_ref) > 0)
56"
57        );
58        $visit!(
59            circle_close_exclusions,
60            "
61    circle_id TEXT PRIMARY KEY,
62    close_id TEXT NOT NULL,
63    excluded_registration TEXT NOT NULL CHECK (json_valid(excluded_registration)),
64    successor_control TEXT NOT NULL CHECK (json_valid(successor_control)),
65    activating_commit TEXT NOT NULL CHECK (json_valid(activating_commit))
66"
67        );
68        $visit!(
69            retained_merge_materializations,
70            "
71    device_id TEXT NOT NULL,
72    seq INTEGER NOT NULL CHECK (seq > 0),
73    commit_ref TEXT NOT NULL CHECK (json_valid(commit_ref)),
74    input_hash TEXT NOT NULL CHECK (length(input_hash) = 64),
75    canonical_input BLOB NOT NULL CHECK (length(canonical_input) > 0),
76    PRIMARY KEY (device_id, seq),
77    UNIQUE (device_id, seq, commit_ref, input_hash)
78"
79        );
80        $visit!(
81            merge_retraction_cleanups,
82            "
83    device_id TEXT NOT NULL,
84    seq INTEGER NOT NULL CHECK (seq > 0),
85    commit_ref TEXT NOT NULL CHECK (json_valid(commit_ref)),
86    cleanup_hash TEXT NOT NULL CHECK (length(cleanup_hash) = 64),
87    canonical_cleanup BLOB NOT NULL CHECK (length(canonical_cleanup) > 0),
88    PRIMARY KEY (device_id, seq),
89    UNIQUE (commit_ref)
90"
91        );
92        $visit!(
93            materialized_commits,
94            "
95    device_id TEXT NOT NULL,
96    seq INTEGER NOT NULL CHECK (seq > 0),
97    commit_ref TEXT NOT NULL CHECK (json_valid(commit_ref)),
98    retained_commit_ref TEXT NOT NULL CHECK (json_valid(retained_commit_ref)),
99    retained_input_hash TEXT NOT NULL CHECK (length(retained_input_hash) = 64),
100    PRIMARY KEY (device_id, seq),
101    FOREIGN KEY (device_id, seq, retained_commit_ref, retained_input_hash)
102        REFERENCES retained_merge_materializations(device_id, seq, commit_ref, input_hash)
103"
104        );
105        $visit!(
106            stream_activations,
107            "
108    activation_id TEXT PRIMARY KEY CHECK (length(activation_id) = 64),
109    author_stream_id TEXT NOT NULL UNIQUE CHECK (length(author_stream_id) = 64),
110    activation BLOB NOT NULL,
111    activating_commit TEXT NOT NULL CHECK (json_valid(activating_commit))
112"
113        );
114        $visit!(
115            snapshot_coverage,
116            "
117    device_id TEXT PRIMARY KEY,
118    seq INTEGER NOT NULL CHECK (seq > 0),
119    commit_ref TEXT NOT NULL CHECK (json_valid(commit_ref)),
120    snapshot_hash TEXT NOT NULL CHECK (length(snapshot_hash) = 64)
121"
122        );
123        $visit!(
124            store_publication_current,
125            "
126    singleton INTEGER PRIMARY KEY CHECK (singleton = 1),
127    record_hash TEXT NOT NULL UNIQUE CHECK (length(record_hash) = 64),
128    record_bytes BLOB NOT NULL CHECK (length(record_bytes) > 0),
129    provider_version TEXT NOT NULL CHECK (length(provider_version) > 0)
130"
131        );
132        $visit!(
133            local_blob_refs,
134            "
135    table_name TEXT NOT NULL,
136    row_id TEXT NOT NULL,
137    column_name TEXT NOT NULL,
138    row_stamp TEXT NOT NULL,
139    namespace TEXT NOT NULL,
140    blob_id TEXT NOT NULL,
141    path TEXT NOT NULL,
142    plaintext_size INTEGER NOT NULL CHECK (plaintext_size >= 0),
143    plaintext_hash TEXT NOT NULL CHECK (length(plaintext_hash) = 64),
144    PRIMARY KEY (table_name, row_id, column_name, row_stamp)
145"
146        );
147        $visit!(cloud_outbox, CLOUD_OUTBOX_COLUMNS);
148        $visit!(blob_make_remote_intents, BLOB_MAKE_REMOTE_INTENTS_COLUMNS);
149        $visit!(
150            local_cleanup_intents,
151            "
152    namespace TEXT NOT NULL,
153    blob_id   TEXT NOT NULL,
154    copy_identity TEXT NOT NULL CHECK (copy_identity = 'local' OR length(copy_identity) = 64),
155    PRIMARY KEY (namespace, blob_id, copy_identity)
156"
157        );
158        $visit!(
159            published_blob_drop_intents,
160            "
161    seq INTEGER NOT NULL CHECK (seq > 0),
162    namespace TEXT NOT NULL,
163    blob_id TEXT NOT NULL,
164    size INTEGER NOT NULL CHECK (size >= 0),
165    plaintext_hash TEXT NOT NULL,
166    locator_hash TEXT NOT NULL,
167    disposition TEXT NOT NULL CHECK (disposition IN ('drop', 'cache', 'pin')),
168    PRIMARY KEY (seq, namespace, blob_id, locator_hash)
169"
170        );
171        $visit!(
172            store_writes,
173            "
174    ordinal INTEGER PRIMARY KEY AUTOINCREMENT,
175    write_id TEXT NOT NULL UNIQUE,
176    status TEXT NOT NULL CHECK (json_valid(status)),
177    affected_rows TEXT CHECK (affected_rows IS NULL OR json_valid(affected_rows)),
178    changeset_hash TEXT CHECK (changeset_hash IS NULL OR length(changeset_hash) = 64),
179    base TEXT CHECK (base IS NULL OR json_valid(base)),
180    blob_facts TEXT CHECK (blob_facts IS NULL OR json_valid(blob_facts)),
181    prepared TEXT CHECK (prepared IS NULL OR json_valid(prepared))
182"
183        );
184        $visit!(
185            store_write_blob_leases,
186            "
187    write_id TEXT NOT NULL,
188    namespace TEXT NOT NULL,
189    blob_id TEXT NOT NULL,
190    PRIMARY KEY (write_id, namespace, blob_id),
191    FOREIGN KEY (write_id) REFERENCES store_writes(write_id)
192"
193        );
194        $visit!(
195            store_write_partitions,
196            "
197    write_id TEXT NOT NULL,
198    audience TEXT NOT NULL,
199    control_coord TEXT,
200    changeset_hash TEXT NOT NULL CHECK (length(changeset_hash) = 64),
201    PRIMARY KEY (write_id, audience),
202    FOREIGN KEY (write_id) REFERENCES store_writes(write_id) ON DELETE CASCADE,
203    CHECK (
204        (audience IN ('store', 'local') AND control_coord IS NULL)
205        OR
206        (audience NOT IN ('store', 'local') AND json_valid(control_coord))
207    )
208"
209        );
210        $visit!(
211            remote_objects,
212            "
213    object_id TEXT PRIMARY KEY CHECK (length(object_id) = 64),
214    state TEXT NOT NULL CHECK (json_valid(state))
215"
216        );
217        $visit!(
218            retained_replay_objects,
219            "
220    device_id TEXT NOT NULL,
221    seq INTEGER NOT NULL CHECK (seq > 0),
222    commit_ref TEXT NOT NULL CHECK (json_valid(commit_ref)),
223    input_hash TEXT NOT NULL CHECK (length(input_hash) = 64),
224    object_id TEXT NOT NULL CHECK (length(object_id) = 64),
225    PRIMARY KEY (device_id, seq, object_id),
226    FOREIGN KEY (device_id, seq, commit_ref, input_hash)
227        REFERENCES retained_merge_materializations(device_id, seq, commit_ref, input_hash),
228    FOREIGN KEY (object_id) REFERENCES remote_objects(object_id)
229"
230        );
231        $visit!(
232            protocol_inert_objects,
233            "
234    object_id TEXT PRIMARY KEY CHECK (length(object_id) = 64),
235    state TEXT NOT NULL CHECK (json_valid(state))
236"
237        );
238        $visit!(
239            reclaimed_store_packages,
240            "
241    object_id TEXT PRIMARY KEY CHECK (length(object_id) = 64),
242    authorization_hash TEXT NOT NULL UNIQUE CHECK (length(authorization_hash) = 64),
243    state TEXT NOT NULL CHECK (json_valid(state)),
244    FOREIGN KEY (authorization_hash) REFERENCES store_reclaim_operations(authorization_hash)
245"
246        );
247        $visit!(
248            store_write_packages,
249            "
250    write_id TEXT NOT NULL,
251    audience TEXT NOT NULL,
252    remote_object_id TEXT NOT NULL CHECK (length(remote_object_id) = 64),
253    PRIMARY KEY (write_id, audience),
254    FOREIGN KEY (write_id) REFERENCES store_writes(write_id) ON DELETE CASCADE,
255    FOREIGN KEY (remote_object_id) REFERENCES remote_objects(object_id)
256"
257        );
258        $visit!(
259            store_write_blobs,
260            "
261    write_id TEXT NOT NULL,
262    audience TEXT NOT NULL,
263    locator_hash TEXT NOT NULL CHECK (length(locator_hash) = 64),
264    remote_object_id TEXT NOT NULL CHECK (length(remote_object_id) = 64),
265    spool_path TEXT,
266    PRIMARY KEY (write_id, audience, remote_object_id),
267    FOREIGN KEY (write_id) REFERENCES store_writes(write_id) ON DELETE CASCADE,
268    FOREIGN KEY (remote_object_id) REFERENCES remote_objects(object_id)
269"
270        );
271        $visit!(
272            blob_locators,
273            "
274    remote_object_id TEXT PRIMARY KEY CHECK (length(remote_object_id) = 64),
275    locator_hash TEXT NOT NULL CHECK (length(locator_hash) = 64),
276    FOREIGN KEY (remote_object_id) REFERENCES remote_objects(object_id)
277"
278        );
279        $visit!(
280            row_blob_locators,
281            "
282    table_name TEXT NOT NULL,
283    row_id TEXT NOT NULL,
284    column_name TEXT NOT NULL,
285    row_stamp TEXT NOT NULL,
286    audience_authority TEXT NOT NULL CHECK (json_valid(audience_authority)),
287    remote_object_id TEXT NOT NULL CHECK (length(remote_object_id) = 64),
288    PRIMARY KEY (table_name, row_id, column_name, row_stamp),
289    FOREIGN KEY (remote_object_id) REFERENCES blob_locators(remote_object_id)
290"
291        );
292        $visit!(
293            outbound_membership_mutation,
294            "
295    singleton INTEGER PRIMARY KEY CHECK (singleton = 1),
296    intent_hash TEXT NOT NULL CHECK (length(intent_hash) = 64),
297    plan_bytes BLOB NOT NULL,
298    progress_bytes BLOB NOT NULL
299"
300        );
301        $visit!(
302            outbound_store_snapshot,
303            "
304    singleton INTEGER PRIMARY KEY CHECK (singleton = 1),
305    snapshot_ref TEXT NOT NULL CHECK (json_valid(snapshot_ref)),
306    meta_prepared TEXT NOT NULL CHECK (json_valid(meta_prepared)),
307    image_ref TEXT NOT NULL CHECK (json_valid(image_ref)),
308    rollup_ref TEXT NOT NULL CHECK (json_valid(rollup_ref)),
309    meta_bytes BLOB NOT NULL,
310    blobs TEXT NOT NULL CHECK (json_valid(blobs))
311"
312        );
313        $visit!(
314            published_store_snapshot,
315            "
316    generation INTEGER PRIMARY KEY CHECK (generation >= 0),
317    snapshot_ref TEXT NOT NULL CHECK (json_valid(snapshot_ref)),
318    successor_slot TEXT NOT NULL CHECK (json_valid(successor_slot)),
319    meta_bytes BLOB NOT NULL
320"
321        );
322        $visit!(
323            snapshot_blob_spool_cleanup,
324            "
325    path TEXT PRIMARY KEY
326"
327        );
328        // Content-addressed payload bytes owned by bookkeeping rows. Every
329        // payload is compressed; the compressed size selects SQLite or a file
330        // in the payload area. `storage` is the authoritative dispatch tag, so
331        // a reader never probes both representations.
332        $visit!(
333            payload_storage,
334            "
335    payload_hash TEXT PRIMARY KEY CHECK (length(payload_hash) = 64),
336    payload_size INTEGER NOT NULL CHECK (payload_size >= 0),
337    storage TEXT NOT NULL CHECK (storage IN ('inline', 'file')),
338    compressed_bytes BLOB,
339    compressed_size INTEGER NOT NULL CHECK (compressed_size > 0),
340    CHECK (
341        (storage = 'inline' AND compressed_bytes IS NOT NULL
342         AND compressed_size = length(compressed_bytes)
343         AND compressed_size <= 65536)
344        OR
345        (storage = 'file' AND compressed_bytes IS NULL)
346    )
347"
348        );
349        // Payload storage the owning row no longer needs. The obligation names
350        // the content hash so the catalog can dispatch to inline bytes or the
351        // matching file without recording a movable filesystem path.
352        $visit!(
353            payload_cleanup,
354            "
355    payload_hash TEXT PRIMARY KEY CHECK (length(payload_hash) = 64)
356        REFERENCES payload_storage(payload_hash)
357"
358        );
359        // One owner's claim on one payload. Two rows can name the same
360        // payload — a Circle operation and the remote object it prepared both
361        // need the bytes — so a payload is deleted when its last claim goes,
362        // not when any one owner is done with it. `owner_key` names the row
363        // holding the claim ('circle-operation:<id>', 'remote-object:<id>'),
364        // so an orphan is traceable to the flow that leaked it.
365        $visit!(
366            payload_owners,
367            "
368    payload_hash TEXT NOT NULL CHECK (length(payload_hash) = 64),
369    owner_key TEXT NOT NULL CHECK (length(owner_key) > 0),
370    PRIMARY KEY (payload_hash, owner_key),
371    FOREIGN KEY (payload_hash) REFERENCES payload_storage(payload_hash)
372"
373        );
374        $visit!(
375            outbound_circle_snapshot,
376            "
377    circle_id TEXT PRIMARY KEY,
378    snapshot_ref TEXT NOT NULL CHECK (json_valid(snapshot_ref)),
379    meta_prepared TEXT NOT NULL CHECK (json_valid(meta_prepared)),
380    image_ref TEXT NOT NULL CHECK (json_valid(image_ref)),
381    meta_bytes BLOB NOT NULL,
382    blobs TEXT NOT NULL CHECK (json_valid(blobs))
383"
384        );
385        $visit!(
386            published_circle_snapshot,
387            "
388    circle_id TEXT NOT NULL,
389    generation INTEGER NOT NULL CHECK (generation >= 0),
390    snapshot_ref TEXT NOT NULL CHECK (json_valid(snapshot_ref)),
391    successor_slot TEXT NOT NULL CHECK (json_valid(successor_slot)),
392    cut TEXT NOT NULL CHECK (json_valid(cut)),
393    meta_bytes BLOB NOT NULL,
394    PRIMARY KEY (circle_id, generation)
395"
396        );
397        $visit!(
398            outbound_store_acks,
399            "
400    singleton INTEGER PRIMARY KEY CHECK (singleton = 1),
401    ack_ref TEXT NOT NULL CHECK (json_valid(ack_ref)),
402    ack_bytes BLOB NOT NULL,
403    prepared_object TEXT NOT NULL CHECK (json_valid(prepared_object)),
404    activation TEXT NOT NULL CHECK (json_valid(activation))
405"
406        );
407        $visit!(
408            published_store_acks,
409            "
410    singleton INTEGER PRIMARY KEY CHECK (singleton = 1),
411    ack_ref TEXT NOT NULL CHECK (json_valid(ack_ref)),
412    successor_slot TEXT NOT NULL CHECK (json_valid(successor_slot)),
413    -- What that acknowledgement asserted, and the commit that carried it, so the
414    -- next cycle can tell whether it still holds. NULL on the acknowledgements
415    -- installed while bootstrapping a device, which computed no assertion of
416    -- their own: the first cycle after one of those has no basis to skip, so it
417    -- acknowledges and records what it said.
418    standing TEXT CHECK (standing IS NULL OR json_valid(standing))
419"
420        );
421        $visit!(
422            outbound_circle_acks,
423            "
424    circle_id TEXT PRIMARY KEY,
425    ack_ref TEXT NOT NULL CHECK (json_valid(ack_ref)),
426    ack_bytes BLOB NOT NULL,
427    prepared_object TEXT NOT NULL CHECK (json_valid(prepared_object))
428"
429        );
430        $visit!(
431            published_circle_acks,
432            "
433    circle_id TEXT PRIMARY KEY,
434    ack_ref TEXT NOT NULL CHECK (json_valid(ack_ref)),
435    successor_slot TEXT NOT NULL CHECK (json_valid(successor_slot)),
436    store_cut TEXT NOT NULL CHECK (json_valid(store_cut)),
437    control_coord TEXT NOT NULL CHECK (json_valid(control_coord))
438"
439        );
440        $visit!(
441            activated_circle_acks,
442            "
443    circle_id TEXT NOT NULL,
444    device_id TEXT NOT NULL,
445    ack_ref TEXT NOT NULL CHECK (json_valid(ack_ref)),
446    activating_commit TEXT NOT NULL CHECK (json_valid(activating_commit)),
447    PRIMARY KEY (circle_id, device_id)
448"
449        );
450        $visit!(
451            activated_store_acks,
452            "
453    device_id TEXT PRIMARY KEY,
454    ack_ref TEXT NOT NULL CHECK (json_valid(ack_ref)),
455    activating_commit TEXT NOT NULL CHECK (json_valid(activating_commit))
456"
457        );
458        $visit!(
459            store_device_exclusion_freezes,
460            "
461    proposal_id TEXT PRIMARY KEY CHECK (length(proposal_id) = 64),
462    proposal_ref TEXT NOT NULL CHECK (json_valid(proposal_ref)),
463    target_cut TEXT NOT NULL CHECK (json_valid(target_cut))
464"
465        );
466        $visit!(
467            outbound_store_device_exclusion,
468            "
469    operation_id TEXT PRIMARY KEY CHECK (length(operation_id) = 64),
470    active_key INTEGER UNIQUE CHECK (active_key IS NULL OR active_key = 1),
471    state TEXT NOT NULL CHECK (json_valid(state))
472"
473        );
474        $visit!(
475            store_reclaim_operations,
476            "
477    authorization_hash TEXT PRIMARY KEY CHECK (length(authorization_hash) = 64),
478    state TEXT NOT NULL CHECK (json_valid(state)),
479    stuck_error TEXT CHECK (stuck_error IS NULL OR length(stuck_error) > 0)
480"
481        );
482        $visit!(
483            store_protocol_root_authority,
484            "
485    singleton INTEGER PRIMARY KEY CHECK (singleton = 1),
486    store_root_hash TEXT NOT NULL CHECK (length(store_root_hash) = 64),
487    store_protocol_root_bytes BLOB NOT NULL,
488    store_root_object TEXT NOT NULL CHECK (json_valid(store_root_object))
489"
490        );
491        $visit!(
492            local_store_protocol_root,
493            "
494    singleton INTEGER PRIMARY KEY CHECK (singleton = 1),
495    store_root_hash TEXT NOT NULL CHECK (length(store_root_hash) = 64),
496    store_protocol_root_bytes BLOB NOT NULL,
497    prepared_object TEXT NOT NULL CHECK (json_valid(prepared_object))
498"
499        );
500        $visit!(
501            local_store_device_registration,
502            "
503    singleton INTEGER PRIMARY KEY CHECK (singleton = 1),
504    device_id TEXT NOT NULL UNIQUE,
505    registration_hash TEXT NOT NULL UNIQUE CHECK (length(registration_hash) = 64),
506    registration_bytes BLOB NOT NULL,
507    prepared_object TEXT NOT NULL CHECK (json_valid(prepared_object)),
508    initial_ack_ref TEXT NOT NULL CHECK (json_valid(initial_ack_ref)),
509    initial_ack_bytes BLOB NOT NULL,
510    initial_ack_prepared TEXT NOT NULL CHECK (json_valid(initial_ack_prepared)),
511    state TEXT NOT NULL CHECK (json_valid(state))
512"
513        );
514        $visit!(
515            local_owner_recovery_publication,
516            "
517    singleton INTEGER PRIMARY KEY CHECK (singleton = 1),
518    registration_hash TEXT NOT NULL UNIQUE CHECK (length(registration_hash) = 64),
519    publication TEXT NOT NULL CHECK (json_valid(publication)),
520    FOREIGN KEY (registration_hash)
521        REFERENCES local_store_device_registration(registration_hash) ON DELETE CASCADE
522"
523        );
524        $visit!(
525            local_store_founder_graph,
526            "
527    singleton INTEGER PRIMARY KEY CHECK (singleton = 1),
528    membership_graph TEXT NOT NULL CHECK (json_valid(membership_graph))
529"
530        );
531        $visit!(
532            store_device_registration_activations,
533            "
534    device_id TEXT PRIMARY KEY,
535    registration_hash TEXT NOT NULL CHECK (length(registration_hash) = 64),
536    author_pubkey TEXT NOT NULL,
537    device_signing_pubkey TEXT NOT NULL,
538    registration_bytes BLOB NOT NULL,
539    registration_object TEXT NOT NULL CHECK (json_valid(registration_object)),
540    activation_authority TEXT NOT NULL CHECK (json_valid(activation_authority)),
541    UNIQUE (device_id, registration_hash),
542    UNIQUE (registration_object)
543"
544        );
545        $visit!(
546            store_device_states,
547            "
548    state_hash TEXT PRIMARY KEY CHECK (length(state_hash) = 64),
549    state TEXT NOT NULL CHECK (json_valid(state)),
550    CHECK (json_extract(state, '$.state_hash') IS state_hash)
551"
552        );
553        $visit!(
554            store_device_state_snapshots,
555            "
556    commit_ref TEXT PRIMARY KEY CHECK (json_valid(commit_ref)),
557    state_hash TEXT NOT NULL REFERENCES store_device_states(state_hash)
558"
559        );
560        $visit!(
561            store_author_exclusion_activations,
562            "
563    exclusion_ref TEXT PRIMARY KEY CHECK (json_valid(exclusion_ref)),
564    accepted_cut TEXT NOT NULL CHECK (json_valid(accepted_cut)),
565    activation_commit TEXT NOT NULL CHECK (json_valid(activation_commit)),
566    activation_head TEXT NOT NULL CHECK (json_valid(activation_head))
567"
568        );
569        $visit!(
570            circle_control_activations,
571            "
572    circle_id TEXT NOT NULL,
573    control_coord TEXT NOT NULL CHECK (json_valid(control_coord)),
574    stream_id TEXT NOT NULL,
575    seq INTEGER NOT NULL CHECK (seq > 0),
576    commit_hash TEXT NOT NULL CHECK (length(commit_hash) = 64),
577    control_bytes BLOB NOT NULL,
578    PRIMARY KEY (circle_id, control_coord),
579    UNIQUE (circle_id, stream_id, seq)
580"
581        );
582        $visit!(
583            circle_operations,
584            "
585    operation_id TEXT PRIMARY KEY,
586    circle_id TEXT NOT NULL UNIQUE,
587    prepared BLOB NOT NULL,
588    phase TEXT NOT NULL CHECK (json_valid(phase))
589"
590        );
591        // Which of an operation's upload steps have completed. One row per
592        // completed step, so recording a step appends instead of rewriting the
593        // operation beside it. The rows belong to the operation named in
594        // `prepared`, and the finalization boundary that replaces that
595        // operation clears them with it.
596        $visit!(
597            circle_operation_uploads,
598            "
599    operation_id TEXT NOT NULL,
600    step TEXT NOT NULL,
601    PRIMARY KEY (operation_id, step),
602    FOREIGN KEY (operation_id) REFERENCES circle_operations(operation_id) ON DELETE CASCADE
603"
604        );
605        $visit!(
606            circle_access_cache,
607            "
608    circle_id TEXT NOT NULL,
609    control_coord TEXT NOT NULL CHECK (json_valid(control_coord)),
610    owner_pubkey TEXT NOT NULL,
611    disposition TEXT NOT NULL CHECK (disposition IN ('active', 'inactive')),
612    PRIMARY KEY (circle_id, control_coord, owner_pubkey),
613    FOREIGN KEY (circle_id, control_coord)
614        REFERENCES circle_control_activations(circle_id, control_coord)
615"
616        );
617        $visit!(
618            circle_current_state,
619            "
620    circle_id TEXT PRIMARY KEY,
621    state BLOB NOT NULL
622"
623        );
624    };
625}
626
627macro_rules! coven_routing_tables {
628    ($visit:ident) => {
629        $visit!(
630            _coven_audience,
631            "
632    routing_id TEXT PRIMARY KEY,
633    circle_id TEXT,
634    _updated_at TEXT NOT NULL
635"
636        );
637        $visit!(
638            _coven_row_routes,
639            "
640    routing_id TEXT PRIMARY KEY,
641    table_name TEXT NOT NULL,
642    row_id TEXT NOT NULL,
643    _updated_at TEXT NOT NULL,
644    UNIQUE (table_name, row_id)
645"
646        );
647    };
648}
649
650#[derive(Debug, Clone, PartialEq, Eq, serde::Serialize, serde::Deserialize)]
651#[serde(deny_unknown_fields)]
652pub struct CovenSchemaManifest {
653    objects: Vec<CovenSchemaObject>,
654    tables: Vec<CovenTableShape>,
655}
656
657impl CovenSchemaManifest {
658    pub fn is_empty(&self) -> bool {
659        self.objects.is_empty() && self.tables.is_empty()
660    }
661}
662
663#[derive(Debug, Clone, PartialEq, Eq, serde::Serialize, serde::Deserialize)]
664#[serde(deny_unknown_fields)]
665struct CovenSchemaObject {
666    kind: String,
667    name: String,
668    table_name: String,
669    sql: Option<String>,
670}
671
672#[derive(Debug, Clone, PartialEq, Eq, serde::Serialize, serde::Deserialize)]
673#[serde(deny_unknown_fields)]
674struct CovenTableShape {
675    name: String,
676    columns: i64,
677    without_rowid: bool,
678    strict: bool,
679}
680
681fn normalize_schema_sql(sql: &str) -> String {
682    let bytes = sql.as_bytes();
683    let mut normalized = String::with_capacity(sql.len());
684    let mut index = 0;
685    let mut pending_separator = false;
686    let mut quote = None;
687
688    while index < bytes.len() {
689        let byte = bytes[index];
690        if let Some(delimiter) = quote {
691            normalized.push(char::from(byte));
692            if byte == delimiter {
693                if bytes.get(index + 1) == Some(&delimiter) {
694                    normalized.push(char::from(delimiter));
695                    index += 1;
696                } else {
697                    quote = None;
698                }
699            }
700            index += 1;
701            continue;
702        }
703
704        if byte == b'-' && bytes.get(index + 1) == Some(&b'-') {
705            index += 2;
706            while index < bytes.len() && !matches!(bytes[index], b'\n' | b'\r') {
707                index += 1;
708            }
709            pending_separator = true;
710            continue;
711        }
712        if byte.is_ascii_whitespace() {
713            pending_separator = true;
714            index += 1;
715            continue;
716        }
717        if matches!(byte, b'\'' | b'"' | b'`') {
718            if pending_separator
719                && normalized
720                    .as_bytes()
721                    .last()
722                    .is_some_and(u8::is_ascii_alphanumeric)
723            {
724                normalized.push(' ');
725            }
726            pending_separator = false;
727            quote = Some(byte);
728            normalized.push(char::from(byte));
729            index += 1;
730            continue;
731        }
732        if pending_separator
733            && byte.is_ascii_alphanumeric()
734            && normalized
735                .as_bytes()
736                .last()
737                .is_some_and(u8::is_ascii_alphanumeric)
738        {
739            normalized.push(' ');
740        }
741        pending_separator = false;
742        normalized.push(char::from(byte.to_ascii_lowercase()));
743        index += 1;
744    }
745
746    normalized
747}
748
749fn all_coven_table_names() -> std::collections::BTreeSet<&'static str> {
750    let mut names = std::collections::BTreeSet::new();
751    macro_rules! collect_name {
752        ($name:ident, $columns:expr) => {
753            names.insert(stringify!($name));
754        };
755    }
756
757    coven_tables!(collect_name);
758    coven_routing_tables!(collect_name);
759    names
760}
761
762#[cfg(test)]
763pub(crate) fn all_table_names() -> std::collections::BTreeSet<&'static str> {
764    all_coven_table_names()
765}
766
767#[cfg(any(test, feature = "test-utils"))]
768#[derive(Clone, Copy)]
769pub struct DatabaseTestTable(pub &'static str);
770
771#[cfg(any(test, feature = "test-utils"))]
772impl DatabaseTestTable {
773    pub fn named(name: &'static str) -> Self {
774        assert!(
775            all_coven_table_names().contains(name),
776            "{name:?} is not a Coven-owned table"
777        );
778        Self(name)
779    }
780}
781
782pub(crate) fn live_coven_schema_manifest(
783    conn: &rusqlite::Connection,
784) -> rusqlite::Result<CovenSchemaManifest> {
785    let names = all_coven_table_names();
786    let mut objects = conn
787        .prepare(
788            "SELECT type, name, tbl_name, sql
789             FROM main.sqlite_schema
790             WHERE type IN ('table', 'index', 'trigger')
791             ORDER BY type, name",
792        )?
793        .query_map([], |row| {
794            Ok(CovenSchemaObject {
795                kind: row.get(0)?,
796                name: row.get(1)?,
797                table_name: row.get(2)?,
798                sql: row
799                    .get::<_, Option<String>>(3)?
800                    .map(|sql| normalize_schema_sql(&sql)),
801            })
802        })?
803        .collect::<rusqlite::Result<Vec<_>>>()?;
804    objects.retain(|object| names.contains(object.table_name.as_str()));
805
806    let mut tables = conn
807        .prepare("PRAGMA main.table_list")?
808        .query_map([], |row| {
809            Ok(CovenTableShape {
810                name: row.get(1)?,
811                columns: row.get(3)?,
812                without_rowid: row.get::<_, i64>(4)? != 0,
813                strict: row.get::<_, i64>(5)? != 0,
814            })
815        })?
816        .collect::<rusqlite::Result<Vec<_>>>()?;
817    tables.retain(|table| names.contains(table.name.as_str()));
818    tables.sort_by(|left, right| left.name.cmp(&right.name));
819
820    Ok(CovenSchemaManifest { objects, tables })
821}
822
823fn build_expected_coven_schema_manifest(
824    include_routing: bool,
825) -> rusqlite::Result<CovenSchemaManifest> {
826    let conn = rusqlite::Connection::open_in_memory()?;
827    apply_coven_schema(&conn)?;
828    if include_routing {
829        apply_coven_routing_schema(&conn)?;
830    }
831    live_coven_schema_manifest(&conn)
832}
833
834fn recreate_table(conn: &rusqlite::Connection, table: &str, columns: &str) -> rusqlite::Result<()> {
835    conn.execute_batch(&format!(
836        "DROP TABLE {table}; CREATE TABLE {table} ({columns}) STRICT;"
837    ))
838}
839
840fn build_expected_coven_schema_v0_manifest(
841    include_routing: bool,
842) -> rusqlite::Result<CovenSchemaManifest> {
843    let conn = rusqlite::Connection::open_in_memory()?;
844    apply_coven_schema(&conn)?;
845    if include_routing {
846        apply_coven_routing_schema(&conn)?;
847    }
848    recreate_table(&conn, "cloud_outbox", CLOUD_OUTBOX_V0_COLUMNS)?;
849    recreate_table(
850        &conn,
851        "blob_make_remote_intents",
852        BLOB_MAKE_REMOTE_INTENTS_V0_COLUMNS,
853    )?;
854    live_coven_schema_manifest(&conn)
855}
856
857static EXPECTED_COVEN_SCHEMA: std::sync::LazyLock<Result<CovenSchemaManifest, rusqlite::Error>> =
858    std::sync::LazyLock::new(|| build_expected_coven_schema_manifest(false));
859static EXPECTED_ROUTED_COVEN_SCHEMA: std::sync::LazyLock<
860    Result<CovenSchemaManifest, rusqlite::Error>,
861> = std::sync::LazyLock::new(|| build_expected_coven_schema_manifest(true));
862static EXPECTED_COVEN_SCHEMA_V0: std::sync::LazyLock<Result<CovenSchemaManifest, rusqlite::Error>> =
863    std::sync::LazyLock::new(|| build_expected_coven_schema_v0_manifest(false));
864static EXPECTED_ROUTED_COVEN_SCHEMA_V0: std::sync::LazyLock<
865    Result<CovenSchemaManifest, rusqlite::Error>,
866> = std::sync::LazyLock::new(|| build_expected_coven_schema_v0_manifest(true));
867
868pub fn expected_coven_schema_manifest(
869    include_routing: bool,
870) -> Result<&'static CovenSchemaManifest, DbError> {
871    let expected = if include_routing {
872        &*EXPECTED_ROUTED_COVEN_SCHEMA
873    } else {
874        &*EXPECTED_COVEN_SCHEMA
875    };
876    expected.as_ref().map_err(DbError::ExpectedSchema)
877}
878
879pub(crate) fn expected_coven_schema_v0_manifest(
880    include_routing: bool,
881) -> Result<&'static CovenSchemaManifest, DbError> {
882    let expected = if include_routing {
883        &*EXPECTED_ROUTED_COVEN_SCHEMA_V0
884    } else {
885        &*EXPECTED_COVEN_SCHEMA_V0
886    };
887    expected.as_ref().map_err(DbError::ExpectedSchema)
888}
889
890pub(crate) fn recreate_current_transition_tables(
891    conn: &rusqlite::Connection,
892) -> rusqlite::Result<()> {
893    recreate_table(conn, "cloud_outbox", CLOUD_OUTBOX_COLUMNS)?;
894    recreate_table(
895        conn,
896        "blob_make_remote_intents",
897        BLOB_MAKE_REMOTE_INTENTS_COLUMNS,
898    )
899}
900
901#[cfg(any(test, feature = "test-utils"))]
902pub(crate) fn downgrade_coven_schema_to_v0_for_test(
903    conn: &rusqlite::Connection,
904    include_routing: bool,
905) -> Result<(), DbError> {
906    let tx = conn.unchecked_transaction().map_err(DbError::from)?;
907    recreate_table(&tx, "cloud_outbox", CLOUD_OUTBOX_V0_COLUMNS).map_err(DbError::from)?;
908    recreate_table(
909        &tx,
910        "blob_make_remote_intents",
911        BLOB_MAKE_REMOTE_INTENTS_V0_COLUMNS,
912    )
913    .map_err(DbError::from)?;
914    let manifest = serde_json::to_string(expected_coven_schema_v0_manifest(include_routing)?)
915        .map_err(DbError::from)?;
916    tx.execute(
917        "UPDATE protocol_state SET value = ?2 WHERE key = ?1",
918        (crate::COVEN_SCHEMA_MANIFEST_STATE_KEY, manifest),
919    )
920    .map_err(DbError::from)?;
921    tx.execute(
922        "DELETE FROM protocol_state WHERE key = ?1",
923        [crate::COVEN_SCHEMA_VERSION_STATE_KEY],
924    )
925    .map_err(DbError::from)?;
926    tx.commit().map_err(DbError::from)
927}
928
929/// Creates Coven's bookkeeping tables after the fresh host schema has passed
930/// sync-routing validation, inside the same open transaction. Idempotent (`IF
931/// NOT EXISTS`). STRICT: every column here is already TEXT/INTEGER/BLOB, so
932/// STRICT only forecloses a future column drifting off its declared affinity.
933pub(crate) fn apply_coven_schema(conn: &rusqlite::Connection) -> rusqlite::Result<()> {
934    macro_rules! apply_table {
935        ($name:ident, $columns:expr) => {
936            conn.execute_batch(&format!(
937                "CREATE TABLE IF NOT EXISTS {} ({}) STRICT;",
938                stringify!($name),
939                $columns,
940            ))?;
941        };
942    }
943
944    coven_tables!(apply_table);
945    conn.execute_batch(
946        "CREATE INDEX IF NOT EXISTS store_device_state_snapshots_by_state
947         ON store_device_state_snapshots(state_hash);",
948    )?;
949    conn.execute_batch(OBJECT_OWNERSHIP_TRIGGERS)?;
950    Ok(())
951}
952
953/// Create the audience mirror and private route map. A snapshot already carries
954/// these schemas, so creation is idempotent; the caller validates their exact
955/// shape before committing initialization.
956pub(crate) fn apply_coven_routing_schema(conn: &rusqlite::Connection) -> rusqlite::Result<()> {
957    macro_rules! apply_table {
958        ($name:ident, $columns:expr) => {
959            conn.execute_batch(&format!(
960                "CREATE TABLE IF NOT EXISTS {} ({}) STRICT, WITHOUT ROWID;",
961                stringify!($name),
962                $columns,
963            ))?;
964        };
965    }
966
967    coven_routing_tables!(apply_table);
968    Ok(())
969}
970
971/// Whether `name` is a table coven owns for sync bookkeeping. Hosts may not
972/// declare these as synced tables.
973pub fn is_reserved_table_name(name: &str) -> bool {
974    macro_rules! matches_table {
975        ($table:ident, $columns:expr) => {
976            if name == stringify!($table) {
977                return true;
978            }
979        };
980    }
981
982    coven_tables!(matches_table);
983    coven_routing_tables!(matches_table);
984    false
985}
986
987/// Every application and Coven table in the main schema, excluding SQLite's
988/// internal tables. Snapshot and retained-replay projections share this one
989/// enumeration so a new table cannot be omitted by one image path.
990pub(crate) fn user_table_names(conn: &rusqlite::Connection) -> rusqlite::Result<Vec<String>> {
991    let tables = query_mapped_rows(
992        conn,
993        "SELECT name FROM main.sqlite_schema
994         WHERE type = 'table' AND name NOT LIKE 'sqlite_%'
995         ORDER BY name",
996        [],
997        |row| row.get::<_, String>(0),
998    )?;
999    Ok(tables)
1000}
1001
1002/// The name of every table [`apply_coven_schema`] creates, for a test to assert a
1003/// schema property (STRICT) holds across all of them without re-listing the set
1004/// by hand.
1005#[cfg(test)]
1006pub(crate) fn table_names() -> Vec<&'static str> {
1007    let mut names = Vec::new();
1008    macro_rules! collect_name {
1009        ($name:ident, $columns:expr) => {
1010            names.push(stringify!($name));
1011        };
1012    }
1013
1014    coven_tables!(collect_name);
1015    names
1016}
1017
1018#[cfg(test)]
1019#[path = "coven_schema_tests.rs"]
1020mod tests;
coven_database/coven_schema.rs

coven_database/
coven_schema.rs
