desertthunder.dev
learn and share notes on atproto (wip) 馃
malfestio.stormlightlabs.org/
readability
solid
axum
atproto
srs
1-- Sync tracking infrastructure for bi-directional PDS synchronization
2-- Adds version tracking and sync status to core tables
3
4-- Sync status enum (idempotent creation)
5DO $$ BEGIN
6 CREATE TYPE sync_status AS ENUM (
7 'local_only', -- Never synced to PDS
8 'synced', -- In sync with PDS
9 'pending_push', -- Local changes need to be pushed
10 'conflict' -- Local and remote both changed
11 );
12EXCEPTION
13 WHEN duplicate_object THEN null;
14END $$;
15
16ALTER TABLE decks
17 ADD COLUMN IF NOT EXISTS version INTEGER NOT NULL DEFAULT 1,
18 ADD COLUMN IF NOT EXISTS pds_cid TEXT,
19 ADD COLUMN IF NOT EXISTS pds_uri TEXT,
20 ADD COLUMN IF NOT EXISTS sync_status sync_status NOT NULL DEFAULT 'local_only',
21 ADD COLUMN IF NOT EXISTS last_synced_at TIMESTAMPTZ;
22
23ALTER TABLE cards
24 ADD COLUMN IF NOT EXISTS version INTEGER NOT NULL DEFAULT 1,
25 ADD COLUMN IF NOT EXISTS pds_cid TEXT,
26 ADD COLUMN IF NOT EXISTS pds_uri TEXT,
27 ADD COLUMN IF NOT EXISTS sync_status sync_status NOT NULL DEFAULT 'local_only',
28 ADD COLUMN IF NOT EXISTS last_synced_at TIMESTAMPTZ;
29
30ALTER TABLE notes
31 ADD COLUMN IF NOT EXISTS version INTEGER NOT NULL DEFAULT 1,
32 ADD COLUMN IF NOT EXISTS pds_cid TEXT,
33 ADD COLUMN IF NOT EXISTS pds_uri TEXT,
34 ADD COLUMN IF NOT EXISTS sync_status sync_status NOT NULL DEFAULT 'local_only',
35 ADD COLUMN IF NOT EXISTS last_synced_at TIMESTAMPTZ;
36
37CREATE TABLE IF NOT EXISTS sync_log (
38 id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
39 owner_did TEXT NOT NULL,
40 entity_type TEXT NOT NULL, -- 'deck', 'card', 'note'
41 entity_id UUID NOT NULL,
42 operation TEXT NOT NULL, -- 'push', 'pull', 'conflict_resolve'
43 status TEXT NOT NULL, -- 'pending', 'success', 'failed'
44 pds_cid TEXT,
45 error_message TEXT,
46 created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
47 completed_at TIMESTAMPTZ
48);
49
50CREATE INDEX IF NOT EXISTS idx_sync_log_owner_did ON sync_log(owner_did);
51CREATE INDEX IF NOT EXISTS idx_sync_log_entity ON sync_log(entity_type, entity_id);
52CREATE INDEX IF NOT EXISTS idx_sync_log_status ON sync_log(status);
53CREATE INDEX IF NOT EXISTS idx_sync_log_created_at ON sync_log(created_at DESC);
54
55CREATE INDEX IF NOT EXISTS idx_decks_sync_status ON decks(sync_status) WHERE sync_status != 'synced';
56CREATE INDEX IF NOT EXISTS idx_cards_sync_status ON cards(sync_status) WHERE sync_status != 'synced';
57CREATE INDEX IF NOT EXISTS idx_notes_sync_status ON notes(sync_status) WHERE sync_status != 'synced';
58
59CREATE OR REPLACE FUNCTION increment_version_on_update()
60RETURNS TRIGGER AS $$
61BEGIN
62 -- Only increment if content changed (not just sync metadata)
63 IF (TG_TABLE_NAME = 'decks' AND (NEW.title != OLD.title OR NEW.description != OLD.description OR NEW.tags != OLD.tags)) OR
64 (TG_TABLE_NAME = 'cards' AND (NEW.front != OLD.front OR NEW.back != OLD.back OR NEW.media_url IS DISTINCT FROM OLD.media_url)) OR
65 (TG_TABLE_NAME = 'notes' AND (NEW.title != OLD.title OR NEW.body != OLD.body OR NEW.tags != OLD.tags)) THEN
66 NEW.version = OLD.version + 1;
67 -- Mark as pending push if it was synced
68 IF OLD.sync_status = 'synced' THEN
69 NEW.sync_status = 'pending_push';
70 END IF;
71 END IF;
72 RETURN NEW;
73END;
74$$ LANGUAGE plpgsql;
75
76CREATE TRIGGER increment_decks_version BEFORE UPDATE ON decks
77 FOR EACH ROW EXECUTE FUNCTION increment_version_on_update();
78
79CREATE TRIGGER increment_cards_version BEFORE UPDATE ON cards
80 FOR EACH ROW EXECUTE FUNCTION increment_version_on_update();
81
82CREATE TRIGGER increment_notes_version BEFORE UPDATE ON notes
83 FOR EACH ROW EXECUTE FUNCTION increment_version_on_update();