The best way to control Claude Code, Codex and more from any computer or phone
Mirrored from https://github.com/Camo67/t3code.git
Synced Nov 21, 04:45 UTC
| 1 | -- Primary D1 owns policy and accounting. Back up policies separately from cache data. |
| 2 | CREATE TABLE deployment ( |
| 3 | id INTEGER PRIMARY KEY CHECK (id = 1), |
| 4 | enabled INTEGER NOT NULL DEFAULT 1 CHECK (enabled IN (0, 1)), |
| 5 | writes_enabled INTEGER NOT NULL DEFAULT 1 CHECK (writes_enabled IN (0, 1)), |
| 6 | byte_limit INTEGER NOT NULL DEFAULT 8000000000 CHECK (byte_limit > 0), |
| 7 | entry_limit INTEGER NOT NULL DEFAULT 20000 CHECK (entry_limit > 0), |
| 8 | association_limit INTEGER NOT NULL DEFAULT 20000 CHECK (association_limit > 0), |
| 9 | retention_high_water_seconds INTEGER NOT NULL DEFAULT 604800, |
| 10 | charged_bytes INTEGER NOT NULL DEFAULT 0 CHECK (charged_bytes >= 0), |
| 11 | entry_count INTEGER NOT NULL DEFAULT 0 CHECK (entry_count >= 0), |
| 12 | association_count INTEGER NOT NULL DEFAULT 0 CHECK (association_count >= 0) |
| 13 | ); |
| 14 | INSERT INTO deployment (id) VALUES (1); |
| 15 | CREATE TABLE scopes ( |
| 16 | scope_id TEXT PRIMARY KEY, |
| 17 | endpoint TEXT NOT NULL UNIQUE, |
| 18 | repository TEXT NOT NULL, |
| 19 | repository_id TEXT NOT NULL, |
| 20 | repository_owner_id TEXT NOT NULL, |
| 21 | branch TEXT NOT NULL, |
| 22 | enabled INTEGER NOT NULL DEFAULT 1 CHECK (enabled IN (0, 1)), |
| 23 | writes_enabled INTEGER NOT NULL DEFAULT 1 CHECK (writes_enabled IN (0, 1)), |
| 24 | policy_version INTEGER NOT NULL DEFAULT 1, |
| 25 | retention_seconds INTEGER NOT NULL DEFAULT 604800 CHECK (retention_seconds > 0), |
| 26 | byte_limit INTEGER NOT NULL DEFAULT 8000000000 CHECK (byte_limit > 0), |
| 27 | entry_limit INTEGER NOT NULL DEFAULT 20000 CHECK (entry_limit > 0), |
| 28 | association_limit INTEGER NOT NULL DEFAULT 20000 CHECK (association_limit > 0), |
| 29 | charged_bytes INTEGER NOT NULL DEFAULT 0 CHECK (charged_bytes >= 0), |
| 30 | entry_count INTEGER NOT NULL DEFAULT 0 CHECK (entry_count >= 0), |
| 31 | association_count INTEGER NOT NULL DEFAULT 0 CHECK (association_count >= 0) |
| 32 | ); |
| 33 | CREATE TABLE generations ( |
| 34 | generation_id TEXT PRIMARY KEY, |
| 35 | scope_id TEXT NOT NULL REFERENCES scopes(scope_id), |
| 36 | state TEXT NOT NULL CHECK (state IN ('uploading', 'ready', 'retired', 'deleting')), |
| 37 | policy_version INTEGER NOT NULL, |
| 38 | token_exp INTEGER NOT NULL, |
| 39 | lease_until INTEGER NOT NULL, |
| 40 | gc_after INTEGER NOT NULL, |
| 41 | gc_claim TEXT, |
| 42 | key BLOB, |
| 43 | secondary_key BLOB, |
| 44 | value_object TEXT NOT NULL UNIQUE, |
| 45 | blob_object TEXT NOT NULL UNIQUE, |
| 46 | blob_id TEXT, |
| 47 | upload_id TEXT, |
| 48 | value_size INTEGER NOT NULL DEFAULT 0 CHECK (value_size >= 0), |
| 49 | blob_size INTEGER NOT NULL DEFAULT 0 CHECK (blob_size >= 0), |
| 50 | charged_bytes INTEGER NOT NULL CHECK (charged_bytes >= 0), |
| 51 | expires_at INTEGER, |
| 52 | retired_at INTEGER, |
| 53 | UNIQUE (scope_id, generation_id) |
| 54 | ); |
| 55 | CREATE UNIQUE INDEX generations_blob ON generations(scope_id, blob_id) WHERE blob_id IS NOT NULL; |
| 56 | CREATE INDEX generations_gc ON generations(gc_after); |
| 57 | CREATE TABLE entries ( |
| 58 | scope_id TEXT NOT NULL REFERENCES scopes(scope_id), |
| 59 | key BLOB NOT NULL CHECK (typeof(key) = 'blob'), |
| 60 | generation_id TEXT NOT NULL, |
| 61 | PRIMARY KEY (scope_id, key), |
| 62 | FOREIGN KEY (scope_id, generation_id) REFERENCES generations(scope_id, generation_id) ON DELETE CASCADE |
| 63 | ) WITHOUT ROWID; |
| 64 | CREATE INDEX entries_generation ON entries(generation_id); |
| 65 | CREATE TABLE associations ( |
| 66 | scope_id TEXT NOT NULL REFERENCES scopes(scope_id), |
| 67 | secondary_key BLOB NOT NULL CHECK (typeof(secondary_key) = 'blob'), |
| 68 | target_key BLOB NOT NULL CHECK (typeof(target_key) = 'blob'), |
| 69 | PRIMARY KEY (scope_id, secondary_key) |
| 70 | ) WITHOUT ROWID; |
| 71 | CREATE INDEX associations_target ON associations(scope_id, target_key); |
| 72 | CREATE TABLE maintenance (id INTEGER PRIMARY KEY CHECK (id = 1), scope_id TEXT NOT NULL, secondary_key BLOB NOT NULL); |
| 73 | INSERT INTO maintenance VALUES (1, '', X''); |
| 74 | |
| 75 | -- Parenthesize CASE expressions so the D1 REST parser preserves each trigger body. |
| 76 | -- Lifecycle rules must never shorten the life of an already published generation. |
| 77 | CREATE TRIGGER initial_retention AFTER INSERT ON scopes BEGIN |
| 78 | UPDATE deployment SET retention_high_water_seconds = max(retention_high_water_seconds, NEW.retention_seconds); |
| 79 | END; |
| 80 | CREATE TRIGGER increased_retention AFTER UPDATE OF retention_seconds ON scopes BEGIN |
| 81 | UPDATE deployment SET retention_high_water_seconds = max(retention_high_water_seconds, NEW.retention_seconds); |
| 82 | END; |
| 83 | CREATE TRIGGER scope_limit BEFORE INSERT ON scopes |
| 84 | WHEN NOT EXISTS (SELECT 1 FROM scopes WHERE scope_id = NEW.scope_id) |
| 85 | BEGIN |
| 86 | SELECT (CASE WHEN (SELECT count(*) FROM scopes) >= 100 THEN RAISE(ABORT, 'cache_scope_limit') END); |
| 87 | END; |
| 88 | |
| 89 | -- Direct policy edits must invalidate uploads that captured the previous policy. |
| 90 | CREATE TRIGGER changed_policy AFTER UPDATE OF endpoint, repository_id, repository_owner_id, branch, |
| 91 | enabled, writes_enabled, retention_seconds, byte_limit, entry_limit, association_limit ON scopes |
| 92 | WHEN NEW.policy_version = OLD.policy_version |
| 93 | BEGIN |
| 94 | UPDATE scopes SET policy_version = OLD.policy_version + 1 WHERE scope_id = NEW.scope_id; |
| 95 | END; |
| 96 | |
| 97 | CREATE TRIGGER reserve_capacity BEFORE INSERT ON generations BEGIN |
| 98 | SELECT (CASE WHEN NOT EXISTS ( |
| 99 | SELECT 1 FROM scopes s, deployment d WHERE s.scope_id = NEW.scope_id |
| 100 | AND s.enabled = 1 AND s.writes_enabled = 1 AND d.enabled = 1 AND d.writes_enabled = 1 |
| 101 | AND s.policy_version = NEW.policy_version AND NEW.token_exp > unixepoch() |
| 102 | AND s.charged_bytes + NEW.charged_bytes <= s.byte_limit |
| 103 | AND d.charged_bytes + NEW.charged_bytes <= d.byte_limit |
| 104 | ) THEN RAISE(ABORT, 'cache_admission_denied') END); |
| 105 | END; |
| 106 | CREATE TRIGGER charge_generation AFTER INSERT ON generations BEGIN |
| 107 | UPDATE scopes SET charged_bytes = charged_bytes + NEW.charged_bytes WHERE scope_id = NEW.scope_id; |
| 108 | UPDATE deployment SET charged_bytes = charged_bytes + NEW.charged_bytes; |
| 109 | END; |
| 110 | CREATE TRIGGER adjust_generation AFTER UPDATE OF charged_bytes ON generations BEGIN |
| 111 | UPDATE scopes SET charged_bytes = charged_bytes + NEW.charged_bytes - OLD.charged_bytes WHERE scope_id = NEW.scope_id; |
| 112 | UPDATE deployment SET charged_bytes = charged_bytes + NEW.charged_bytes - OLD.charged_bytes; |
| 113 | END; |
| 114 | CREATE TRIGGER release_generation AFTER DELETE ON generations BEGIN |
| 115 | UPDATE scopes SET charged_bytes = charged_bytes - OLD.charged_bytes WHERE scope_id = OLD.scope_id; |
| 116 | UPDATE deployment SET charged_bytes = charged_bytes - OLD.charged_bytes; |
| 117 | END; |
| 118 | |
| 119 | CREATE TRIGGER count_entry BEFORE INSERT ON entries |
| 120 | WHEN NOT EXISTS (SELECT 1 FROM entries WHERE scope_id = NEW.scope_id AND key = NEW.key) |
| 121 | BEGIN |
| 122 | SELECT (CASE WHEN EXISTS ( |
| 123 | SELECT 1 FROM scopes s, deployment d WHERE s.scope_id = NEW.scope_id |
| 124 | AND (s.entry_count >= s.entry_limit OR d.entry_count >= d.entry_limit) |
| 125 | ) THEN RAISE(ABORT, 'cache_entry_limit') END); |
| 126 | UPDATE scopes SET entry_count = entry_count + 1 WHERE scope_id = NEW.scope_id; |
| 127 | UPDATE deployment SET entry_count = entry_count + 1; |
| 128 | END; |
| 129 | CREATE TRIGGER uncount_entry AFTER DELETE ON entries BEGIN |
| 130 | UPDATE scopes SET entry_count = entry_count - 1 WHERE scope_id = OLD.scope_id; |
| 131 | UPDATE deployment SET entry_count = entry_count - 1; |
| 132 | END; |
| 133 | CREATE TRIGGER count_association BEFORE INSERT ON associations |
| 134 | WHEN NOT EXISTS (SELECT 1 FROM associations WHERE scope_id = NEW.scope_id AND secondary_key = NEW.secondary_key) |
| 135 | BEGIN |
| 136 | SELECT (CASE WHEN EXISTS ( |
| 137 | SELECT 1 FROM scopes s, deployment d WHERE s.scope_id = NEW.scope_id |
| 138 | AND (s.association_count >= s.association_limit OR d.association_count >= d.association_limit) |
| 139 | ) THEN RAISE(ABORT, 'cache_association_limit') END); |
| 140 | UPDATE scopes SET association_count = association_count + 1 WHERE scope_id = NEW.scope_id; |
| 141 | UPDATE deployment SET association_count = association_count + 1; |
| 142 | END; |
| 143 | CREATE TRIGGER uncount_association AFTER DELETE ON associations BEGIN |
| 144 | UPDATE scopes SET association_count = association_count - 1 WHERE scope_id = OLD.scope_id; |
| 145 | UPDATE deployment SET association_count = association_count - 1; |
| 146 | END; |
| 147 | |
| 148 | -- All publication mutations inherit the guarded state change's transaction. |
| 149 | -- A zero-row guard fires no trigger. A quota failure rolls every mutation back. |
| 150 | CREATE TRIGGER publish_generation AFTER UPDATE OF state ON generations |
| 151 | WHEN OLD.state = 'uploading' AND NEW.state = 'ready' |
| 152 | BEGIN |
| 153 | UPDATE generations SET state = 'retired', retired_at = unixepoch(), |
| 154 | gc_after = (CASE WHEN expires_at > unixepoch() THEN unixepoch() + 600 ELSE unixepoch() END) |
| 155 | WHERE generation_id = (SELECT generation_id FROM entries WHERE scope_id = NEW.scope_id AND key = NEW.key) AND state = 'ready'; |
| 156 | INSERT INTO entries (scope_id, key, generation_id) VALUES (NEW.scope_id, NEW.key, NEW.generation_id) |
| 157 | ON CONFLICT (scope_id, key) DO UPDATE SET generation_id = excluded.generation_id; |
| 158 | INSERT INTO associations (scope_id, secondary_key, target_key) VALUES (NEW.scope_id, NEW.secondary_key, NEW.key) |
| 159 | ON CONFLICT (scope_id, secondary_key) DO UPDATE SET target_key = excluded.target_key; |
| 160 | END; |