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
159
160
|
import Database from "better-sqlite3";
import path from "path";
let db: Database.Database | null = null;
export function getDb(): Database.Database {
if (!db) {
const dbPath = process.env.DB_PATH ?? path.resolve("todo.db");
db = new Database(dbPath);
db.pragma("journal_mode = WAL");
db.pragma("foreign_keys = ON");
initSchema(db);
}
return db;
}
function initSchema(db: Database.Database) {
db.exec(`
CREATE TABLE IF NOT EXISTS users (
id TEXT PRIMARY KEY,
username TEXT UNIQUE NOT NULL,
created_at TEXT NOT NULL DEFAULT (datetime('now'))
);
CREATE TABLE IF NOT EXISTS credentials (
id TEXT PRIMARY KEY,
user_id TEXT NOT NULL REFERENCES users(id),
public_key TEXT NOT NULL,
counter INTEGER NOT NULL DEFAULT 0,
transports TEXT
);
CREATE TABLE IF NOT EXISTS todos (
id TEXT PRIMARY KEY,
user_id TEXT NOT NULL REFERENCES users(id),
title TEXT NOT NULL,
done INTEGER NOT NULL DEFAULT 0,
created_at TEXT NOT NULL DEFAULT (datetime('now'))
);
CREATE INDEX IF NOT EXISTS idx_todos_user ON todos(user_id, created_at DESC);
`);
}
/* ── Users ── */
export interface User {
id: string;
username: string;
created_at: string;
}
export function getUserById(id: string): User | null {
return getDb().prepare("SELECT * FROM users WHERE id = ?").get(id) as User | null;
}
export function getUserByUsername(username: string): User | null {
return getDb().prepare("SELECT * FROM users WHERE username = ?").get(username) as User | null;
}
export function upsertUser(id: string, username: string): User {
getDb()
.prepare("INSERT OR IGNORE INTO users (id, username) VALUES (?, ?)")
.run(id, username);
return getUserById(id) ?? getUserByUsername(username)!;
}
/* ── Credentials ── */
export interface Credential {
id: string;
user_id: string;
public_key: string;
counter: number;
transports: string | null;
}
export function hasAnyCredential(): boolean {
return (getDb().prepare("SELECT 1 FROM credentials LIMIT 1").get() as unknown) != null;
}
export function listCredentials(): Credential[] {
return getDb().prepare("SELECT * FROM credentials").all() as Credential[];
}
export function getCredentialById(id: string): Credential | null {
return getDb().prepare("SELECT * FROM credentials WHERE id = ?").get(id) as Credential | null;
}
export function saveCredential(input: {
id: string;
userId: string;
publicKey: Uint8Array;
counter: number;
transports?: string[];
}): void {
getDb()
.prepare(
"INSERT INTO credentials (id, user_id, public_key, counter, transports) VALUES (?, ?, ?, ?, ?)"
)
.run(
input.id,
input.userId,
Buffer.from(input.publicKey).toString("base64url"),
input.counter,
input.transports ? JSON.stringify(input.transports) : null
);
}
export function updateCredentialCounter(id: string, counter: number): void {
getDb().prepare("UPDATE credentials SET counter = ? WHERE id = ?").run(counter, id);
}
/* ── Todos ── */
export interface Todo {
id: string;
user_id: string;
title: string;
done: number;
created_at: string;
}
export function listTodos(userId: string, filter?: string): Todo[] {
if (filter === "active") {
return getDb()
.prepare("SELECT * FROM todos WHERE user_id = ? AND done = 0 ORDER BY created_at DESC")
.all(userId) as Todo[];
}
if (filter === "done") {
return getDb()
.prepare("SELECT * FROM todos WHERE user_id = ? AND done = 1 ORDER BY created_at DESC")
.all(userId) as Todo[];
}
return getDb()
.prepare("SELECT * FROM todos WHERE user_id = ? ORDER BY created_at DESC")
.all(userId) as Todo[];
}
export function createTodo(userId: string, title: string): void {
getDb()
.prepare("INSERT INTO todos (id, user_id, title) VALUES (?, ?, ?)")
.run(crypto.randomUUID(), userId, title);
}
export function toggleTodo(id: string, userId: string, done: boolean): void {
getDb()
.prepare("UPDATE todos SET done = ? WHERE id = ? AND user_id = ?")
.run(done ? 1 : 0, id, userId);
}
export function deleteTodo(id: string, userId: string): void {
getDb().prepare("DELETE FROM todos WHERE id = ? AND user_id = ?").run(id, userId);
}
export function countActiveTodos(userId: string): number {
const row = getDb()
.prepare("SELECT COUNT(*) as n FROM todos WHERE user_id = ? AND done = 0")
.get(userId) as { n: number };
return row.n;
}
|
