1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
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
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88

// db/schema.ts — local SQLite schema (Drizzle, better-sqlite3).
// Single-user, local-only. No users table — everything belongs to the one local user.

import { sql } from "drizzle-orm";
import {
  sqliteTable,
  text,
  integer,
  index,
} from "drizzle-orm/sqlite-core";

// A configured data source shown on the dashboard.
export const sources = sqliteTable("sources", {
  id: text("id").primaryKey(),
  kind: text("kind").notNull(),
  label: text("label").notNull(),
  enabled: integer("enabled", { mode: "boolean" }).notNull().default(true),
  config: text("config", { mode: "json" }).notNull().default(sql`'{}'`),
  refreshSeconds: integer("refresh_seconds").notNull().default(900),
  position: integer("position").notNull().default(0),
  size: text("size").notNull().default("md"), // vestigial; layout now uses cols/rows
  cols: integer("cols").notNull().default(1), // grid column span (free w×h bento)
  rows: integer("rows").notNull().default(2), // grid row span
});

// Cached fetch result per source.
export const snapshots = sqliteTable(
  "snapshots",
  {
    id: text("id").primaryKey(),
    sourceId: text("source_id")
      .notNull()
      .references(() => sources.id, { onDelete: "cascade" }),
    payload: text("payload", { mode: "json" }),
    fetchedAt: text("fetched_at").notNull(),
    ok: integer("ok", { mode: "boolean" }).notNull().default(true),
    error: text("error"),
  },
  (t) => ({
    sourceIdx: index("snapshots_source_idx").on(t.sourceId),
    fetchedIdx: index("snapshots_fetched_idx").on(t.fetchedAt),
  }),
);

// Local todos (this app owns them).
export const todos = sqliteTable(
  "todos",
  {
    id: text("id").primaryKey(),
    title: text("title").notNull(),
    done: integer("done", { mode: "boolean" }).notNull().default(false),
    due: text("due"),
    createdAt: text("created_at").notNull(),
  },
  (t) => ({
    createdIdx: index("todos_created_idx").on(t.createdAt),
  }),
);

// Generated daily PDF "newspaper" editions.
export const editions = sqliteTable(
  "editions",
  {
    id: text("id").primaryKey(),
    date: text("date").notNull(),
    pdfPath: text("pdf_path").notNull(),
    generatedAt: text("generated_at").notNull(),
  },
  (t) => ({
    dateIdx: index("editions_date_idx").on(t.date),
  }),
);

// App settings as key/value JSON rows.
export const settings = sqliteTable("settings", {
  key: text("key").primaryKey(),
  value: text("value", { mode: "json" }),
});

export type SourceRow = typeof sources.$inferSelect;
export type NewSourceRow = typeof sources.$inferInsert;
export type SnapshotRow = typeof snapshots.$inferSelect;
export type NewSnapshotRow = typeof snapshots.$inferInsert;
export type TodoRow = typeof todos.$inferSelect;
export type NewTodoRow = typeof todos.$inferInsert;
export type EditionRow = typeof editions.$inferSelect;
export type NewEditionRow = typeof editions.$inferInsert;
export type SettingRow = typeof settings.$inferSelect;