1const db = getDatabase("questions.sqlite");
2
3/** how each type is stored */
4export const types = { normal: 0, reject: 1, pending: 3 } as const;
5export type Type = keyof typeof types;
6const dbQuestionType = sqliteNumericEnum(types);
7
8const 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});
18db.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
26export type Question = ReturnType<typeof toQuestion>;
27
28/** the payload of a question that has not been answered */
29export 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
43function 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
53const toQmid = (date: Date) => Math.floor(date.getTime() / 1000 / 60);
54
55export 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 */
63export 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
70export 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 */
78export 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
98export function deleteByQmid(qmid: number) {
99 db.delete(dbQuestions).where(eq(dbQuestions.qmid, qmid)).run();
100}
101
102export function rejectByQmid(qmid: number) {
103 db.update(dbQuestions).set({ type: "reject" }).where(eq(dbQuestions.qmid, qmid)).run();
104}
105
106export 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 */
111export 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
121import { getDatabase, sqliteNumericEnum } from "#sitegen/drizzle";
122import { and, desc, eq, like, or, sql } from "drizzle-orm";
123import { integer, sqliteTable, text } from "drizzle-orm/sqlite-core";
124import { formatQuestionId } from "../format.ts";