1 files changed, 52 insertions, 0 deletions
diff --git a/app/lib/db.server.ts b/app/lib/db.server.ts
index b076ec7..cd8d198 100644
--- a/app/lib/db.server.ts
+++ b/app/lib/db.server.ts
@@ -22,9 +22,18 @@ function initSchema(db: Database.Database) {
       created_at TEXT NOT NULL DEFAULT (datetime('now'))
     );
     CREATE INDEX IF NOT EXISTS idx_posts_created_at ON posts(created_at DESC);
+
+    CREATE TABLE IF NOT EXISTS credentials (
+      id         TEXT PRIMARY KEY,
+      public_key TEXT NOT NULL,
+      counter    INTEGER NOT NULL DEFAULT 0,
+      transports TEXT
+    );
   `);
 }
 
+/* ── Posts ── */
+
 export interface Post {
   id: string;
   content: string;
@@ -48,3 +57,46 @@ export function createPost(input: CreatePostInput): Post {
   getDb().prepare("INSERT INTO posts (id, content) VALUES (?, ?)").run(input.id, input.content);
   return getPostById(input.id)!;
 }
+
+export function deletePost(id: string): void {
+  getDb().prepare("DELETE FROM posts WHERE id = ?").run(id);
+}
+
+/* ── Credentials (passkey) ── */
+
+export interface Credential {
+  id: string;
+  public_key: string;
+  counter: number;
+  transports: string | 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;
+  publicKey: Uint8Array;
+  counter: number;
+  transports?: string[];
+}): void {
+  getDb()
+    .prepare(
+      "INSERT INTO credentials (id, public_key, counter, transports) VALUES (?, ?, ?, ?)"
+    )
+    .run(
+      input.id,
+      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);
+}