[{"_1":2,"_175":-5,"_176":-5},"loaderData",{"_3":4,"_92":93},"root",{"_5":6,"_35":36,"_65":66},"ctx",{"_7":8,"_23":-5,"_24":25,"_26":27,"_28":20,"_29":20,"_30":20,"_31":32,"_33":20,"_34":20},"auth",{"_9":-5,"_10":-5,"_11":-5,"_12":-5,"_13":-5,"_14":-5,"_15":-5,"_16":-5,"_17":-5,"_18":-5,"_19":20,"_21":22},"sessionClaims","sessionId","sessionStatus","userId","actor","orgId","orgRole","orgSlug","orgPermissions","factorVerificationAge","isAuthenticated",false,"tokenType","session_token","user","preferencesKeybindings","default","preferencesTheme","light","preferencesFixers","preferencesCopilot","preferencesSelectionAi","notificationCount",0,"flagDenols","isTeamOrgMember","ENV",{"_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},"REACT_APP_SENTRY_DSN","https://586020d86f05489db63a554690acf1e9@o1398359.ingest.sentry.io/6724499","VAL_RUNTIME_NAME","prod","RENDER_GIT_COMMIT","48807a73dfb4c250ae0af97d07e94d04aadb2cac","RENDER_GIT_BRANCH","main","CLERK_PUBLISHABLE_KEY","pk_live_Y2xlcmsudmFsLnRvd24k","POSTHOG_API_TOKEN","phc_nmoyuAkStEZuUp69rYlg1DZgrIt19Gx0Y4Hwf8tGgJP","POSTHOG_API_HOST","https://wart.val.town","WEB_API_TEMPLATE","https://{val}.web.val.run","APP_URL","https://www.val.town","MODULE_URL","https://esm.town","VANITY_WEB_API_TEMPLATE","https://{label}.val.run/","DENOLS_URL","https://lsp.val.town","NODE_ENV","production","API_URL","https://api.val.town","clerkState",{"_67":68},"__internal_clerk_state",{"_69":70,"_71":46,"_72":73,"_74":73,"_75":20,"_76":73,"_77":73,"_78":73,"_79":73,"_80":81,"_90":20,"_91":20},"__clerk_ssr_state",{"_21":22,"_9":-5,"_10":-5,"_11":-5,"_12":-5,"_13":-5,"_14":-5,"_15":-5,"_16":-5,"_17":-5,"_18":-5,"_19":20},"__publishableKey","__proxyUrl","","__domain","__isSatellite","__signInUrl","__signUpUrl","__afterSignInUrl","__afterSignUpUrl","__clerk_debug",{"_82":20,"_19":20,"_83":73,"_84":85,"_86":73,"_87":46,"_88":20,"_89":73},"isSignedIn","proxyUrl","reason","session-token-and-uat-missing","message","publishableKey","isSatellite","domain","__telemetryDisabled","__telemetryDebug","routes/embed.x.$handle.$name.$",{"_94":95,"_96":97,"_98":99,"_100":101,"_154":155,"_172":173,"_174":-5},"handle","axiom","name","the-playbook-hono-react","path","database.ts","project",{"_102":103,"_96":97,"_104":105,"_106":-5,"_107":20,"_108":109,"_110":111,"_112":-5,"_12":113,"_114":115,"_116":117,"_118":-5,"_119":-5,"_120":32,"_14":121,"_122":123,"_124":123,"_125":126,"_127":126,"_128":126,"_129":123,"_130":123,"_23":131,"_134":135,"_137":138,"_153":20},"id","95d89656-d488-11f0-820e-42dde27851f2","createdAt",["D",1765234354304],"deletedAt","legacyEnvVars","privacy","public","description","open-source demo by Banks (jabanks@gmail.com)","imageUrl","019af6f6-fda3-778e-a508-5639a1468e03","pinnedFileId","019b002a-a8a5-767b-b671-60880fd87a03","pinPreview",true,"pinMigrated","screenshotVersion","likeCount","019af6f6-fda3-778e-a508-5b6cc66d79d5","userPerm","r","valPerm","blobPerm","rw","emailPerm","sqlitePerm","projectPerm","telemetryPerm",{"_94":95,"_132":133},"profileImageUrl","https://img.clerk.com/eyJ0eXBlIjoiZGVmYXVsdCIsImlpZCI6Imluc18yTm10cGhQUklwU0tqZHNiOHNnNGd2ZjJURFUiLCJyaWQiOiJ1c2VyXzM2VjdZZnVOblV5ZUFaZlRaS3RQdVVHbzhYTiJ9","org",{"_94":95,"_136":133,"_12":113},"avatar","branch",{"_139":140,"_96":44,"_102":141,"_142":103,"_143":144,"_145":146,"_147":109,"_148":149,"_150":121,"_151":97,"_152":95},"version",23,"95db7fa6-d488-11f0-820e-42dde27851f2","projectId","forkedBranchId","7da38c7c-d464-11f0-a05c-42dde27851f2","forkedBranchVersion",37,"forkedProjectPrivacy","forkedProjectId","7da33fa6-d464-11f0-a05c-42dde27851f2","forkedProjectOrgId","projectName","userHandle","isOwned","file",{"_102":156,"_96":99,"_157":158,"_159":-5,"_160":141,"_161":140,"_162":117,"_104":163,"_139":164,"_165":166,"_167":168,"_169":20,"_170":-5,"_98":171},"69cbb88c-d7cf-11f0-89f1-42dde27851f2","fileId","019b002a-a8a5-767b-b671-593901c41393","parentId","branchId","branchVersion","latest",["D",1765594628157],4,"fileType","script","content","/**\n * @fileoverview Database schema, migrations, and CRUD operations for The Playbook.\n * Handles all SQLite interactions with type-safe wrappers.\n *\n * @author The Playbook Team\n * @version 3.0.0\n */\n\nimport { sqlite } from \"https://esm.town/v/std/sqlite?v=6\";\nimport { nanoid } from \"npm:nanoid@5.0.7\";\nimport { DB_VERSION, SETUP_COMPLETE_KEY } from \"./config.ts\";\nimport type {\n CreateSessionInput,\n DatabaseRow,\n Document,\n DocumentStatus,\n DocumentSummary,\n Session,\n SetupConfig,\n StoredAnalysis,\n TierId,\n User,\n} from \"./types.ts\";\n\n// =============================================================================\n// DATABASE INITIALIZATION & MIGRATIONS\n// =============================================================================\n\n/**\n * Unique table prefix for this Playbook instance\n */\nconst TABLE_PREFIX = `pb3_`;\n\n/**\n * SQL statements for creating database tables.\n * Executed in order during initialization.\n */\nconst SCHEMA_MIGRATIONS: string[] = [\n // Users table\n `CREATE TABLE IF NOT EXISTS ${TABLE_PREFIX}users (\n id TEXT PRIMARY KEY,\n email TEXT UNIQUE NOT NULL,\n name TEXT,\n tier TEXT DEFAULT 'tier_rookie',\n avatar_url TEXT,\n created_at DATETIME DEFAULT CURRENT_TIMESTAMP,\n updated_at DATETIME DEFAULT CURRENT_TIMESTAMP\n )`,\n\n // Sessions table\n `CREATE TABLE IF NOT EXISTS ${TABLE_PREFIX}sessions (\n id TEXT PRIMARY KEY,\n user_id TEXT NOT NULL,\n expires_at DATETIME NOT NULL,\n user_agent TEXT,\n ip_address TEXT,\n created_at DATETIME DEFAULT CURRENT_TIMESTAMP,\n FOREIGN KEY(user_id) REFERENCES ${TABLE_PREFIX}users(id) ON DELETE CASCADE\n )`,\n\n // Documents table\n `CREATE TABLE IF NOT EXISTS ${TABLE_PREFIX}documents (\n id TEXT PRIMARY KEY,\n user_id TEXT NOT NULL,\n title TEXT NOT NULL,\n text_content TEXT NOT NULL,\n word_count INTEGER DEFAULT 0,\n status TEXT DEFAULT 'pending',\n created_at DATETIME DEFAULT CURRENT_TIMESTAMP,\n updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,\n FOREIGN KEY(user_id) REFERENCES ${TABLE_PREFIX}users(id) ON DELETE CASCADE\n )`,\n\n // Analyses table\n `CREATE TABLE IF NOT EXISTS ${TABLE_PREFIX}analyses (\n id TEXT PRIMARY KEY,\n document_id TEXT NOT NULL,\n lens_id TEXT NOT NULL,\n squad TEXT NOT NULL,\n analysis_json TEXT,\n score INTEGER DEFAULT 0,\n created_at DATETIME DEFAULT CURRENT_TIMESTAMP,\n FOREIGN KEY(document_id) REFERENCES ${TABLE_PREFIX}documents(id) ON DELETE CASCADE\n )`,\n\n // Playbook reports (combined analyses)\n `CREATE TABLE IF NOT EXISTS ${TABLE_PREFIX}playbook_reports (\n id TEXT PRIMARY KEY,\n user_id TEXT NOT NULL,\n document_id TEXT NOT NULL,\n report_json TEXT,\n offensive_score INTEGER,\n defensive_score INTEGER,\n combined_score INTEGER,\n created_at DATETIME DEFAULT CURRENT_TIMESTAMP,\n FOREIGN KEY(user_id) REFERENCES ${TABLE_PREFIX}users(id) ON DELETE CASCADE,\n FOREIGN KEY(document_id) REFERENCES ${TABLE_PREFIX}documents(id) ON DELETE CASCADE\n )`,\n\n // System configuration (for setup wizard, etc.)\n `CREATE TABLE IF NOT EXISTS ${TABLE_PREFIX}system_config (\n key TEXT PRIMARY KEY,\n value TEXT NOT NULL,\n updated_at DATETIME DEFAULT CURRENT_TIMESTAMP\n )`,\n\n // Usage logs for analytics\n `CREATE TABLE IF NOT EXISTS ${TABLE_PREFIX}usage_logs (\n id INTEGER PRIMARY KEY AUTOINCREMENT,\n user_id TEXT NOT NULL,\n action TEXT NOT NULL,\n metadata_json TEXT,\n created_at DATETIME DEFAULT CURRENT_TIMESTAMP\n )`,\n\n // Create indexes for performance\n `CREATE INDEX IF NOT EXISTS idx_${TABLE_PREFIX}sessions_user ON ${TABLE_PREFIX}sessions(user_id)`,\n `CREATE INDEX IF NOT EXISTS idx_${TABLE_PREFIX}sessions_expires ON ${TABLE_PREFIX}sessions(expires_at)`,\n `CREATE INDEX IF NOT EXISTS idx_${TABLE_PREFIX}documents_user ON ${TABLE_PREFIX}documents(user_id)`,\n `CREATE INDEX IF NOT EXISTS idx_${TABLE_PREFIX}analyses_document ON ${TABLE_PREFIX}analyses(document_id)`,\n `CREATE INDEX IF NOT EXISTS idx_${TABLE_PREFIX}usage_user ON ${TABLE_PREFIX}usage_logs(user_id)`,\n];\n\n/**\n * Initializes the database schema.\n * Runs all migrations in sequence, skipping any that already exist.\n *\n * @returns Promise that resolves when initialization is complete\n * @throws Error if a critical migration fails\n */\nexport async function initializeDatabase(): Promise {\n console.log(`[Database] Initializing schema v${DB_VERSION}...`);\n\n for (const migration of SCHEMA_MIGRATIONS) {\n try {\n await sqlite.execute(migration);\n } catch (error) {\n // Log but don't throw for \"already exists\" errors\n const errorMessage = error instanceof Error\n ? error.message\n : String(error);\n if (!errorMessage.includes(\"already exists\")) {\n console.error(`[Database] Migration error:`, errorMessage);\n }\n }\n }\n\n console.log(`[Database] Schema initialization complete.`);\n}\n\n// =============================================================================\n// ROW PARSING HELPERS\n// =============================================================================\n\n/**\n * Safely extracts a value from a database row.\n * Handles both array and object row formats from sqlite driver.\n *\n * @param row - The database row\n * @param key - The column name (for object format)\n * @param index - The column index (for array format)\n * @returns The extracted value or undefined\n */\nfunction extractValue(row: DatabaseRow, key: string, index: number): unknown {\n if (Array.isArray(row)) {\n return row[index];\n }\n if (typeof row === \"object\" && row !== null) {\n return (row as Record)[key];\n }\n return undefined;\n}\n\n/**\n * Parses a database row into a User object.\n */\nfunction parseUserRow(row: DatabaseRow): User {\n return {\n id: String(extractValue(row, \"id\", 0)),\n email: String(extractValue(row, \"email\", 1)),\n name: extractValue(row, \"name\", 2) as string | null,\n tier: String(extractValue(row, \"tier\", 3)) as TierId,\n avatarUrl: extractValue(row, \"avatar_url\", 4) as string | undefined,\n createdAt: String(extractValue(row, \"created_at\", 5)),\n };\n}\n\n/**\n * Parses a database row into a Document object.\n */\nfunction parseDocumentRow(row: DatabaseRow): Document {\n return {\n id: String(extractValue(row, \"id\", 0)),\n userId: String(extractValue(row, \"user_id\", 1)),\n title: String(extractValue(row, \"title\", 2)),\n textContent: String(extractValue(row, \"text_content\", 3)),\n wordCount: Number(extractValue(row, \"word_count\", 4)) || 0,\n status: String(extractValue(row, \"status\", 5)) as DocumentStatus,\n createdAt: String(extractValue(row, \"created_at\", 6)),\n };\n}\n\n/**\n * Parses a database row into a DocumentSummary object.\n */\nfunction parseDocumentSummaryRow(row: DatabaseRow): DocumentSummary {\n return {\n id: String(extractValue(row, \"id\", 0)),\n title: String(extractValue(row, \"title\", 1)),\n wordCount: Number(extractValue(row, \"word_count\", 2)) || 0,\n status: String(extractValue(row, \"status\", 3)) as DocumentStatus,\n createdAt: String(extractValue(row, \"created_at\", 4)),\n };\n}\n\n// =============================================================================\n// SETUP CONFIGURATION OPERATIONS\n// =============================================================================\n\n/**\n * Checks if the initial setup wizard has been completed.\n *\n * @returns Promise - true if setup is complete\n */\nexport async function isSetupComplete(): Promise {\n try {\n const result = await sqlite.execute({\n sql: `SELECT value FROM ${TABLE_PREFIX}system_config WHERE key = ?`,\n args: [SETUP_COMPLETE_KEY],\n });\n\n if (result.rows.length === 0) {\n return false;\n }\n\n const value = extractValue(result.rows[0], \"value\", 0);\n return value === \"true\";\n } catch {\n // Table might not exist yet\n return false;\n }\n}\n\n/**\n * Marks the setup wizard as complete.\n *\n * @param config - Optional configuration to store\n */\nexport async function completeSetup(\n config?: Partial,\n): Promise {\n const now = new Date().toISOString();\n\n await sqlite.execute({\n sql:\n `INSERT OR REPLACE INTO ${TABLE_PREFIX}system_config (key, value, updated_at) VALUES (?, ?, ?)`,\n args: [SETUP_COMPLETE_KEY, \"true\", now],\n });\n\n if (config?.adminUserId) {\n await sqlite.execute({\n sql:\n `INSERT OR REPLACE INTO ${TABLE_PREFIX}system_config (key, value, updated_at) VALUES (?, ?, ?)`,\n args: [\"admin_user_id\", config.adminUserId, now],\n });\n }\n}\n\n/**\n * Gets the current setup configuration.\n */\nexport async function getSetupConfig(): Promise {\n const result = await sqlite.execute({\n sql:\n `SELECT key, value FROM ${TABLE_PREFIX}system_config WHERE key IN (?, ?, ?)`,\n args: [SETUP_COMPLETE_KEY, \"admin_user_id\", \"completed_at\"],\n });\n\n const config: SetupConfig = { isComplete: false };\n\n for (const row of result.rows) {\n const key = extractValue(row, \"key\", 0);\n const value = extractValue(row, \"value\", 1);\n\n if (key === SETUP_COMPLETE_KEY) {\n config.isComplete = value === \"true\";\n } else if (key === \"admin_user_id\") {\n config.adminUserId = String(value);\n } else if (key === \"completed_at\") {\n config.completedAt = String(value);\n }\n }\n\n return config;\n}\n\n// =============================================================================\n// USER OPERATIONS\n// =============================================================================\n\n/**\n * Finds a user by their email address.\n *\n * @param email - The email to search for\n * @returns The user if found, null otherwise\n */\nexport async function findUserByEmail(email: string): Promise {\n const result = await sqlite.execute({\n sql:\n `SELECT id, email, name, tier, avatar_url, created_at FROM ${TABLE_PREFIX}users WHERE email = ?`,\n args: [email],\n });\n\n if (result.rows.length === 0) {\n return null;\n }\n\n return parseUserRow(result.rows[0]);\n}\n\n/**\n * Finds a user by their ID.\n *\n * @param id - The user ID\n * @returns The user if found, null otherwise\n */\nexport async function findUserById(id: string): Promise {\n const result = await sqlite.execute({\n sql:\n `SELECT id, email, name, tier, avatar_url, created_at FROM ${TABLE_PREFIX}users WHERE id = ?`,\n args: [id],\n });\n\n if (result.rows.length === 0) {\n return null;\n }\n\n return parseUserRow(result.rows[0]);\n}\n\n/**\n * Creates a new user.\n *\n * @param email - User's email address\n * @param name - Optional display name\n * @param tier - Initial tier (defaults to rookie)\n * @returns The created user\n */\nexport async function createUser(\n email: string,\n name?: string,\n tier: TierId = \"tier_rookie\",\n): Promise {\n const id = `usr_${nanoid(16)}`;\n const now = new Date().toISOString();\n\n await sqlite.execute({\n sql:\n `INSERT INTO ${TABLE_PREFIX}users (id, email, name, tier, created_at, updated_at) VALUES (?, ?, ?, ?, ?, ?)`,\n args: [id, email, name || null, tier, now, now],\n });\n\n return {\n id,\n email,\n name: name || null,\n tier,\n createdAt: now,\n };\n}\n\n/**\n * Updates a user's tier.\n *\n * @param userId - The user's ID\n * @param tier - The new tier\n */\nexport async function updateUserTier(\n userId: string,\n tier: TierId,\n): Promise {\n const now = new Date().toISOString();\n\n await sqlite.execute({\n sql:\n `UPDATE ${TABLE_PREFIX}users SET tier = ?, updated_at = ? WHERE id = ?`,\n args: [tier, now, userId],\n });\n}\n\n/**\n * Updates a user's profile information.\n *\n * @param userId - The user's ID\n * @param updates - Fields to update\n */\nexport async function updateUserProfile(\n userId: string,\n updates: { name?: string; avatarUrl?: string },\n): Promise {\n const now = new Date().toISOString();\n const setClauses: string[] = [\"updated_at = ?\"];\n const args: (string | null)[] = [now];\n\n if (updates.name !== undefined) {\n setClauses.push(\"name = ?\");\n args.push(updates.name);\n }\n\n if (updates.avatarUrl !== undefined) {\n setClauses.push(\"avatar_url = ?\");\n args.push(updates.avatarUrl);\n }\n\n args.push(userId);\n\n await sqlite.execute({\n sql: `UPDATE ${TABLE_PREFIX}users SET ${\n setClauses.join(\", \")\n } WHERE id = ?`,\n args,\n });\n}\n\n// =============================================================================\n// SESSION OPERATIONS\n// =============================================================================\n\n/**\n * Creates a new session for a user.\n *\n * @param input - Session creation parameters\n * @returns The session ID\n */\nexport async function createSession(\n input: CreateSessionInput,\n): Promise {\n const { userId, userAgent, ipAddress } = input;\n const sessionId = `sess_${nanoid(32)}`;\n const now = new Date();\n const expiresAt = new Date(now.getTime() + 30 * 24 * 60 * 60 * 1000); // 30 days\n\n await sqlite.execute({\n sql:\n `INSERT INTO ${TABLE_PREFIX}sessions (id, user_id, expires_at, user_agent, ip_address, created_at) \n VALUES (?, ?, ?, ?, ?, ?)`,\n args: [\n sessionId,\n userId,\n expiresAt.toISOString(),\n userAgent || null,\n ipAddress || null,\n now.toISOString(),\n ],\n });\n\n return sessionId;\n}\n\n/**\n * Validates a session and returns the associated user.\n *\n * @param sessionId - The session ID to validate\n * @returns The user if session is valid, null otherwise\n */\nexport async function validateSession(sessionId: string): Promise {\n const result = await sqlite.execute({\n sql: `\n SELECT u.id, u.email, u.name, u.tier, u.avatar_url, u.created_at, s.expires_at\n FROM ${TABLE_PREFIX}sessions s\n JOIN ${TABLE_PREFIX}users u ON s.user_id = u.id\n WHERE s.id = ?\n `,\n args: [sessionId],\n });\n\n if (result.rows.length === 0) {\n return null;\n }\n\n const row = result.rows[0];\n const expiresAt = new Date(String(extractValue(row, \"expires_at\", 6)));\n\n if (expiresAt < new Date()) {\n // Session expired, clean it up\n await deleteSession(sessionId);\n return null;\n }\n\n return parseUserRow(row);\n}\n\n/**\n * Deletes a session.\n *\n * @param sessionId - The session ID to delete\n */\nexport async function deleteSession(sessionId: string): Promise {\n await sqlite.execute({\n sql: `DELETE FROM ${TABLE_PREFIX}sessions WHERE id = ?`,\n args: [sessionId],\n });\n}\n\n/**\n * Deletes all sessions for a user.\n *\n * @param userId - The user ID\n */\nexport async function deleteUserSessions(userId: string): Promise {\n await sqlite.execute({\n sql: `DELETE FROM ${TABLE_PREFIX}sessions WHERE user_id = ?`,\n args: [userId],\n });\n}\n\n/**\n * Cleans up expired sessions.\n *\n * @returns Number of sessions deleted\n */\nexport async function cleanupExpiredSessions(): Promise {\n const result = await sqlite.execute({\n sql:\n `DELETE FROM ${TABLE_PREFIX}sessions WHERE expires_at < datetime('now')`,\n args: [],\n });\n\n return result.rowsAffected || 0;\n}\n\n// =============================================================================\n// DOCUMENT OPERATIONS\n// =============================================================================\n\n/**\n * Creates a new document.\n *\n * @param userId - Owner's user ID\n * @param title - Document title\n * @param textContent - Full text content\n * @returns The created document\n */\nexport async function createDocument(\n userId: string,\n title: string,\n textContent: string,\n): Promise {\n const id = `doc_${nanoid(24)}`;\n const now = new Date().toISOString();\n const wordCount =\n textContent.split(/\\s+/).filter((word) => word.length > 0).length;\n\n await sqlite.execute({\n sql:\n `INSERT INTO ${TABLE_PREFIX}documents (id, user_id, title, text_content, word_count, status, created_at, updated_at)\n VALUES (?, ?, ?, ?, ?, 'pending', ?, ?)`,\n args: [id, userId, title, textContent, wordCount, now, now],\n });\n\n return {\n id,\n userId,\n title,\n textContent,\n wordCount,\n status: \"pending\",\n createdAt: now,\n };\n}\n\n/**\n * Gets a document by ID with ownership verification.\n *\n * @param documentId - The document ID\n * @param userId - The expected owner's ID\n * @returns The document if found and owned by user, null otherwise\n */\nexport async function getDocument(\n documentId: string,\n userId: string,\n): Promise {\n const result = await sqlite.execute({\n sql:\n `SELECT id, user_id, title, text_content, word_count, status, created_at\n FROM ${TABLE_PREFIX}documents WHERE id = ? AND user_id = ?`,\n args: [documentId, userId],\n });\n\n if (result.rows.length === 0) {\n return null;\n }\n\n return parseDocumentRow(result.rows[0]);\n}\n\n/**\n * Gets all documents for a user.\n *\n * @param userId - The user's ID\n * @returns Array of document summaries\n */\nexport async function getUserDocuments(\n userId: string,\n): Promise {\n const result = await sqlite.execute({\n sql: `SELECT id, title, word_count, status, created_at\n FROM ${TABLE_PREFIX}documents WHERE user_id = ? ORDER BY created_at DESC`,\n args: [userId],\n });\n\n return result.rows.map(parseDocumentSummaryRow);\n}\n\n/**\n * Counts documents created by a user in the current month.\n *\n * @param userId - The user's ID\n * @returns Number of documents this month\n */\nexport async function countUserDocumentsThisMonth(\n userId: string,\n): Promise {\n const result = await sqlite.execute({\n sql: `SELECT COUNT(id) as count FROM ${TABLE_PREFIX}documents \n WHERE user_id = ? AND created_at > date('now', '-30 days')`,\n args: [userId],\n });\n\n if (result.rows.length === 0) {\n return 0;\n }\n\n const count = extractValue(result.rows[0], \"count\", 0);\n return Number(count) || 0;\n}\n\n/**\n * Updates a document's status.\n *\n * @param documentId - The document ID\n * @param status - The new status\n */\nexport async function updateDocumentStatus(\n documentId: string,\n status: DocumentStatus,\n): Promise {\n const now = new Date().toISOString();\n\n await sqlite.execute({\n sql:\n `UPDATE ${TABLE_PREFIX}documents SET status = ?, updated_at = ? WHERE id = ?`,\n args: [status, now, documentId],\n });\n}\n\n/**\n * Deletes a document and all associated analyses.\n *\n * @param documentId - The document ID\n * @param userId - The owner's ID for verification\n * @returns true if deleted, false if not found\n */\nexport async function deleteDocument(\n documentId: string,\n userId: string,\n): Promise {\n // First verify ownership\n const doc = await getDocument(documentId, userId);\n if (!doc) {\n return false;\n }\n\n // Delete associated analyses first\n await sqlite.execute({\n sql: `DELETE FROM ${TABLE_PREFIX}analyses WHERE document_id = ?`,\n args: [documentId],\n });\n\n // Delete associated reports\n await sqlite.execute({\n sql: `DELETE FROM ${TABLE_PREFIX}playbook_reports WHERE document_id = ?`,\n args: [documentId],\n });\n\n // Delete the document\n await sqlite.execute({\n sql: `DELETE FROM ${TABLE_PREFIX}documents WHERE id = ?`,\n args: [documentId],\n });\n\n return true;\n}\n\n// =============================================================================\n// ANALYSIS OPERATIONS\n// =============================================================================\n\n/**\n * Stores an analysis result.\n *\n * @param documentId - The analyzed document's ID\n * @param lensId - The lens used\n * @param squad - Which squad (phoenix/fortress/combined)\n * @param analysisJson - JSON-encoded analysis result\n * @param score - Numeric score\n * @returns The analysis ID\n */\nexport async function storeAnalysis(\n documentId: string,\n lensId: string,\n squad: \"phoenix\" | \"fortress\" | \"combined\",\n analysisJson: string,\n score: number,\n): Promise {\n const id = `anl_${nanoid(20)}`;\n const now = new Date().toISOString();\n\n await sqlite.execute({\n sql:\n `INSERT INTO ${TABLE_PREFIX}analyses (id, document_id, lens_id, squad, analysis_json, score, created_at)\n VALUES (?, ?, ?, ?, ?, ?, ?)`,\n args: [id, documentId, lensId, squad, analysisJson, score, now],\n });\n\n return id;\n}\n\n/**\n * Gets all analyses for a document.\n *\n * @param documentId - The document ID\n * @returns Array of stored analyses\n */\nexport async function getDocumentAnalyses(\n documentId: string,\n): Promise {\n const result = await sqlite.execute({\n sql:\n `SELECT id, document_id, lens_id, squad, analysis_json, score, created_at\n FROM ${TABLE_PREFIX}analyses WHERE document_id = ? ORDER BY created_at DESC`,\n args: [documentId],\n });\n\n return result.rows.map((row) => ({\n id: String(extractValue(row, \"id\", 0)),\n documentId: String(extractValue(row, \"document_id\", 1)),\n lensId: String(extractValue(row, \"lens_id\", 2)),\n squad: String(extractValue(row, \"squad\", 3)) as\n | \"phoenix\"\n | \"fortress\"\n | \"combined\",\n analysisJson: String(extractValue(row, \"analysis_json\", 4)),\n score: Number(extractValue(row, \"score\", 5)) || 0,\n createdAt: String(extractValue(row, \"created_at\", 6)),\n }));\n}\n\n/**\n * Gets the core beliefs analysis for a document.\n *\n * @param documentId - The document ID\n * @returns Beliefs and score, or null if not found\n */\nexport async function getDocumentBeliefs(\n documentId: string,\n): Promise<{ beliefs: string[]; score: number } | null> {\n const result = await sqlite.execute({\n sql: `SELECT analysis_json, score FROM ${TABLE_PREFIX}analyses \n WHERE document_id = ? AND lens_id = 'core_beliefs' \n ORDER BY created_at DESC LIMIT 1`,\n args: [documentId],\n });\n\n if (result.rows.length === 0) {\n return null;\n }\n\n const row = result.rows[0];\n const analysisJson = String(extractValue(row, \"analysis_json\", 0));\n const score = Number(extractValue(row, \"score\", 1)) || 0;\n\n try {\n const beliefs = JSON.parse(analysisJson);\n return { beliefs: Array.isArray(beliefs) ? beliefs : [], score };\n } catch {\n return { beliefs: [], score };\n }\n}\n\n/**\n * Stores a Phoenix Fortress combined report.\n *\n * @param userId - The user's ID\n * @param documentId - The document ID\n * @param reportJson - JSON-encoded full report\n * @param scores - Individual and combined scores\n * @returns The report ID\n */\nexport async function storePlaybookReport(\n userId: string,\n documentId: string,\n reportJson: string,\n scores: { offensive: number; defensive: number; combined: number },\n): Promise {\n const id = `rpt_${nanoid(20)}`;\n const now = new Date().toISOString();\n\n await sqlite.execute({\n sql: `INSERT INTO ${TABLE_PREFIX}playbook_reports \n (id, user_id, document_id, report_json, offensive_score, defensive_score, combined_score, created_at)\n VALUES (?, ?, ?, ?, ?, ?, ?, ?)`,\n args: [\n id,\n userId,\n documentId,\n reportJson,\n scores.offensive,\n scores.defensive,\n scores.combined,\n now,\n ],\n });\n\n return id;\n}\n\n// =============================================================================\n// USAGE LOGGING\n// =============================================================================\n\n/**\n * Logs a user action for analytics.\n *\n * @param userId - The user's ID\n * @param action - Action identifier\n * @param metadata - Optional metadata object\n */\nexport async function logUsage(\n userId: string,\n action: string,\n metadata?: Record,\n): Promise {\n await sqlite.execute({\n sql:\n `INSERT INTO ${TABLE_PREFIX}usage_logs (user_id, action, metadata_json, created_at)\n VALUES (?, ?, ?, datetime('now'))`,\n args: [userId, action, metadata ? JSON.stringify(metadata) : null],\n });\n}\n\n/**\n * Gets usage statistics for a user.\n *\n * @param userId - The user's ID\n * @param days - Number of days to look back\n * @returns Usage counts by action type\n */\nexport async function getUserUsageStats(\n userId: string,\n days: number = 30,\n): Promise> {\n const result = await sqlite.execute({\n sql: `SELECT action, COUNT(*) as count FROM ${TABLE_PREFIX}usage_logs \n WHERE user_id = ? AND created_at > date('now', '-' || ? || ' days')\n GROUP BY action`,\n args: [userId, days],\n });\n\n const stats: Record = {};\n\n for (const row of result.rows) {\n const action = String(extractValue(row, \"action\", 0));\n const count = Number(extractValue(row, \"count\", 1)) || 0;\n stats[action] = count;\n }\n\n return stats;\n}","deleted","label",[99],"permalink","https://www.val.town/x/axiom/the-playbook-hono-react/code/database.ts","extension","actionData","errors"]