SQLite cache bootstrap + single-issue mirror upsert (#3)

A rebuildable local mirror of the reconciled backlog — an index over gitea's
durable truth, never the source of truth (D4). This lands the core seam:

- `cache/cache-v0.ts`: the 5-table schema (issues/labels/milestones/comments/
  issue_events), a `CacheDriver` injected-IO interface (exec/run/get/all), and
  pure row<->domain mappers. `upsertIssue` mirrors one issue (+ its milestone),
  upsert-by-number so a re-reconcile updates in place; `readIssue` re-derives
  `facts` from the stored label set via extractLabelFacts, so the mirror can't
  drift from the label semantics. Facts are never stored.
- Resolves #3's open scope: pure mappers + SQL in core (driver-agnostic), native
  driver in main. Tests bind node:sqlite (present in the Node 24 test runtime) to
  the same CacheDriver seam better-sqlite3 will fill in main.

Acceptance met: upsert one reconciled issue, read back, assert
estimateDays/priority/hardDeadline (+ upsert-in-place, no-milestone, and miss
cases). Core suite green; typecheck clean.

Follow-up (noted in the plan): the main-process better-sqlite3 adapter +
snapshot-store migration — packaging-sensitive (native module), kept out of this
slice so the shippable .dmg stays verified.

Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>

apps/desktop/src/renderer/src/components/screens/calibration-screen.tsx

@@ -178,6 +178,9 @@ export function CalibrationScreen({ onBack, data }: { onBack: () => void; data?:
               {c.active
                 ? 'You are not bad at estimating; you are optimistic in a very stable way. Stable, I can work with.'
                 : 'Not enough closed history yet — I’m forecasting from cold-start priors and widening the cone to stay honest. The curve takes over at 20.'}
+              {!c.active && c.excludedSameDay > 0
+                ? ` And ${c.excludedSameDay} closed ${c.excludedSameDay === 1 ? 'issue' : 'issues'} closed the same day they were started — 0 working days can’t calibrate, so they don’t count toward the 20.`
+                : ''}
             </p>
           </Card>
         </div>

apps/desktop/src/renderer/src/components/screens/runway-screen.tsx

@@ -17,7 +17,7 @@ export function RunwayScreen({
 }: {
   onOpenCalibration: () => void
   onOpenMilestone: (id?: number) => void
-  calibration?: { n: number; coldStart: boolean }
+  calibration?: { n: number; coldStart: boolean; excludedSameDay?: number }
   milestones?: RunwayMilestone[]
   capacity?: CapacityMember[]
 }) {
@@ -30,9 +30,11 @@ export function RunwayScreen({
           hours: `${capacityPerWorkday(m).toFixed(2)} pd/day`,
         }))
       : CAPACITY
+  const excluded = calibration?.excludedSameDay ?? 0
   const calibNote = calibration
     ? calibration.coldStart
-      ? `cold-start priors · ${calibration.n}/20 closed issues estimated`
+      ? `cold-start priors · ${calibration.n}/20 closed issues estimated` +
+        (excluded > 0 ? ` · ${excluded} same-day close${excluded === 1 ? '' : 's'} can’t calibrate` : '')
       : `calibrated on ${calibration.n} closed ${calibration.n === 1 ? 'issue' : 'issues'}`
     : 'calibrated on 27 closed issues'
   return (

apps/desktop/src/renderer/src/components/shell/app-shell.tsx

@@ -306,7 +306,15 @@ export function AppShell() {
               setMilestoneId(id ?? null)
               setView('milestone')
             }}
-            calibration={calibration ? { n: calibration.model.n, coldStart: calibration.model.coldStart } : undefined}
+            calibration={
+              calibration
+                ? {
+                    n: calibration.model.n,
+                    coldStart: calibration.model.coldStart,
+                    excludedSameDay: calibration.coverage.excludedSameDay,
+                  }
+                : undefined
+            }
             milestones={runwayMilestones}
             capacity={capacityMembers}
           />

apps/desktop/src/renderer/src/data/fixtures.ts

@@ -361,11 +361,14 @@ export interface CalibrationData {
   scatter: number[][]
   fit: number
   effect: { raw: string; banded: string; p50: string }
+  /** Closed+estimated issues that can't calibrate (same-day / 0-day closes). */
+  excludedSameDay: number
 }
 
 export const CALIBRATION: CalibrationData = {
   n: 27,
   active: true,
+  excludedSameDay: 0,
   labels: [
     { label: 'est/1d', n: 8, median: '1.1d', bias: 8 },
     { label: 'est/2d', n: 9, median: '2.4d', bias: 18 },

apps/desktop/src/renderer/src/lib/backlog.ts

@@ -1,6 +1,8 @@
 import {
+  type CalibrationCoverage,
   type CalibrationModel,
   type CalibrationSample,
+  calibrationCoverage,
   calibrationSamples,
   type CapacityMember,
   capacityPerWorkday,
@@ -171,10 +173,11 @@ export function backlogCalibration(
   issues: GiteaIssue[],
   timelines: Timelines = {},
   asOf: Date = new Date(),
-): { model: CalibrationModel; data: CalibrationData } {
+): { model: CalibrationModel; data: CalibrationData; coverage: CalibrationCoverage } {
   const samples = calibrationSamples(issues, timelines, asOf)
   const model = fitCalibration(samples)
-  return { model, data: calibrationData(model, samples, issues) }
+  const coverage = calibrationCoverage(issues, timelines, asOf)
+  return { model, coverage, data: calibrationData(model, samples, issues, coverage.excludedSameDay) }
 }
 
 const pctFromMu = (mu: number) => Math.round((Math.exp(mu) - 1) * 100)
@@ -188,6 +191,7 @@ export function calibrationData(
   model: CalibrationModel,
   samples: CalibrationSample[],
   openIssues: GiteaIssue[],
+  excludedSameDay = 0,
 ): CalibrationData {
   const labels = PRIOR_BUCKETS.map((b) => {
     const inBucket = samples.filter((s) => s.bucket === b)
@@ -227,6 +231,7 @@ export function calibrationData(
     scatter: samples.map((s) => [s.estimateDays, s.actualWorkingDays]),
     fit: Number(Math.exp(model.global.mu).toFixed(2)),
     effect,
+    excludedSameDay,
   }
 }
 

packages/core/src/cache/cache-v0.test.ts

@@ -0,0 +1,92 @@
+import { DatabaseSync } from 'node:sqlite'
+
+import { describe, expect, it } from 'vitest'
+
+import type { GiteaIssue } from '../gitea/types.js'
+import { extractLabelFacts } from '../labels/label-schema.js'
+import { type CacheDriver, initCache, readIssue, upsertIssue } from './cache-v0.js'
+
+/** Adapt node:sqlite's DatabaseSync to the CacheDriver seam (main uses better-sqlite3). */
+function memoryDriver(): CacheDriver {
+  const db = new DatabaseSync(':memory:')
+  return {
+    exec: (sql) => db.exec(sql),
+    run: (sql, params = []) => {
+      db.prepare(sql).run(...(params as never[]))
+    },
+    get: (sql, params = []) => db.prepare(sql).get(...(params as never[])) as Record<string, unknown> | undefined,
+    all: (sql, params = []) => db.prepare(sql).all(...(params as never[])) as Record<string, unknown>[],
+  }
+}
+
+function issue(over: Partial<GiteaIssue> = {}): GiteaIssue {
+  const labels = over.labels ?? ['est/5d', 'p/1', 'deadline/hard']
+  return {
+    number: 42,
+    title: 'Monte Carlo engine',
+    body: 'percentile bands',
+    state: 'open',
+    labels,
+    facts: extractLabelFacts(labels),
+    milestone: { id: 7, title: 'P2 — Scheduler', dueOn: '2026-09-01T00:00:00Z' },
+    assignee: 'christian',
+    assignees: ['christian'],
+    createdAt: '2026-07-08T00:00:00Z',
+    updatedAt: '2026-07-08T01:00:00Z',
+    closedAt: null,
+    url: 'https://gitea/christian/commitea/issues/42',
+    ...over,
+  }
+}
+
+describe('cache-v0', () => {
+  it('mirrors one issue and reads its facts back through extractLabelFacts (acceptance)', () => {
+    const d = memoryDriver()
+    initCache(d)
+    upsertIssue(d, issue({ labels: ['est/5d', 'p/1', 'deadline/hard'] }))
+
+    const back = readIssue(d, 42)!
+    expect(back.labels).toEqual(['est/5d', 'p/1', 'deadline/hard'])
+    // facts are re-derived on read, not stored
+    expect(back.facts.estimateDays).toBe(5)
+    expect(back.facts.priority).toBe(1)
+    expect(back.facts.hardDeadline).toBe(true)
+    // the rest of the domain shape round-trips
+    expect(back.milestone).toEqual({ id: 7, title: 'P2 — Scheduler', dueOn: '2026-09-01T00:00:00Z' })
+    expect(back.assignee).toBe('christian')
+    expect(back.state).toBe('open')
+  })
+
+  it('re-derives facts from the current labels after a re-reconcile (upsert in place, no dup)', () => {
+    const d = memoryDriver()
+    initCache(d)
+    upsertIssue(d, issue({ labels: ['est/2d', 'p/3'] }))
+    // reconcile again with changed labels + closed
+    upsertIssue(d, issue({ labels: ['est/8d', 'p/1'], state: 'closed', closedAt: '2026-07-09T00:00:00Z' }))
+
+    expect(d.all('SELECT number FROM issues')).toHaveLength(1) // upsert by number, not a second row
+    const back = readIssue(d, 42)!
+    expect(back.facts.estimateDays).toBe(8)
+    expect(back.facts.priority).toBe(1)
+    expect(back.facts.hardDeadline).toBe(false) // deadline/hard dropped
+    expect(back.state).toBe('closed')
+    expect(back.closedAt).toBe('2026-07-09T00:00:00Z')
+  })
+
+  it('reads an issue with no milestone / empty labels', () => {
+    const d = memoryDriver()
+    initCache(d)
+    upsertIssue(d, issue({ number: 9, labels: [], milestone: null, assignee: null, assignees: [] }))
+    const back = readIssue(d, 9)!
+    expect(back.milestone).toBeNull()
+    expect(back.labels).toEqual([])
+    expect(back.facts.estimateDays).toBeNull()
+    expect(back.assignee).toBeNull()
+  })
+
+  it('returns null for an uncached issue', () => {
+    const d = memoryDriver()
+    initCache(d)
+    expect(readIssue(d, 999)).toBeNull()
+  })
+})

packages/core/src/cache/cache-v0.ts

@@ -0,0 +1,159 @@
+/**
+ * SQLite cache, v0 (#3) — a rebuildable local mirror of the reconciled backlog.
+ * It is an index over the durable truth in gitea, never the source of truth (D4):
+ * delete it, resync, lose nothing. This module owns the schema + the pure
+ * row<->domain mappers; the actual SQLite handle is injected as a `CacheDriver`,
+ * so core stays free of any native driver (better-sqlite3 lives in main; tests
+ * use node:sqlite). Facts are never stored — they are re-derived from the label
+ * set on read via `extractLabelFacts`, so the mirror can't drift from the label
+ * semantics.
+ */
+
+import type { GiteaIssue, GiteaMilestoneRef } from '../gitea/types.js'
+import { extractLabelFacts } from '../labels/label-schema.js'
+
+/**
+ * The injected IO boundary: a thin synchronous SQL executor. Core writes the SQL;
+ * the host binds a real driver (better-sqlite3 in the desktop main process,
+ * node:sqlite in tests). Kept minimal on purpose — no ORM, no query builder.
+ */
+export interface CacheDriver {
+  /** Run one or more DDL/utility statements (no params, no result). */
+  exec(sql: string): void
+  /** Execute a single parameterized write. */
+  run(sql: string, params?: readonly unknown[]): void
+  /** First row of a parameterized query, or undefined. */
+  get(sql: string, params?: readonly unknown[]): Record<string, unknown> | undefined
+  /** All rows of a parameterized query. */
+  all(sql: string, params?: readonly unknown[]): Record<string, unknown>[]
+}
+
+/** The cache schema — five tables mirroring gitea's shape. Regenerable; drop and rebuild freely. */
+export const CACHE_SCHEMA = `
+CREATE TABLE IF NOT EXISTS milestones (
+  id       INTEGER PRIMARY KEY,
+  title    TEXT NOT NULL,
+  state    TEXT,
+  due_on   TEXT
+);
+CREATE TABLE IF NOT EXISTS issues (
+  number       INTEGER PRIMARY KEY,
+  title        TEXT NOT NULL,
+  body         TEXT NOT NULL DEFAULT '',
+  state        TEXT NOT NULL,
+  labels       TEXT NOT NULL DEFAULT '[]',  -- JSON array of label names; facts re-derived on read
+  milestone_id INTEGER,
+  assignee     TEXT,
+  assignees    TEXT NOT NULL DEFAULT '[]',  -- JSON array of logins
+  created_at   TEXT,
+  updated_at   TEXT,
+  closed_at    TEXT,
+  url          TEXT,
+  FOREIGN KEY (milestone_id) REFERENCES milestones(id)
+);
+CREATE TABLE IF NOT EXISTS labels (
+  id    INTEGER PRIMARY KEY,
+  name  TEXT NOT NULL
+);
+CREATE TABLE IF NOT EXISTS comments (
+  id            INTEGER PRIMARY KEY,
+  issue_number  INTEGER NOT NULL,
+  author        TEXT,
+  body          TEXT NOT NULL DEFAULT '',
+  created_at    TEXT
+);
+CREATE TABLE IF NOT EXISTS issue_events (
+  id            INTEGER PRIMARY KEY AUTOINCREMENT,
+  issue_number  INTEGER NOT NULL,
+  type          TEXT NOT NULL,
+  at            TEXT NOT NULL
+);
+CREATE INDEX IF NOT EXISTS idx_issue_events_number ON issue_events(issue_number);
+CREATE INDEX IF NOT EXISTS idx_comments_number ON comments(issue_number);
+`
+
+/** Create the schema if absent. Idempotent. */
+export function initCache(driver: CacheDriver): void {
+  driver.exec(CACHE_SCHEMA)
+}
+
+const UPSERT_ISSUE = `
+INSERT INTO issues (number, title, body, state, labels, milestone_id, assignee, assignees, created_at, updated_at, closed_at, url)
+VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
+ON CONFLICT(number) DO UPDATE SET
+  title = excluded.title, body = excluded.body, state = excluded.state, labels = excluded.labels,
+  milestone_id = excluded.milestone_id, assignee = excluded.assignee, assignees = excluded.assignees,
+  created_at = excluded.created_at, updated_at = excluded.updated_at, closed_at = excluded.closed_at, url = excluded.url
+`
+
+const UPSERT_MILESTONE = `
+INSERT INTO milestones (id, title, state, due_on) VALUES (?, ?, ?, ?)
+ON CONFLICT(id) DO UPDATE SET title = excluded.title, state = excluded.state, due_on = excluded.due_on
+`
+
+/**
+ * Mirror one reconciled issue into the cache (and its milestone, if any). Upsert
+ * by `number`, so re-reconciling the same issue updates in place — never duplicates.
+ */
+export function upsertIssue(driver: CacheDriver, issue: GiteaIssue): void {
+  if (issue.milestone) {
+    driver.run(UPSERT_MILESTONE, [issue.milestone.id, issue.milestone.title, null, issue.milestone.dueOn])
+  }
+  driver.run(UPSERT_ISSUE, [
+    issue.number,
+    issue.title,
+    issue.body,
+    issue.state,
+    JSON.stringify(issue.labels),
+    issue.milestone?.id ?? null,
+    issue.assignee,
+    JSON.stringify(issue.assignees),
+    issue.createdAt,
+    issue.updatedAt,
+    issue.closedAt,
+    issue.url,
+  ])
+}
+
+const READ_ISSUE = `
+SELECT i.*, m.title AS m_title, m.due_on AS m_due
+FROM issues i LEFT JOIN milestones m ON m.id = i.milestone_id
+WHERE i.number = ?
+`
+
+function str(v: unknown): string {
+  return typeof v === 'string' ? v : ''
+}
+
+/**
+ * Read one mirrored issue back as a domain object, re-deriving `facts` from the
+ * stored label set (so the mirror can't disagree with the label semantics).
+ * Returns null when the issue isn't cached.
+ */
+export function readIssue(driver: CacheDriver, number: number): GiteaIssue | null {
+  const row = driver.get(READ_ISSUE, [number])
+  if (!row) return null
+
+  const labels = (JSON.parse(str(row.labels) || '[]') as string[]) ?? []
+  const assignees = (JSON.parse(str(row.assignees) || '[]') as string[]) ?? []
+  const milestone: GiteaMilestoneRef | null =
+    row.milestone_id != null
+      ? { id: Number(row.milestone_id), title: str(row.m_title), dueOn: (row.m_due as string | null) ?? null }
+      : null
+
+  return {
+    number: Number(row.number),
+    title: str(row.title),
+    body: str(row.body),
+    state: row.state === 'closed' ? 'closed' : 'open',
+    labels,
+    facts: extractLabelFacts(labels),
+    milestone,
+    assignee: (row.assignee as string | null) ?? null,
+    assignees,
+    createdAt: str(row.created_at),
+    updatedAt: str(row.updated_at),
+    closedAt: (row.closed_at as string | null) ?? null,
+    url: str(row.url),
+  }
+}

packages/core/src/calibration/calibration-v0.test.ts

@@ -5,6 +5,7 @@ import type { LifecycleEvent } from '../lifecycle/lifecycle-v0.js'
 import type { GiteaIssue } from '../gitea/types.js'
 import {
   CALIBRATION_BUCKET_FLOOR,
+  calibrationCoverage,
   calibrationSamples,
   type CalibrationSample,
   COLD_START_THRESHOLD,
@@ -118,4 +119,26 @@ describe('calibrationSamples', () => {
     const noEst = issue({ number: 9, labels: [] })
     expect(calibrationSamples([open, noEst], { ...events(8), ...events(9) }, asOf)).toEqual([])
   })
+
+  it('coverage counts same-day closes as excluded candidates, not as "more closes needed"', () => {
+    // usable: commit Wed 01-07 → close Mon 01-12 = 3 working days
+    const usable = issue({ number: 7, labels: ['est/2d'] })
+    // same-day close: commit and close on the same day = 0 working days → excluded
+    const sameDay = issue({ number: 10, labels: ['est/2d'], createdAt: '2026-01-12T08:00:00Z' })
+    const sameDayEvents = {
+      10: [
+        { type: 'commit', at: '2026-01-12T09:00:00Z' } as LifecycleEvent,
+        { type: 'close', at: '2026-01-12T17:00:00Z' } as LifecycleEvent,
+      ],
+    }
+    const open = issue({ number: 8, state: 'open', labels: ['est/2d'], closedAt: null })
+    const noEst = issue({ number: 9, labels: [] })
+
+    const cov = calibrationCoverage([usable, sameDay, open, noEst], { ...events(7), ...sameDayEvents }, asOf)
+    expect(cov.candidates).toBe(2) // closed + estimated only (usable + sameDay)
+    expect(cov.usable).toBe(1)
+    expect(cov.excludedSameDay).toBe(1)
+    // the honest denominator: usable matches the model's n
+    expect(cov.usable).toBe(calibrationSamples([usable, sameDay, open, noEst], { ...events(7), ...sameDayEvents }, asOf).length)
+  })
 })

packages/core/src/calibration/calibration-v0.ts

@@ -125,3 +125,40 @@ export function calibrationSamples(
   }
   return out
 }
+
+/** How the closed+estimated backlog splits into usable samples vs. what can't calibrate. */
+export interface CalibrationCoverage {
+  /** Closed issues carrying an estimate — the calibration candidates. */
+  candidates: number
+  /** Candidates that yielded a usable actual (> 0 working days) → become samples. */
+  usable: number
+  /**
+   * Candidates excluded because the issue closed with 0 working days (same-day
+   * close) or no resolvable actual — real closes that structurally can't
+   * calibrate. Counting them keeps `usable/threshold` honest: it's not "N more
+   * closes away" if some of your closes will never count.
+   */
+  excludedSameDay: number
+}
+
+/**
+ * Coverage of the calibration candidates — how many closed+estimated issues are
+ * usable vs. silently unusable (same-day / 0-day closes). {@link calibrationSamples}
+ * drops the latter; this counts them so the UI can say *why* the sample is thin.
+ */
+export function calibrationCoverage(
+  issues: GiteaIssue[],
+  timelines: Record<number, LifecycleEvent[]>,
+  asOf: Date,
+): CalibrationCoverage {
+  let candidates = 0
+  let usable = 0
+  for (const issue of issues) {
+    if (issue.state !== 'closed') continue
+    if (issue.facts.estimateDays == null) continue
+    candidates++
+    const inf = inferLifecycle(issue, timelines[issue.number] ?? [], asOf)
+    if (inf.actualWorkingDays != null && inf.actualWorkingDays > 0) usable++
+  }
+  return { candidates, usable, excludedSameDay: candidates - usable }
+}

packages/core/src/index.ts

@@ -23,6 +23,8 @@ export type {
   GiteaRequestInit,
 } from './gitea/types.js'
 
+export { CACHE_SCHEMA, initCache, readIssue, upsertIssue } from './cache/cache-v0.js'
+export type { CacheDriver } from './cache/cache-v0.js'
 export { describeChange, isLabelChange, planIssueChange, proposalsFor, summarizeChange } from './changes/apply-changes-v0.js'
 export type {
   ChangeProposal,
@@ -80,6 +82,7 @@ export type {
 
 export {
   CALIBRATION_BUCKET_FLOOR,
+  calibrationCoverage,
   calibrationSamples,
   COLD_START_THRESHOLD,
   fitCalibration,
@@ -87,6 +90,7 @@ export {
 } from './calibration/calibration-v0.js'
 export type {
   BucketFit,
+  CalibrationCoverage,
   CalibrationModel,
   CalibrationSample,
   PersonBias,