File size: 4,654 Bytes
88c4c60 | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 | // Latest schema version — bumped when a migration is added in ./migrations/
export const SCHEMA_VERSION = 1;
export const PRAGMA_SQL = `
PRAGMA journal_mode = WAL;
PRAGMA synchronous = NORMAL;
PRAGMA temp_store = MEMORY;
PRAGMA mmap_size = 30000000;
PRAGMA cache_size = -64000;
PRAGMA foreign_keys = ON;
PRAGMA busy_timeout = 5000;
`;
// Declarative current schema. Used by syncSchemaFromTables() to
// auto-add missing tables/columns/indexes after versioned migrations.
// For destructive changes (drop/rename/type-change), write a migration file.
export const TABLES = {
_meta: {
columns: {
key: "TEXT PRIMARY KEY",
value: "TEXT NOT NULL",
},
},
settings: {
columns: {
id: "INTEGER PRIMARY KEY CHECK (id = 1)",
data: "TEXT NOT NULL",
},
},
providerConnections: {
columns: {
id: "TEXT PRIMARY KEY",
provider: "TEXT NOT NULL",
authType: "TEXT NOT NULL",
name: "TEXT",
email: "TEXT",
priority: "INTEGER",
isActive: "INTEGER DEFAULT 1",
data: "TEXT NOT NULL",
createdAt: "TEXT NOT NULL",
updatedAt: "TEXT NOT NULL",
},
indexes: [
"CREATE INDEX IF NOT EXISTS idx_pc_provider ON providerConnections(provider)",
"CREATE INDEX IF NOT EXISTS idx_pc_provider_active ON providerConnections(provider, isActive)",
"CREATE INDEX IF NOT EXISTS idx_pc_priority ON providerConnections(provider, priority)",
],
},
providerNodes: {
columns: {
id: "TEXT PRIMARY KEY",
type: "TEXT",
name: "TEXT",
data: "TEXT NOT NULL",
createdAt: "TEXT NOT NULL",
updatedAt: "TEXT NOT NULL",
},
indexes: ["CREATE INDEX IF NOT EXISTS idx_pn_type ON providerNodes(type)"],
},
proxyPools: {
columns: {
id: "TEXT PRIMARY KEY",
isActive: "INTEGER DEFAULT 1",
testStatus: "TEXT",
data: "TEXT NOT NULL",
createdAt: "TEXT NOT NULL",
updatedAt: "TEXT NOT NULL",
},
indexes: [
"CREATE INDEX IF NOT EXISTS idx_pp_active ON proxyPools(isActive)",
"CREATE INDEX IF NOT EXISTS idx_pp_status ON proxyPools(testStatus)",
],
},
apiKeys: {
columns: {
id: "TEXT PRIMARY KEY",
key: "TEXT UNIQUE NOT NULL",
name: "TEXT",
machineId: "TEXT",
isActive: "INTEGER DEFAULT 1",
createdAt: "TEXT NOT NULL",
},
indexes: ["CREATE INDEX IF NOT EXISTS idx_ak_key ON apiKeys(key)"],
},
combos: {
columns: {
id: "TEXT PRIMARY KEY",
name: "TEXT UNIQUE NOT NULL",
kind: "TEXT",
models: "TEXT NOT NULL",
createdAt: "TEXT NOT NULL",
updatedAt: "TEXT NOT NULL",
},
indexes: ["CREATE INDEX IF NOT EXISTS idx_combo_name ON combos(name)"],
},
kv: {
columns: {
scope: "TEXT NOT NULL",
key: "TEXT NOT NULL",
value: "TEXT NOT NULL",
},
primaryKey: "PRIMARY KEY (scope, key)",
indexes: ["CREATE INDEX IF NOT EXISTS idx_kv_scope ON kv(scope)"],
},
usageHistory: {
columns: {
id: "INTEGER PRIMARY KEY AUTOINCREMENT",
timestamp: "TEXT NOT NULL",
provider: "TEXT",
model: "TEXT",
connectionId: "TEXT",
apiKey: "TEXT",
endpoint: "TEXT",
promptTokens: "INTEGER DEFAULT 0",
completionTokens: "INTEGER DEFAULT 0",
cost: "REAL DEFAULT 0",
status: "TEXT",
tokens: "TEXT",
meta: "TEXT",
},
indexes: [
"CREATE INDEX IF NOT EXISTS idx_uh_ts ON usageHistory(timestamp DESC)",
"CREATE INDEX IF NOT EXISTS idx_uh_provider ON usageHistory(provider)",
"CREATE INDEX IF NOT EXISTS idx_uh_model ON usageHistory(model)",
"CREATE INDEX IF NOT EXISTS idx_uh_conn ON usageHistory(connectionId)",
],
},
usageDaily: {
columns: {
dateKey: "TEXT PRIMARY KEY",
data: "TEXT NOT NULL",
},
},
requestDetails: {
columns: {
id: "TEXT PRIMARY KEY",
timestamp: "TEXT NOT NULL",
provider: "TEXT",
model: "TEXT",
connectionId: "TEXT",
status: "TEXT",
data: "TEXT NOT NULL",
},
indexes: [
"CREATE INDEX IF NOT EXISTS idx_rd_ts ON requestDetails(timestamp DESC)",
"CREATE INDEX IF NOT EXISTS idx_rd_provider ON requestDetails(provider)",
"CREATE INDEX IF NOT EXISTS idx_rd_model ON requestDetails(model)",
"CREATE INDEX IF NOT EXISTS idx_rd_conn ON requestDetails(connectionId)",
],
},
};
export function buildCreateTableSql(name, def) {
const cols = Object.entries(def.columns).map(([k, v]) => `${k} ${v}`);
if (def.primaryKey) cols.push(def.primaryKey);
return `CREATE TABLE IF NOT EXISTS ${name} (${cols.join(", ")})`;
}
|