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

coven_database/
coven_schema.rs
