| 1 | const db = getDatabase("questions.sqlite"); |
| 2 | |
| 3 | /** how each type is stored */ |
| 4 | export const types = { normal: 0, reject: 1, pending: 3 } as const; |
| 5 | export type Type = keyof typeof types; |
| 6 | const dbQuestionType = sqliteNumericEnum(types); |
| 7 | |
| 8 | const dbQuestions = sqliteTable("questions", { |
| 9 | /** |
| 10 | * questions are identified by a 'minute identifier' |
| 11 | * which is time / 60000 (minutes since epoch) |
| 12 | */ |
| 13 | qmid: integer().primaryKey(), |
| 14 | type: dbQuestionType().notNull(), |
| 15 | /** for type="pending" questions, this is a JSON payload */ |
| 16 | text: text().notNull(), |
| 17 | }); |
| 18 | db.migrate(dbQuestions, 2, { |
| 19 | // a search index nothing reads, which kept the text of rejected and deleted questions |
| 20 | 2: (db) => { |
| 21 | db.run(sql`drop trigger if exists questions_ai`); |
| 22 | db.run(sql`drop table if exists questions_fts`); |
| 23 | }, |
| 24 | }); |
| 25 | |
| 26 | export type Question = ReturnType<typeof toQuestion>; |
| 27 | |
| 28 | /** the payload of a question that has not been answered */ |
| 29 | export type PendingData = { |
| 30 | date: string; |
| 31 | prompt: string; |
| 32 | sourceName: string | "unknown"; |
| 33 | sourceLocation: string | "unknown"; |
| 34 | /** proxy/provider/host/vpn name */ |
| 35 | sourceVPN: string | null; |
| 36 | sourceRisk?: number | null; |
| 37 | /** value of the "i am a robot" honeypot checkbox, null when not sent. absent before 2026-10-24 */ |
| 38 | robot?: string | null; |
| 39 | /** value of the hidden "email" honeypot input, null when not sent. absent before 2026-10-24 */ |
| 40 | email?: string | null; |
| 41 | }; |
| 42 | |
| 43 | function toQuestion(q: typeof dbQuestions.$inferSelect) { |
| 44 | const date = new Date(q.qmid * 1000 * 60); |
| 45 | return { |
| 46 | /** "https://paperclover.net/q+a/${id}" - #YYMMDDHHMM */ |
| 47 | id: formatQuestionId(date), |
| 48 | ...q, |
| 49 | date, |
| 50 | }; |
| 51 | } |
| 52 | |
| 53 | const toQmid = (date: Date) => Math.floor(date.getTime() / 1000 / 60); |
| 54 | |
| 55 | export function getAll() { |
| 56 | return db.select().from(dbQuestions) |
| 57 | .where(eq(dbQuestions.type, "normal")) |
| 58 | .orderBy(desc(dbQuestions.qmid)) |
| 59 | .all().map(toQuestion); |
| 60 | } |
| 61 | |
| 62 | /** what is in the inbox, with each question's payload beside it */ |
| 63 | export function getAllPending() { |
| 64 | return db.select().from(dbQuestions) |
| 65 | .where(eq(dbQuestions.type, "pending")) |
| 66 | .orderBy(desc(dbQuestions.qmid)) |
| 67 | .all().map((q) => ({ ...JSON.parse(q.text) as PendingData, ...toQuestion(q) })); |
| 68 | } |
| 69 | |
| 70 | export function getByDate(timestamp: Date) { |
| 71 | // Older questions are inserted with second precision, |
| 72 | // but that data should always be floored. |
| 73 | const q = db.select().from(dbQuestions).where(eq(dbQuestions.qmid, toQmid(timestamp))).get(); |
| 74 | return q ? toQuestion(q) : null; |
| 75 | } |
| 76 | |
| 77 | /** Create a new Question, rounding the date to the nearest available slot */ |
| 78 | export function create(type: Type, text: string, date = new Date()) { |
| 79 | const { qmid } = db.get<{ qmid: number }>(sql` |
| 80 | with recursive timestamp(value) as ( |
| 81 | select ${toQmid(date)} |
| 82 | union all |
| 83 | select value + 1 |
| 84 | from timestamp |
| 85 | where exists (select 1 from questions where qmid = value) |
| 86 | ) |
| 87 | insert into questions (type, text, qmid) |
| 88 | values (${types[type]}, ${text}, ( |
| 89 | select value from timestamp |
| 90 | where not exists (select 1 from questions where qmid = value) |
| 91 | limit 1 |
| 92 | )) |
| 93 | returning qmid |
| 94 | `); |
| 95 | return new Date(qmid * 1000 * 60); |
| 96 | } |
| 97 | |
| 98 | export function deleteByQmid(qmid: number) { |
| 99 | db.delete(dbQuestions).where(eq(dbQuestions.qmid, qmid)).run(); |
| 100 | } |
| 101 | |
| 102 | export function rejectByQmid(qmid: number) { |
| 103 | db.update(dbQuestions).set({ type: "reject" }).where(eq(dbQuestions.qmid, qmid)).run(); |
| 104 | } |
| 105 | |
| 106 | export function updateByQmid(qmid: number, text: string, type: Type) { |
| 107 | db.update(dbQuestions).set({ text, type }).where(eq(dbQuestions.qmid, qmid)).run(); |
| 108 | } |
| 109 | |
| 110 | /** answered questions that link to `ref`, as `@ref` or by url */ |
| 111 | export function search(ref: string) { |
| 112 | const links = or( |
| 113 | like(dbQuestions.text, `%@${ref}%`), |
| 114 | like(dbQuestions.text, `%https://paperclover.net/${ref}%`), |
| 115 | ); |
| 116 | return db.select().from(dbQuestions) |
| 117 | .where(and(eq(dbQuestions.type, "normal"), links)) |
| 118 | .all().map(toQuestion); |
| 119 | } |
| 120 | |
| 121 | import { getDatabase, sqliteNumericEnum } from "#sitegen/drizzle"; |
| 122 | import { and, desc, eq, like, or, sql } from "drizzle-orm"; |
| 123 | import { integer, sqliteTable, text } from "drizzle-orm/sqlite-core"; |
| 124 | import { formatQuestionId } from "../format.ts"; |