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
|
/**
* @yt-music/storage-sqlite — append-only persistence for jobs + events.
*
* EFFECT at the edge: reads/writes SQLite via `bun:sqlite`. The pure
* `reconcile()` (in @yt-music/job-store) folds the persisted events back into
* a JobState on load — durability: status is DERIVED, never trusted from disk.
*
* Schema: two tables — `jobs` (one row per job) + `job_events` (append-only
* log, one row per event). Events are stored as JSON; the reducer re-derives
* status on read.
*/
import { Database } from "bun:sqlite";
import type { AudioFormat, Job, JobEvent, JobId, JobMode, Settings } from "@yt-music/contract";
import { reconcile } from "@yt-music/job-store";
export interface Storage {
createJob(url: string, mode: JobMode, format: AudioFormat): JobId;
appendEvent(jobId: JobId, event: JobEvent): void;
getJob(jobId: JobId): Job | null;
listJobs(): Job[];
/** Persisted settings, or the default passed to `createStorage` if none saved. */
getSettings(): Settings;
/** Persist (overwrite) the settings. */
saveSettings(settings: Settings): void;
close(): void;
}
export function createStorage(dbPath: string, defaultSettings: Settings): Storage {
const db = new Database(dbPath);
db.exec("PRAGMA journal_mode = WAL;");
db.exec(`
CREATE TABLE IF NOT EXISTS jobs (
id TEXT PRIMARY KEY,
url TEXT NOT NULL,
mode TEXT NOT NULL,
format TEXT NOT NULL,
created_at INTEGER NOT NULL
);
`);
db.exec(`
CREATE TABLE IF NOT EXISTS job_events (
id INTEGER PRIMARY KEY AUTOINCREMENT,
job_id TEXT NOT NULL,
seq INTEGER NOT NULL,
event_json TEXT NOT NULL,
FOREIGN KEY (job_id) REFERENCES jobs(id),
UNIQUE (job_id, seq)
);
CREATE INDEX IF NOT EXISTS idx_job_events_job ON job_events(job_id, seq);
`);
// Single-row settings table (id = 1). Settings are user-editable; stored as
// JSON so adding a field later needs no migration.
db.exec(`
CREATE TABLE IF NOT EXISTS settings (
id INTEGER PRIMARY KEY CHECK (id = 1),
settings_json TEXT NOT NULL
);
`);
const insertJob = db.prepare(
"INSERT INTO jobs (id, url, mode, format, created_at) VALUES (?, ?, ?, ?, ?)",
);
const insertEvent = db.prepare(
"INSERT INTO job_events (job_id, seq, event_json) VALUES (?, ?, ?)",
);
const selectJob = db.prepare("SELECT * FROM jobs WHERE id = ?");
const selectEvents = db.prepare(
"SELECT event_json FROM job_events WHERE job_id = ? ORDER BY seq ASC",
);
const selectAllJobs = db.prepare("SELECT * FROM jobs ORDER BY created_at DESC");
const selectSettings = db.prepare("SELECT settings_json FROM settings WHERE id = 1");
const upsertSettings = db.prepare(
"INSERT INTO settings (id, settings_json) VALUES (1, ?) ON CONFLICT(id) DO UPDATE SET settings_json = excluded.settings_json",
);
return {
createJob(url, mode, format): JobId {
const id = generateJobId();
insertJob.run(id, url, mode, format, Date.now());
return id;
},
appendEvent(jobId, event) {
// seq = next sequence number for this job
const countRow = db
.prepare("SELECT COUNT(*) as n FROM job_events WHERE job_id = ?")
.get(jobId) as { n: number } | null;
const seq = (countRow?.n ?? 0) + 1;
insertEvent.run(jobId, seq, JSON.stringify(event));
},
getJob(jobId): Job | null {
const row = selectJob.get(jobId) as JobRow | null;
if (!row) return null;
const eventRows = selectEvents.all(jobId) as { event_json: string }[];
const events = eventRows.map((r) => JSON.parse(r.event_json) as JobEvent);
return {
id: row.id,
url: row.url,
mode: row.mode as JobMode,
format: row.format as AudioFormat,
status: reconcile(events).status,
events,
createdAt: row.created_at,
};
},
listJobs(): Job[] {
const rows = selectAllJobs.all() as JobRow[];
return rows.map((row) => {
const eventRows = selectEvents.all(row.id) as { event_json: string }[];
const events = eventRows.map((r) => JSON.parse(r.event_json) as JobEvent);
return {
id: row.id,
url: row.url,
mode: row.mode as JobMode,
format: row.format as AudioFormat,
status: reconcile(events).status,
events,
createdAt: row.created_at,
};
});
},
getSettings(): Settings {
const row = selectSettings.get() as { settings_json: string } | null;
if (!row) return defaultSettings;
try {
return JSON.parse(row.settings_json) as Settings;
} catch {
// Corrupt row — fall back to the default rather than crashing.
return defaultSettings;
}
},
saveSettings(settings: Settings): void {
upsertSettings.run(JSON.stringify(settings));
},
close() {
db.close();
},
};
}
interface JobRow {
id: string;
url: string;
mode: string;
format: string;
created_at: number;
}
function generateJobId(): JobId {
return `job_${Date.now().toString(36)}_${Math.random().toString(36).slice(2, 8)}`;
}
|
