From cba06f7e23417a9aef6c15ae26715385feb225ba Mon Sep 17 00:00:00 2001
From: yyamashita <yyamashita@hetzner.yyamashita.com>
Date: Fri, 19 Jun 2026 19:45:41 +0900
Subject: Add white theme, post delete, and passkey authentication

- Rewrite CSS to light/white theme
- Add delete button (auth-gated) with confirm dialog
- Add passkey register/login/logout routes via @simplewebauthn
- Gate compose form and delete behind session authentication
- Add credentials table to SQLite schema
- Add SESSION_SECRET and WebAuthn env vars to docker-compose

Co-Authored-By: Claude Sonnet 4.6 <noreply@anthropic.com>
---
 app/lib/db.server.ts | 52 ++++++++++++++++++++++++++++++++++++++++++++++++++++
 1 file changed, 52 insertions(+)

(limited to 'app/lib/db.server.ts')

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);
+}
-- 
cgit v1.2.3