add the one-time Kepr 2 import tool, writing put-if-absent and checked by the sync's own release validator, with a local-Postgres test, and record phase 8 in continuity (v3.0.43)

@@ -0,0 +1,98 @@1+// One-time Kepr 2 import (§28), deleted after cutover; the only bucket writer. Env: KEPR2_DATABASE_URL,2+// KEPR2_DATA, KEPR_DATA, KOH_FOLDER or KOH_S3_* (write key at run time only). --dry-run writes nothing.3+import { join } from "node:path";4+import { blake3 } from "@noble/hashes/blake3.js";5+import { bytesToHex } from "@noble/hashes/utils.js";6+import { SQL } from "bun";7+import { openBucket } from "../src/bucket/reader.ts";8+import { checkRelease } from "../src/bucket/repo.ts";9+import { compareVersions } from "../src/releases/releases.ts";10+import { openKeprDb } from "../src/server/db.ts";11+12+type Row = Record<string, string | number | null>;13+const env = (name: string) => process.env[name] ?? "";14+const dry = process.argv.includes("--dry-run");15+const pg = new SQL(env("KEPR2_DATABASE_URL"));16+const kepr = openKeprDb(env("KEPR_DATA"));17+const folder = env("KOH_FOLDER");18+const VARS = "ENDPOINT BUCKET REGION ACCESS_KEY_ID SECRET_ACCESS_KEY".split(" ");19+const [endpoint, name, region, accessKeyId, secretAccessKey] = VARS.map((v) => env(`KOH_S3_${v}`));20+const s3 = { endpoint, region, accessKeyId, secretAccessKey };21+const bucket = openBucket(folder ? { kind: "folder", path: folder } : { kind: "s3", name, ...s3 });22+/** put_if_absent (§9.1): true when `key` holds `bytes` after; a key holding other bytes is kept. */23+async function put(key: string, body: string | Uint8Array): Promise<boolean> {24+	const bytes = typeof body === "string" ? new TextEncoder().encode(body) : body;25+	const had = await bucket.get(key);26+	const same = had === null || Bun.deepEquals(had, bytes);27+	console.log(had === null ? (dry ? "would put" : "put") : same ? "have" : "differs, kept:", key);28+	if (had !== null || dry) return same;29+	if (folder) return Bun.write(join(folder, key), bytes).then(() => true);30+	// Bun's S3 client has no conditional write, so a presigned PUT carries it.31+	const url = new Bun.S3Client({ ...s3, bucket: name }).presign(key, { method: "PUT" });32+	const res = await fetch(url, { method: "PUT", body: bytes, headers: { "If-None-Match": "*" } });33+	if (!res.ok) throw new Error(`put ${key}: ${res.status}`);34+	return true;35+}36+function insert(table: string, row: Row, tail: string): number | null {37+	const cols = Object.keys(row);38+	const sql = `INSERT INTO ${table} (${cols}) VALUES (${cols.map((c) => `$${c}`)}) ${tail} RETURNING rowid AS id`;39+	const id = kepr.query<{ id: number }, [Row]>(sql).get(row)?.id ?? null;40+	console.log(table, JSON.stringify(row).slice(0, 80), id ? "set" : "have");41+	return id;42+}43+kepr.run("BEGIN");44+// Kepr 3 keeps one category and has no description field; Kepr 2 had no homepage.45+const upsert = "ON CONFLICT DO UPDATE SET public = excluded.public, category = excluded.category";46+for (const repo of await pg<Row[]>`47+	SELECT name, (private = 0)::int AS public, trim(split_part(COALESCE(categories, ''), ',', 1)) AS category FROM repos`)48+	insert("repos", repo, upsert);49+// Kepr 2's four statuses become two (Phase 5); "enhancement" is an idea. Any account's reply but the50+// reporter's own is the operator's, since §23's thread has no other author.51+for (const { replies, ...issue } of await pg<(Row & { replies: Row[] })[]>`52+	SELECT i.repo, i.number, i.title, i.description AS body, CASE WHEN i.type IN ('bug', 'question') THEN i.type ELSE 'idea' END AS type,53+		COALESCE(i.filed_ver, '') AS version, COALESCE(NULLIF(i.reporter_name, ''), '@' || a.username, '') AS reporter_name,54+		COALESCE(NULLIF(i.contact_email, ''), i.contact, '') AS contact_email, i.filed_at,55+		CASE WHEN i.status IN ('resolved', 'wontfix', 'closed') THEN 'closed' ELSE 'open' END AS status,56+		CASE WHEN i.status IN ('resolved', 'wontfix', 'closed') THEN COALESCE(i.closed_at, i.filed_at) END AS closed_at,57+		CASE WHEN i.closed_save ~ '^[0-9a-f]{12}$' THEN i.closed_save ELSE '' END AS fixed_in,58+		(SELECT COALESCE(json_agg(json_build_object('author', CASE WHEN e.account_id = i.account_id THEN 'reporter' ELSE 'operator' END,59+			'body', e.body, 'created_at', e.created_at) ORDER BY e.created_at, e.id), '[]') FROM issue_entries e WHERE e.issue_id = i.id AND e.entry_type = 'reply' AND e.body IS NOT NULL) AS replies60+	FROM issues i LEFT JOIN accounts a ON a.id = i.account_id ORDER BY i.repo, i.number`) {61+	const id = insert("issues", issue, "ON CONFLICT DO NOTHING");62+	for (const reply of id ? replies : []) insert("issue_entries", { issue_id: id, ...reply }, "");63+}64+// Kepr 2 counted Nix fetches as installs (kept 90 days) and never counted release downloads.65+for (const count of await pg<Row[]>`66+	SELECT repo_id AS repo, COUNT(*) AS count FROM engagement_events WHERE event_type = 'nix_install' AND repo_id IS NOT NULL GROUP BY repo_id`)67+	insert("installs", count, "ON CONFLICT DO NOTHING");68+// As Kepr 2 served: successful and partial builds, a file stored under the repo's name as <repo>-<platform>,69+// older uploads' files named by platform; builds off hid a repo's. Save, notes, time: the newest build's.70+type Release = { repo: string; version: string; hidden: boolean; record: Row; stored: string[] };71+const newest = new Map<string, string>();72+for (const { repo, version, hidden, record, stored } of await pg<Release[]>`73+	SELECT a.repo_id AS repo, a.version, bool_or(r.builds_enabled = 0) AS hidden, json_agg(a.filename ORDER BY a.platform) AS stored,74+		json_build_object('format', 1, 'repo', a.repo_id, 'version', a.version, 'save', (array_agg(b.save_id ORDER BY b.started_at DESC))[1],75+			'published_at', max(COALESCE(b.finished_at, a.built_at)), 'machine', 'kepr2', 'notes', COALESCE((array_agg(b.release_notes ORDER BY b.started_at DESC))[1], ''),76+			'files', json_agg(json_build_object('platform', a.platform, 'name', CASE WHEN a.filename = a.repo_id THEN a.repo_id || '-' || a.platform ELSE a.filename END,77+				'size', a.size_bytes, 'blake3', a.blake3, 'sha256', a.sha256) ORDER BY a.platform)) AS record78+	FROM release_artifacts a JOIN build_runs b ON b.id = a.build_run JOIN repos r ON r.name = a.repo_id WHERE b.status IN ('success', 'partial') GROUP BY a.repo_id, a.version ORDER BY a.repo_id, a.version`) {79+	const prefix = `${repo}/releases/${version}`;80+	try {81+		for (const [n, f] of checkRelease(repo, version, record).files.entries()) {82+			const dir = join(env("KEPR2_DATA"), "releases", repo, version, f.platform);83+			const path = join(dir, stored[n] ?? "");84+			const bytes = await Bun.file((await Bun.file(path).exists()) ? path : dir).bytes();85+			const kepr2s = bytesToHex(blake3(bytes)) === f.blake3;86+			if (!kepr2s || !(await put(`${prefix}/${f.platform}/${f.name}`, bytes)))87+				throw new Error(`${f.platform}: not Kepr 2's file, or another is there`);88+		}89+		if (!(await put(`${prefix}/release.json`, JSON.stringify(record)))) continue;90+		if (hidden) insert("release_settings", { repo, version, hidden: 1 }, "ON CONFLICT DO NOTHING");91+		if (compareVersions(version, newest.get(repo) ?? "") > 0) newest.set(repo, version);92+	} catch (error) {93+		console.log(`skip ${prefix}:`, error instanceof Error ? error.message : error);94+	}95+}96+for (const [repo, version] of newest) await put(`${repo}/releases/latest`, version);97+kepr.run(dry ? "ROLLBACK" : "COMMIT");98+await pg.close();

@@ -0,0 +1,715 @@1+-- Kepr Postgres schema — version 1.2+--3+-- A faithful translation of the cumulative SQLite schema (migrations 1..39,4+-- as materialised by db.applyAll) into idiomatic Postgres. It is NOT derived5+-- from prose: it was produced from `.schema` of a database with all 396+-- migrations applied, so every column, constraint, CHECK, and index matches7+-- what the live SQLite database actually holds.8+--9+-- Translation rules (chosen for an exact, value-preserving migration):10+--   * SQLite INTEGER  -> BIGINT          (SQLite ints are 64-bit; size_bytes11+--                                          and byte counts exceed int32)12+--   * SQLite REAL     -> DOUBLE PRECISION13+--   * SQLite TEXT     -> TEXT14+--   * Integer 0/1 booleans are KEPT as BIGINT 0/1 — not converted to BOOLEAN —15+--     so the migration is a verbatim value copy and db.zig reads them exactly16+--     as it does today (i64, != 0 == true).17+--   * INTEGER PRIMARY KEY AUTOINCREMENT -> BIGINT GENERATED BY DEFAULT AS18+--     IDENTITY (BY DEFAULT, not ALWAYS, so the migration tool can insert the19+--     existing ids; the sequence is advanced past MAX(id) after bulk load).20+--   * All timestamps remain Unix epoch seconds (BIGINT), unconverted.21+--22+-- Foreign keys are declared as in SQLite. SQLite did not enforce them at23+-- runtime, so the one-time migration tool loads with24+-- `SET session_replication_role = replica` to bypass FK/trigger checks, then25+-- the constraints guard all subsequently-written data.26+27+BEGIN;28+29+CREATE TABLE schema_version (30+    version INTEGER NOT NULL31+);32+INSERT INTO schema_version (version) VALUES (1);33+34+-- ── Repositories and saves ───────────────────────────────────────────35+36+CREATE TABLE repos (37+    name               TEXT PRIMARY KEY,38+    created_at         BIGINT NOT NULL,39+    description        TEXT,40+    languages          TEXT DEFAULT '',41+    details            TEXT,42+    categories         TEXT DEFAULT '',43+    count_extensions   TEXT DEFAULT '',44+    contributor_handle TEXT,45+    issues_enabled     BIGINT NOT NULL DEFAULT 1,46+    languages_cloud    TEXT,47+    project_id         TEXT,48+    trusted            BIGINT NOT NULL DEFAULT 0,49+    trust_computed_at  BIGINT,50+    private            BIGINT NOT NULL DEFAULT 0,51+    builds_enabled     BIGINT NOT NULL DEFAULT 1,52+    display_languages  TEXT53+);54+55+CREATE TABLE saves (56+    id            TEXT NOT NULL,57+    repo          TEXT NOT NULL REFERENCES repos(name) ON UPDATE CASCADE,58+    lane          TEXT NOT NULL CHECK (lane IN ('main','dev')),59+    message       TEXT NOT NULL,60+    timestamp     BIGINT NOT NULL,61+    size_bytes    BIGINT NOT NULL,62+    parent_id     TEXT,63+    is_checkpoint BIGINT NOT NULL DEFAULT 0,64+    checksum      TEXT NOT NULL,65+    manifest_path TEXT NOT NULL,66+    file_count    BIGINT NOT NULL DEFAULT 0,67+    line_count    BIGINT NOT NULL DEFAULT 0,68+    session       TEXT,69+    agent         TEXT,70+    PRIMARY KEY (id, repo)71+);72+CREATE INDEX saves_repo_lane_ts ON saves(repo, lane, timestamp);73+74+CREATE TABLE object_refs (75+    hex_hash TEXT NOT NULL,76+    save_id  TEXT NOT NULL,77+    repo     TEXT NOT NULL,78+    PRIMARY KEY (hex_hash, save_id, repo)79+);80+CREATE INDEX object_refs_hash ON object_refs(hex_hash);81+82+-- Reserved advisory-lock id: Postgres advisory locks (pg_advisory_xact_lock /83+-- pg_advisory_xact_lock_shared) are a single flat bigint namespace per84+-- cluster, not scoped to any table, but the id kepr reserves here exists to85+-- protect this table specifically. storage GC's sweep (storage_admin.gc)86+-- holds it exclusive around deciding which objects/ files are unreferenced;87+-- every offer-complete transaction that promotes rows into object_refs88+-- (src/web/api.zig: handleComplete, acceptPending) takes it89+-- shared first, so a commit that references an object already on disk can90+-- never race a concurrent sweep into deleting that object (review F12,91+-- decision D3). Shared holders never block each other. See92+-- db_mod.STORAGE_GC_ADVISORY_LOCK_ID in src/db.zig for the actual id and the93+-- lockGcExclusive/lockGcShared helpers — if kepr ever reserves another94+-- advisory-lock id, pick a value nowhere near this one and document it here95+-- too.96+97+-- Content-addressed cache of a countable file's line count, keyed by its98+-- Blake3 hash. A hash's line count never changes, so once computed it never99+-- needs decompressing again on a later save that re-references the same100+-- unchanged file.101+CREATE TABLE object_line_counts (102+    hex_hash TEXT PRIMARY KEY,103+    lines    BIGINT NOT NULL104+);105+106+-- Operator-purged paths: the server-side equivalent of .kohignore (which kepr107+-- strips from manifests, so it cannot be edited server-side). A path here was108+-- stripped from every save's manifest by an operator; future offers that109+-- re-introduce it are filtered on ingest. Matches a file exactly or as a dir.110+CREATE TABLE repo_purged_paths (111+    repo_id   TEXT NOT NULL REFERENCES repos(name) ON UPDATE CASCADE,112+    path      TEXT NOT NULL,113+    purged_at BIGINT NOT NULL,114+    PRIMARY KEY (repo_id, path)115+);116+117+CREATE TABLE offer_tokens (118+    offer_id    TEXT PRIMARY KEY,119+    repo        TEXT NOT NULL,120+    manifest_id TEXT NOT NULL,121+    missing     TEXT NOT NULL,122+    manifest    TEXT NOT NULL,123+    key_type    TEXT NOT NULL CHECK (key_type IN ('operator','contributor')),124+    expires_at  BIGINT NOT NULL,125+    -- Account id that negotiated this token; complete must be performed by the126+    -- same account (§8.9). NULL only for tokens predating protocol 2.0.127+    negotiated_account TEXT128+);129+130+CREATE TABLE pending_offers (131+    offer_id      TEXT PRIMARY KEY,132+    repo          TEXT NOT NULL,133+    manifest_id   TEXT NOT NULL,134+    manifest      TEXT NOT NULL,135+    contributor   TEXT,136+    submitted_at  BIGINT NOT NULL,137+    status        TEXT NOT NULL DEFAULT 'pending'138+        CHECK (status IN ('draft','pending','accepted','declined')),139+    reviewed_at   BIGINT,140+    review_note   TEXT,141+    agent_verdict TEXT,142+    agent_status  TEXT DEFAULT 'pending'143+        CHECK (agent_status IN ('pending','running','complete','failed','unavailable')),144+    findings_json TEXT,145+    scan_status   TEXT CHECK (scan_status IN ('ok','failed')),146+    -- Contributor's human-readable note to the reviewer (separate from save147+    -- messages); 'draft' status holds an offer out of the review queue until148+    -- the contributor promotes it.149+    description   TEXT150+);151+152+CREATE TABLE pending_offer_identity_quarantine (153+    offer_id           TEXT PRIMARY KEY REFERENCES pending_offers(offer_id) ON DELETE CASCADE,154+    legacy_fingerprint TEXT NOT NULL,155+    reason             TEXT NOT NULL,156+    recorded_at        BIGINT NOT NULL DEFAULT EXTRACT(EPOCH FROM NOW())::BIGINT157+);158+CREATE INDEX pending_offer_identity_quarantine_fp159+    ON pending_offer_identity_quarantine(legacy_fingerprint);160+161+CREATE TABLE steals (162+    id        TEXT PRIMARY KEY,163+    repo      TEXT NOT NULL REFERENCES repos(name) ON UPDATE CASCADE,164+    timestamp BIGINT NOT NULL165+);166+CREATE INDEX steals_repo_ts ON steals(repo, timestamp);167+168+CREATE TABLE storage_snapshots (169+    date           TEXT PRIMARY KEY,170+    total_bytes    BIGINT NOT NULL,171+    object_count   BIGINT NOT NULL,172+    repo_count     BIGINT NOT NULL,173+    save_count     BIGINT NOT NULL,174+    languages      TEXT,175+    first_received BIGINT176+);177+178+CREATE TABLE instance_images (179+    filename     TEXT PRIMARY KEY,180+    created_at   BIGINT NOT NULL,181+    size_bytes   BIGINT NOT NULL,182+    checksum     TEXT NOT NULL,183+    repo_count   BIGINT NOT NULL,184+    save_count   BIGINT NOT NULL,185+    kepr_version TEXT NOT NULL186+);187+188+CREATE TABLE diff_cache (189+    repo        TEXT NOT NULL,190+    save_id     TEXT NOT NULL,191+    diff_json   TEXT NOT NULL,192+    computed_at BIGINT NOT NULL,193+    PRIMARY KEY (repo, save_id)194+);195+196+CREATE TABLE ops_credentials (197+    key   TEXT PRIMARY KEY,198+    value TEXT NOT NULL199+);200+201+-- ── Contributor handles ──────────────────────────────────────────────202+203+CREATE TABLE handles (204+    id            TEXT PRIMARY KEY,205+    handle        TEXT UNIQUE NOT NULL,206+    created_at    BIGINT NOT NULL,207+    recovery_hash TEXT NOT NULL208+);209+210+-- ── Assets, dependencies, builds, releases ───────────────────────────211+212+CREATE TABLE repo_assets (213+    id          TEXT PRIMARY KEY,214+    repo_id     TEXT NOT NULL REFERENCES repos(name) ON UPDATE CASCADE,215+    filename    TEXT NOT NULL,216+    mime_type   TEXT NOT NULL,217+    size_bytes  BIGINT NOT NULL,218+    uploaded_at BIGINT NOT NULL219+);220+221+CREATE TABLE repo_dependencies (222+    from_repo       TEXT NOT NULL REFERENCES repos(name) ON UPDATE CASCADE,223+    to_repo         TEXT NOT NULL REFERENCES repos(name) ON UPDATE CASCADE,224+    first_stolen_at BIGINT NOT NULL,225+    last_stolen_at  BIGINT NOT NULL,226+    PRIMARY KEY (from_repo, to_repo)227+);228+229+CREATE TABLE build_runs (230+    id               TEXT PRIMARY KEY,231+    repo_id          TEXT NOT NULL REFERENCES repos(name) ON UPDATE CASCADE,232+    save_id          TEXT NOT NULL,233+    version          TEXT NOT NULL,234+    trigger          TEXT NOT NULL,235+    status           TEXT NOT NULL,236+    started_at       BIGINT NOT NULL,237+    finished_at      BIGINT,238+    log              TEXT,239+    release_notes    TEXT,240+    platform_results TEXT NOT NULL DEFAULT '{}',241+    duration_ms      BIGINT,242+    lane             TEXT NOT NULL DEFAULT 'main'243+);244+245+CREATE TABLE release_artifacts (246+    id          TEXT PRIMARY KEY,247+    build_run   TEXT NOT NULL REFERENCES build_runs(id),248+    repo_id     TEXT NOT NULL REFERENCES repos(name) ON UPDATE CASCADE,249+    version     TEXT NOT NULL,250+    platform    TEXT NOT NULL,251+    filename    TEXT NOT NULL,252+    size_bytes  BIGINT NOT NULL,253+    sha256      TEXT NOT NULL,254+    blake3      TEXT NOT NULL,255+    signature   TEXT NOT NULL DEFAULT '',256+    built_at    BIGINT NOT NULL257+);258+CREATE UNIQUE INDEX release_artifacts_repo_version_platform259+    ON release_artifacts (repo_id, version, platform);260+261+-- ── Issues ───────────────────────────────────────────────────────────262+263+CREATE TABLE issues (264+    id            BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,265+    repo          TEXT    NOT NULL REFERENCES repos(name) ON UPDATE CASCADE,266+    title         TEXT    NOT NULL,267+    description   TEXT    NOT NULL,268+    contact       TEXT,269+    filed_at      BIGINT  NOT NULL,270+    filed_save    TEXT    NOT NULL,271+    filed_ver     TEXT,272+    status        TEXT    NOT NULL DEFAULT 'open',273+    closed_at     BIGINT,274+    closed_save   TEXT,275+    ip_hash       TEXT,276+    number        BIGINT  NOT NULL DEFAULT 0,277+    type          TEXT    NOT NULL DEFAULT 'bug',278+    severity      TEXT,279+    face_id       TEXT,280+    resolution_face TEXT,281+    account_id    TEXT,282+    contact_email TEXT,283+    updated_at    BIGINT  NOT NULL DEFAULT 0,284+    reporter_name TEXT285+);286+CREATE UNIQUE INDEX issues_repo_number ON issues(repo, number);287+CREATE INDEX issues_repo_status_created288+    ON issues(repo, status, filed_at DESC);289+CREATE INDEX issues_repo_type_status_created290+    ON issues(repo, type, status, filed_at DESC);291+292+CREATE TABLE issue_notes (293+    id        BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,294+    issue_id  BIGINT  NOT NULL REFERENCES issues(id),295+    body      TEXT    NOT NULL,296+    posted_at BIGINT  NOT NULL,297+    is_op     BIGINT  NOT NULL DEFAULT 0298+);299+300+CREATE TABLE issue_entries (301+    id         TEXT PRIMARY KEY,302+    issue_id   BIGINT NOT NULL REFERENCES issues(id) ON DELETE CASCADE,303+    account_id TEXT,304+    entry_type TEXT NOT NULL CHECK (entry_type IN ('reply','status_change','face_linked')),305+    body       TEXT,306+    created_at BIGINT NOT NULL307+);308+CREATE INDEX issue_entries_issue ON issue_entries(issue_id, created_at);309+310+CREATE TABLE issue_attachments (311+    id          TEXT PRIMARY KEY,312+    issue_id    BIGINT REFERENCES issues(id) ON DELETE CASCADE,313+    repo_id     TEXT NOT NULL REFERENCES repos(name) ON UPDATE CASCADE,314+    filename    TEXT NOT NULL,315+    mime_type   TEXT NOT NULL,316+    size_bytes  BIGINT NOT NULL,317+    path        TEXT NOT NULL,318+    tmp_token   TEXT,319+    tmp_expires BIGINT,320+    created_at  BIGINT NOT NULL321+);322+CREATE INDEX issue_attachments_issue ON issue_attachments(issue_id, created_at);323+CREATE INDEX issue_attachments_repo ON issue_attachments(repo_id, created_at);324+325+-- ── Caches and challenges ────────────────────────────────────────────326+327+CREATE TABLE graph_cache (328+    repo         TEXT NOT NULL,329+    head_save_id TEXT NOT NULL,330+    json         TEXT NOT NULL,331+    computed_at  BIGINT NOT NULL,332+    PRIMARY KEY (repo, head_save_id)333+);334+335+CREATE TABLE archive_cache (336+    repo_id    TEXT NOT NULL REFERENCES repos(name) ON UPDATE CASCADE,337+    save_id    TEXT NOT NULL,338+    path       TEXT NOT NULL,339+    size_bytes BIGINT NOT NULL,340+    created_at BIGINT NOT NULL,341+    -- Vestigial: live archive nar hashes are kept in archive_hashes now. The342+    -- column still exists in the SQLite source schema, so it is mirrored here so343+    -- the one-time SQLite→Postgres migration (which copies every source column344+    -- via PRAGMA table_info) does not fail on a missing target column.345+    nar_hash   TEXT,346+    PRIMARY KEY (repo_id, save_id)347+);348+349+CREATE TABLE archive_hashes (350+    repo_id     TEXT NOT NULL REFERENCES repos(name) ON UPDATE CASCADE,351+    save_id     TEXT NOT NULL,352+    blake3      TEXT NOT NULL,353+    sha256      TEXT NOT NULL,354+    nar_hash    TEXT,355+    computed_at BIGINT NOT NULL,356+    PRIMARY KEY (repo_id, save_id)357+);358+359+CREATE TABLE pow_challenges (360+    id         TEXT PRIMARY KEY,361+    nonce      TEXT NOT NULL,362+    difficulty BIGINT NOT NULL DEFAULT 4,363+    created_at BIGINT NOT NULL,364+    expires_at BIGINT NOT NULL,365+    used       BIGINT NOT NULL DEFAULT 0366+);367+368+-- ── Accounts, sessions, auth ─────────────────────────────────────────369+370+CREATE TABLE accounts (371+    id                   TEXT PRIMARY KEY,372+    username             TEXT NOT NULL UNIQUE,373+    bio                  TEXT,374+    location             TEXT,375+    website              TEXT,376+    email                TEXT,377+    recovery_hash        TEXT NOT NULL,378+    created_at           BIGINT NOT NULL,379+    tier                 TEXT NOT NULL DEFAULT 'contributor'380+        CHECK (tier IN ('operator', 'team', 'contributor')),381+    -- Operator web-login credentials (bcrypt password + base32 TOTP secret).382+    -- NULL until the operator enrols at /login; only the operator-tier row uses them.383+    password_hash        TEXT,384+    totp_secret          TEXT,385+    notif_offer_accepted BIGINT NOT NULL DEFAULT 1,386+    notif_offer_rejected BIGINT NOT NULL DEFAULT 1,387+    notif_issue_response BIGINT NOT NULL DEFAULT 1,388+    notif_issue_resolved BIGINT NOT NULL DEFAULT 1,389+    notif_issue_status   BIGINT NOT NULL DEFAULT 1390+);391+CREATE INDEX accounts_username ON accounts(username);392+-- Partial unique: one account per non-null email, enforced at the database393+-- so the join handler's check-then-act can't lose a race under concurrent394+-- registrations (review F4). Existing databases get this via db.zig's395+-- ensureUniqueEmailIndex idempotent migration instead, which must detect396+-- pre-existing duplicates first (D4).397+CREATE UNIQUE INDEX accounts_email_unique ON accounts(email) WHERE email IS NOT NULL;398+399+-- ── Internal tickets ─────────────────────────────────────────────────400+-- Operator/team-only ticket tracker. created_by/assigned_to reference401+-- accounts(id) (TEXT, not the int ids the original brief assumed).402+-- resolving_save_id is a plain TEXT column with no FK: saves has a403+-- composite PRIMARY KEY (id, repo), so a single-column FK to saves(id)404+-- is not expressible; integrity is enforced in the application layer.405+406+CREATE TABLE tickets (407+    id                BIGINT PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,408+    title             TEXT NOT NULL,409+    body              TEXT NOT NULL DEFAULT '',410+    state             TEXT NOT NULL DEFAULT 'open'411+        CHECK (state IN ('open', 'in_progress', 'closed')),412+    repo              TEXT REFERENCES repos(name) ON UPDATE CASCADE ON DELETE SET NULL,413+    resolving_save_id TEXT,414+    created_by        TEXT NOT NULL REFERENCES accounts(id),415+    assigned_to       TEXT REFERENCES accounts(id) ON DELETE SET NULL,416+    created_at        BIGINT NOT NULL DEFAULT EXTRACT(EPOCH FROM NOW())::BIGINT,417+    updated_at        BIGINT NOT NULL DEFAULT EXTRACT(EPOCH FROM NOW())::BIGINT,418+    closed_at         BIGINT419+);420+CREATE INDEX tickets_state ON tickets(state, created_at DESC);421+CREATE INDEX tickets_repo ON tickets(repo);422+CREATE INDEX tickets_assigned ON tickets(assigned_to);423+CREATE INDEX tickets_created_by ON tickets(created_by);424+425+CREATE TABLE ticket_attachments (426+    id          TEXT PRIMARY KEY,427+    ticket_id   BIGINT REFERENCES tickets(id) ON DELETE CASCADE,428+    repo_id     TEXT REFERENCES repos(name) ON UPDATE CASCADE,429+    filename    TEXT NOT NULL,430+    mime_type   TEXT NOT NULL,431+    size_bytes  BIGINT NOT NULL,432+    path        TEXT NOT NULL,433+    created_at  BIGINT NOT NULL434+);435+CREATE INDEX ticket_attachments_ticket ON ticket_attachments(ticket_id, created_at);436+CREATE INDEX ticket_attachments_repo ON ticket_attachments(repo_id, created_at);437+438+-- ── Collaboration layer + notifications ──────────────────────────────439+-- author/account columns reference accounts(id) (TEXT). Comment ids are440+-- BIGINT identity to match the project convention (the brief assumed441+-- SQLite-era INTEGER identity). save_comments carries both save_id and442+-- repo so a composite FK to saves(id, repo) is expressible; the other443+-- save-keyed tables hold only save_id (plain TEXT, no FK — saves has a444+-- composite PK) and rely on application-layer integrity. Boolean-ish445+-- columns are BIGINT 0/1 to match the existing schema (accounts.notif_*,446+-- repos.*), since the pg shim binds/reads integers, not SQL BOOLEAN.447+448+CREATE TABLE save_comments (449+    id                BIGINT PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,450+    save_id           TEXT NOT NULL,451+    repo              TEXT NOT NULL REFERENCES repos(name) ON UPDATE CASCADE ON DELETE CASCADE,452+    author_account_id TEXT NOT NULL REFERENCES accounts(id),453+    body              TEXT NOT NULL,454+    created_at        BIGINT NOT NULL DEFAULT EXTRACT(EPOCH FROM NOW())::BIGINT,455+    updated_at        BIGINT NOT NULL DEFAULT EXTRACT(EPOCH FROM NOW())::BIGINT,456+    edited            BIGINT NOT NULL DEFAULT 0,457+    FOREIGN KEY (save_id, repo) REFERENCES saves(id, repo) ON DELETE CASCADE458+);459+CREATE INDEX save_comments_save_id ON save_comments(save_id, created_at);460+CREATE INDEX save_comments_repo ON save_comments(repo, created_at DESC);461+CREATE INDEX save_comments_author ON save_comments(author_account_id);462+463+CREATE TABLE save_acknowledgements (464+    save_id    TEXT NOT NULL,465+    account_id TEXT NOT NULL REFERENCES accounts(id) ON DELETE CASCADE,466+    kind       TEXT NOT NULL DEFAULT 'ack'467+        CHECK (kind IN ('ack', 'flag')),468+    created_at BIGINT NOT NULL DEFAULT EXTRACT(EPOCH FROM NOW())::BIGINT,469+    PRIMARY KEY (save_id, account_id)470+);471+CREATE INDEX save_acks_save_id ON save_acknowledgements(save_id);472+473+CREATE TABLE draft_comments (474+    id                BIGINT PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,475+    offer_id          TEXT NOT NULL REFERENCES pending_offers(offer_id) ON DELETE CASCADE,476+    author_account_id TEXT NOT NULL REFERENCES accounts(id),477+    body              TEXT NOT NULL,478+    created_at        BIGINT NOT NULL DEFAULT EXTRACT(EPOCH FROM NOW())::BIGINT479+);480+CREATE INDEX draft_comments_offer ON draft_comments(offer_id, created_at);481+482+CREATE TABLE notifications (483+    id           BIGINT PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,484+    account_id   TEXT NOT NULL REFERENCES accounts(id) ON DELETE CASCADE,485+    kind         TEXT NOT NULL CHECK (kind IN (486+        'save_comment', 'save_comment_reply', 'save_mention', 'save_ack',487+        'save_flag', 'contribution_accepted', 'contribution_declined',488+        'contribution_pending', 'draft_comment', 'issue_filed', 'issue_reply',489+        'issue_response', 'issue_resolved', 'issue_status',490+        'ticket_assigned', 'ticket_state_changed', 'team_promoted',491+        'milestone_saves', 'milestone_downloads', 'milestone_first_steal',492+        'milestone_first_contributor', 'milestone_trusted'493+    )),494+    payload_json TEXT NOT NULL DEFAULT '{}',495+    preview      TEXT NOT NULL DEFAULT '',496+    href         TEXT NOT NULL DEFAULT '/',497+    read         BIGINT NOT NULL DEFAULT 0,498+    created_at   BIGINT NOT NULL DEFAULT EXTRACT(EPOCH FROM NOW())::BIGINT,499+    read_at      BIGINT500+);501+CREATE INDEX notifications_account_unread ON notifications(account_id, read, created_at DESC);502+CREATE INDEX notifications_account_all ON notifications(account_id, created_at DESC);503+CREATE INDEX notifications_created_at ON notifications(created_at DESC);504+505+CREATE TABLE notification_preferences (506+    account_id TEXT NOT NULL REFERENCES accounts(id) ON DELETE CASCADE,507+    kind       TEXT NOT NULL,508+    in_app     BIGINT NOT NULL DEFAULT 1,509+    email      BIGINT NOT NULL DEFAULT 0,510+    PRIMARY KEY (account_id, kind)511+);512+513+CREATE TABLE notification_category_prefs (514+    account_id TEXT NOT NULL REFERENCES accounts(id) ON DELETE CASCADE,515+    category   TEXT NOT NULL CHECK (category IN (516+        'contributions', 'discussion', 'issues', 'tickets', 'milestones'517+    )),518+    in_app     BIGINT NOT NULL DEFAULT 1,519+    email      BIGINT NOT NULL DEFAULT 0,520+    PRIMARY KEY (account_id, category)521+);522+523+CREATE TABLE sessions (524+    id         TEXT PRIMARY KEY,525+    account_id TEXT NOT NULL REFERENCES accounts(id) ON DELETE CASCADE,526+    created_at BIGINT NOT NULL,527+    expires_at BIGINT NOT NULL,528+    last_used  BIGINT NOT NULL,529+    user_agent TEXT530+);531+CREATE INDEX sessions_account ON sessions(account_id);532+CREATE INDEX sessions_expiry ON sessions(expires_at);533+534+CREATE TABLE account_recovery_codes (535+    id          TEXT PRIMARY KEY,536+    account_id  TEXT NOT NULL REFERENCES accounts(id) ON DELETE CASCADE,537+    code_hash   TEXT NOT NULL,538+    created_at  BIGINT NOT NULL,539+    expires_at  BIGINT NOT NULL,540+    used        BIGINT NOT NULL DEFAULT 0541+);542+CREATE INDEX account_recovery_codes_account ON account_recovery_codes(account_id, expires_at);543+544+-- ── Health, safety, trust ────────────────────────────────────────────545+546+CREATE TABLE repo_health (547+    repo_id        TEXT PRIMARY KEY REFERENCES repos(name) ON UPDATE CASCADE,548+    save_id        TEXT NOT NULL,549+    computed_at    BIGINT NOT NULL,550+    secrets_status TEXT NOT NULL DEFAULT 'clean',551+    secrets_detail TEXT,552+    deps_status    TEXT NOT NULL DEFAULT 'none',553+    deps_detail    TEXT,554+    builds_status  TEXT NOT NULL DEFAULT 'none',555+    builds_detail  TEXT,556+    agents_status  TEXT NOT NULL DEFAULT 'none',557+    agents_detail  TEXT,558+    license_status TEXT NOT NULL DEFAULT 'none',559+    license_detail TEXT,560+    trust_status   TEXT NOT NULL DEFAULT 'clean',561+    trust_detail   TEXT,562+    collapsed_html TEXT NOT NULL DEFAULT ''563+);564+565+CREATE TABLE secret_scan_results (566+    id          TEXT PRIMARY KEY,567+    repo_id     TEXT NOT NULL REFERENCES repos(name) ON UPDATE CASCADE,568+    save_id     TEXT NOT NULL,569+    file_path   TEXT NOT NULL,570+    line_number BIGINT,571+    pattern     TEXT NOT NULL,572+    entropy     DOUBLE PRECISION,573+    severity    TEXT NOT NULL,574+    status      TEXT NOT NULL,575+    detected_at BIGINT NOT NULL576+);577+CREATE INDEX secret_scan_results_repo_save578+    ON secret_scan_results(repo_id, save_id, detected_at DESC);579+580+CREATE TABLE secret_false_positives (581+    repo_id   TEXT NOT NULL,582+    file_path TEXT NOT NULL,583+    marked_at BIGINT NOT NULL,584+    PRIMARY KEY (repo_id, file_path)585+);586+587+CREATE TABLE code_finding_false_positives (588+    repo_id   TEXT NOT NULL,589+    code      TEXT NOT NULL,590+    path      TEXT NOT NULL,591+    marked_at BIGINT NOT NULL,592+    PRIMARY KEY (repo_id, code, path)593+);594+595+-- Operator-dismissed safety findings. A suppressed finding is filtered from596+-- the public repo page and the admin panel; the dismissed list remains597+-- visible and reversible. Keyed by (repo_id, file_path, rule, fingerprint)598+-- so a suppression only matches when the same content fires the same rule.599+CREATE TABLE safety_suppressions (600+    id           TEXT PRIMARY KEY,601+    repo_id      TEXT NOT NULL REFERENCES repos(name) ON UPDATE CASCADE ON DELETE CASCADE,602+    file_path    TEXT NOT NULL,603+    rule         TEXT NOT NULL,604+    fingerprint  TEXT NOT NULL,605+    dismissed_at BIGINT NOT NULL,606+    dismissed_by TEXT NOT NULL REFERENCES accounts(id),607+    note         TEXT608+);609+CREATE INDEX safety_suppressions_repo ON safety_suppressions(repo_id, file_path, rule);610+611+CREATE TABLE dep_advisories (612+    id          TEXT PRIMARY KEY,613+    repo_id     TEXT NOT NULL REFERENCES repos(name) ON UPDATE CASCADE,614+    save_id     TEXT NOT NULL,615+    dep_name    TEXT NOT NULL,616+    dep_version TEXT NOT NULL,617+    cve_id      TEXT,618+    severity    TEXT NOT NULL,619+    summary     TEXT NOT NULL,620+    fix_version TEXT,621+    detected_at BIGINT NOT NULL622+);623+CREATE INDEX dep_advisories_repo_save624+    ON dep_advisories(repo_id, save_id, detected_at DESC);625+626+CREATE TABLE binary_sizes (627+    id         TEXT PRIMARY KEY,628+    repo_id    TEXT NOT NULL REFERENCES repos(name) ON UPDATE CASCADE,629+    save_id    TEXT NOT NULL,630+    platform   TEXT NOT NULL,631+    size_bytes BIGINT NOT NULL,632+    built_at   BIGINT NOT NULL633+);634+CREATE INDEX binary_sizes_repo_save635+    ON binary_sizes(repo_id, save_id, built_at DESC);636+637+CREATE TABLE trust_results (638+    repo_id             TEXT PRIMARY KEY REFERENCES repos(name) ON UPDATE CASCADE,639+    save_id             TEXT NOT NULL,640+    trusted             BIGINT NOT NULL,641+    secrets_clean       BIGINT NOT NULL,642+    no_binary_blobs     BIGINT NOT NULL,643+    build_scripts_clean BIGINT NOT NULL,644+    no_trojan_source    BIGINT NOT NULL,645+    supply_chain_stable BIGINT NOT NULL,646+    licenses_compatible BIGINT NOT NULL,647+    has_attribution     BIGINT NOT NULL,648+    detail_json         TEXT NOT NULL DEFAULT '{}',649+    computed_at         BIGINT NOT NULL650+);651+652+-- ── Engagement and notifications ─────────────────────────────────────653+654+CREATE TABLE engagement_events (655+    id          TEXT PRIMARY KEY,656+    event_type  TEXT NOT NULL,657+    repo_id     TEXT,658+    save_id     TEXT,659+    platform    TEXT,660+    occurred_at BIGINT NOT NULL661+);662+CREATE INDEX eng_repo_type ON engagement_events(repo_id, event_type, occurred_at DESC);663+CREATE INDEX eng_type_time ON engagement_events(event_type, occurred_at DESC);664+665+CREATE TABLE notification_queue (666+    id         TEXT PRIMARY KEY,667+    account_id TEXT,668+    type       TEXT NOT NULL,669+    payload    TEXT NOT NULL,670+    status     TEXT NOT NULL DEFAULT 'pending',671+    attempts   BIGINT NOT NULL DEFAULT 0,672+    next_retry BIGINT,673+    created_at BIGINT NOT NULL,674+    sent_at    BIGINT675+);676+CREATE INDEX notification_queue_pending677+    ON notification_queue(status, next_retry);678+679+-- ┌─────────────────────────────────────────────────────────────────┐680+-- │  JOB QUEUE — Postgres-native, SELECT FOR UPDATE SKIP LOCKED.      │681+-- │  New in the Postgres architecture; has no SQLite predecessor.     │682+-- └─────────────────────────────────────────────────────────────────┘683+684+CREATE TABLE jobs (685+    id           BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,686+    kind         TEXT NOT NULL,687+    payload_json TEXT NOT NULL DEFAULT '{}',688+    status       TEXT NOT NULL DEFAULT 'pending'689+        CHECK (status IN ('pending','running','done','failed')),690+    -- Only one pending/running job per (kind, dedup_key) — collapses the691+    -- thundering herd of a 1000-save offer into one job of each kind per repo.692+    dedup_key    TEXT,693+    priority     BIGINT NOT NULL DEFAULT 5,694+    attempts     BIGINT NOT NULL DEFAULT 0,695+    max_attempts BIGINT NOT NULL DEFAULT 3,696+    created_at   BIGINT NOT NULL,697+    started_at   BIGINT,698+    completed_at BIGINT,699+    error_text   TEXT,700+    run_after    BIGINT NOT NULL DEFAULT 0701+);702+703+-- Worker poll path: lowest priority then oldest, pending only.704+CREATE INDEX jobs_pending ON jobs(priority, created_at)705+    WHERE status = 'pending';706+707+-- Deduplication: enforce one live job per (kind, dedup_key).708+CREATE UNIQUE INDEX jobs_dedup ON jobs(kind, dedup_key)709+    WHERE status IN ('pending','running') AND dedup_key IS NOT NULL;710+711+-- Cleanup scan for pruning finished jobs.712+CREATE INDEX jobs_completed_at ON jobs(completed_at)713+    WHERE status IN ('done','failed');714+715+COMMIT;

@@ -0,0 +1,304 @@1+import { afterAll, beforeAll, expect, test } from "bun:test";2+import { mkdir, mkdtemp, readdir, writeFile } from "node:fs/promises";3+import { tmpdir } from "node:os";4+import { join } from "node:path";5+import { blake3 } from "@noble/hashes/blake3.js";6+import { bytesToHex } from "@noble/hashes/utils.js";7+import { SQL } from "bun";8+import { instance } from "./browser.ts";9+import { makeRepos } from "./fixture.ts";10+11+// A throwaway Postgres holding Kepr 2's schema and a few synthetic rows, stopped after the tests.12+const SCRIPT = join(import.meta.dir, "../scripts/import-kepr2.ts");13+const root = await mkdtemp(join(tmpdir(), "kepr-import-"));14+const pgDir = join(root, "pg");15+const kepr2Data = join(root, "kepr2");16+let url = "";17+let bucketDir = "";18+let save = "";19+20+const hex = (bytes: Uint8Array) => bytesToHex(blake3(bytes));21+const sha = (bytes: Uint8Array) => new Bun.CryptoHasher("sha256").update(bytes).digest("hex");22+const bytesOf = (text: string) => new TextEncoder().encode(text);23+const run = (cmd: string[]) => {24+	const out = Bun.spawnSync(cmd, { cwd: root, env: { PATH: process.env.PATH, LC_ALL: "C" } });25+	if (out.exitCode !== 0) throw new Error(`${cmd.join(" ")}: ${out.stderr.toString()}`);26+};27+28+async function kepr2File(path: string, text: string) {29+	await mkdir(join(kepr2Data, "releases", path, ".."), { recursive: true });30+	await writeFile(join(kepr2Data, "releases", path), text);31+	return bytesOf(text);32+}33+34+beforeAll(async () => {35+	const repos = await makeRepos(["demo"]);36+	bucketDir = repos.bucketDir;37+	save = repos.saves.demo as string;38+	const probe = Bun.serve({ port: 0, fetch: () => new Response() });39+	const port = probe.port;40+	probe.stop(true);41+	run(["initdb", "-D", pgDir, "-U", "kepr", "-A", "trust"]);42+	const options = `-p ${port} -k ${pgDir} -c listen_addresses=127.0.0.1`;43+	run(["pg_ctl", "-D", pgDir, "-o", options, "-l", join(root, "pg.log"), "-w", "start"]);44+	url = `postgres://[email protected]:${port}/postgres`;45+	run([46+		"psql",47+		url,48+		"-q",49+		"-v",50+		"ON_ERROR_STOP=1",51+		"-f",52+		join(import.meta.dir, "fixtures/kepr2/schema.sql"),53+	]);54+55+	const legacy = await kepr2File("demo/1.0.0/linux-x86_64/demo", "demo 1.0.0 linux");56+	const byPlatform = await kepr2File("demo/1.1.0/macos-aarch64", "demo 1.1.0 mac");57+	const apk = await kepr2File("demo/1.1.0/android-arm64/demo-1.1.0.apk", "demo 1.1.0 apk");58+	const failed = await kepr2File("demo/1.2.0/linux-x86_64/demo", "demo 1.2.0 failed");59+	await kepr2File("demo/1.3.0/linux-x86_64/demo", "demo 1.3.0 tampered");60+	const secret = await kepr2File("secret/0.1.0/linux-x86_64/secret", "secret 0.1.0");61+	const oldPlatform = await kepr2File(62+		"demo/1.4.0/x86_64-linux/demo",63+		"demo 1.4.0 old platform name",64+	);65+	const artifact = (66+		id: string,67+		run: string,68+		repo: string,69+		version: string,70+		platform: string,71+		name: string,72+		bytes: Uint8Array,73+		b3 = hex(bytes),74+	) =>75+		`('${id}', '${run}', '${repo}', '${version}', '${platform}', '${name}', ${bytes.length}, '${sha(bytes)}', '${b3}', 5)`;76+	const pg = new SQL(url);77+	await pg.unsafe(`78+		INSERT INTO repos (name, created_at, description, categories, private, builds_enabled) VALUES79+			('demo', 1, 'A demo.', 'tools, cli', 0, 1), ('secret', 1, NULL, '', 1, 0);80+		INSERT INTO accounts (id, username, recovery_hash, created_at) VALUES ('acc-r', 'rita', 'x', 1), ('acc-o', 'olga', 'x', 1);81+		INSERT INTO issues (id, repo, number, title, description, contact, contact_email, filed_at, filed_save, filed_ver, status, closed_at, closed_save, type, account_id, reporter_name) VALUES82+			(1, 'demo', 7, 'Crash on start', 'It <b>crashes</b>.', NULL, '[email protected]', 1000, 'aaaaaaaaaaaa', '1.0.0', 'inprogress', NULL, NULL, 'bug', 'acc-r', NULL),83+			(2, 'demo', 9, 'Dark mode', 'Please.', '[email protected]', NULL, 1500, 'aaaaaaaaaaaa', NULL, 'wontfix', 2000, '${save}', 'enhancement', NULL, 'Sam');84+		INSERT INTO issue_entries (id, issue_id, account_id, entry_type, body, created_at) VALUES85+			('e1', 1, 'operator', 'reply', 'Looking.', 1100), ('e2', 1, 'acc-r', 'reply', 'Thanks!', 1200),86+			('e3', 1, 'operator', 'status_change', NULL, 1300), ('e4', 1, 'acc-o', 'reply', 'Me too.', 1400);87+		INSERT INTO issue_notes (issue_id, body, posted_at, is_op) VALUES (1, 'internal only', 1250, 1);88+		INSERT INTO engagement_events (id, event_type, repo_id, occurred_at) VALUES89+			('v1', 'nix_install', 'demo', 1), ('v2', 'nix_install', 'demo', 2), ('v3', 'nix_install', 'demo', 3),90+			('v4', 'archive_download', 'demo', 4), ('v5', 'nix_install', 'secret', 5);91+		INSERT INTO build_runs (id, repo_id, save_id, version, trigger, status, started_at, finished_at, release_notes) VALUES92+			('b1', 'demo', '${save}', '1.0.0', 'manual', 'success', 10, 20, NULL),93+			('b2', 'demo', '${save}', '1.1.0', 'manual', 'partial', 30, 40, 'Starts twice as fast and no longer crashes on start.'),94+			('b3', 'demo', '${save}', '1.2.0', 'manual', 'failed', 50, 60, NULL),95+			('b4', 'demo', '${save}', '1.3.0', 'manual', 'success', 70, 80, NULL),96+			('b5', 'secret', '${save}', '0.1.0', 'manual', 'success', 90, 100, NULL),97+			('b6', 'demo', '${save}', '1.4.0', 'manual', 'success', 110, 120, NULL);98+		INSERT INTO release_artifacts (id, build_run, repo_id, version, platform, filename, size_bytes, sha256, blake3, built_at) VALUES99+			${artifact("a1", "b1", "demo", "1.0.0", "linux-x86_64", "demo", legacy)},100+			${artifact("a2", "b2", "demo", "1.1.0", "macos-aarch64", "demo", byPlatform)},101+			${artifact("a3", "b2", "demo", "1.1.0", "android-arm64", "demo-1.1.0.apk", apk)},102+			${artifact("a4", "b3", "demo", "1.2.0", "linux-x86_64", "demo", failed)},103+			${artifact("a5", "b4", "demo", "1.3.0", "linux-x86_64", "demo", legacy, hex(legacy))},104+			${artifact("a6", "b5", "secret", "0.1.0", "linux-x86_64", "secret", secret)},105+			${artifact("a7", "b6", "demo", "1.4.0", "x86_64-linux", "demo", oldPlatform)};106+	`);107+	await pg.close();108+});109+110+afterAll(() => run(["pg_ctl", "-D", pgDir, "-m", "fast", "-w", "stop"]));111+112+async function importKepr2(target: Record<string, string>, keprData: string, ...args: string[]) {113+	const env = {114+		PATH: process.env.PATH,115+		KEPR2_DATABASE_URL: url,116+		KEPR2_DATA: kepr2Data,117+		KEPR_DATA: keprData,118+		...target,119+	};120+	// Not spawnSync: the S3 test's stand-in server runs on this process's event loop.121+	const child = Bun.spawn(["bun", SCRIPT, ...args], { cwd: root, env, stderr: "pipe" });122+	const [stdout, stderr, code] = await Promise.all([123+		new Response(child.stdout).text(),124+		new Response(child.stderr).text(),125+		child.exited,126+	]);127+	if (code !== 0) throw new Error(stderr);128+	return stdout;129+}130+131+async function files(dir: string): Promise<string[]> {132+	return (await readdir(dir, { recursive: true, withFileTypes: true }))133+		.filter((entry) => entry.isFile())134+		.map((entry) => join(entry.parentPath, entry.name).slice(dir.length + 1))135+		.sort();136+}137+138+test("--dry-run prints the import and writes neither the bucket nor kepr.db", async () => {139+	const before = await files(bucketDir);140+	const keprData = await mkdtemp(join(tmpdir(), "kepr-import-dry-"));141+	const out = await importKepr2({ KOH_FOLDER: bucketDir }, keprData, "--dry-run");142+	expect(out).toContain("would put demo/releases/1.1.0/release.json");143+	expect(out).toContain('issues {"repo":"demo","number":"7"');144+	expect(await files(bucketDir)).toEqual(before);145+	const inst = await instance(bucketDir, keprData);146+	expect(inst.kepr.query("SELECT COUNT(*) AS n FROM issues").get()).toEqual({ n: 0 });147+});148+149+test("the import lands Kepr 2's issues, counts, settings and releases, and a re-run changes nothing", async () => {150+	const keprData = await mkdtemp(join(tmpdir(), "kepr-import-data-"));151+	const first = await importKepr2({ KOH_FOLDER: bucketDir }, keprData);152+	expect(first).toContain("skip demo/releases/1.3.0: linux-x86_64: not Kepr 2's file");153+	expect(first).toContain("skip demo/releases/1.4.0: release record does not match §11.2");154+	expect(first).not.toContain("1.2.0");155+	const landed = await files(bucketDir);156+	expect(landed.filter((key) => key.includes("/releases/"))).toEqual([157+		"demo/releases/1.0.0/linux-x86_64/demo-linux-x86_64",158+		"demo/releases/1.0.0/release.json",159+		"demo/releases/1.1.0/android-arm64/demo-1.1.0.apk",160+		"demo/releases/1.1.0/macos-aarch64/demo-macos-aarch64",161+		"demo/releases/1.1.0/release.json",162+		"demo/releases/latest",163+		"secret/releases/0.1.0/linux-x86_64/secret-linux-x86_64",164+		"secret/releases/0.1.0/release.json",165+		"secret/releases/latest",166+	]);167+	const again = await importKepr2({ KOH_FOLDER: bucketDir }, keprData);168+	expect(again).not.toMatch(/^(put|would put|differs)/m);169+	// Only the repo settings are set again (to the same values); nothing else is added.170+	expect(171+		again.split("\n").filter((line) => line.endsWith(" set") && !line.startsWith("repos ")),172+	).toEqual([]);173+	expect(await files(bucketDir)).toEqual(landed);174+175+	const inst = await instance(bucketDir, keprData);176+	const q = (sql: string) => inst.kepr.query(sql).all();177+	expect(q("SELECT name, public, category FROM repos ORDER BY name")).toEqual([178+		{ name: "demo", public: 1, category: "tools" },179+		{ name: "secret", public: 0, category: "" },180+	]);181+	expect(q("SELECT repo, count FROM installs ORDER BY repo")).toEqual([182+		{ repo: "demo", count: 3 },183+		{ repo: "secret", count: 1 },184+	]);185+	expect(q("SELECT COUNT(*) AS n FROM downloads")).toEqual([{ n: 0 }]);186+	expect(q("SELECT repo, version, hidden FROM release_settings")).toEqual([187+		{ repo: "secret", version: "0.1.0", hidden: 1 },188+	]);189+	expect(190+		q(191+			"SELECT number, title, body, type, version, reporter_name, contact_email, filed_at, status, closed_at, fixed_in, token_hash FROM issues ORDER BY number",192+		),193+	).toEqual([194+		{195+			number: 7,196+			title: "Crash on start",197+			body: "It <b>crashes</b>.",198+			type: "bug",199+			version: "1.0.0",200+			reporter_name: "@rita",201+			contact_email: "[email protected]",202+			filed_at: 1000,203+			status: "open",204+			closed_at: null,205+			fixed_in: "",206+			token_hash: null,207+		},208+		{209+			number: 9,210+			title: "Dark mode",211+			body: "Please.",212+			type: "idea",213+			version: "",214+			reporter_name: "Sam",215+			contact_email: "[email protected]",216+			filed_at: 1500,217+			status: "closed",218+			closed_at: 2000,219+			fixed_in: save,220+			token_hash: null,221+		},222+	]);223+	expect(q("SELECT author, body, created_at FROM issue_entries ORDER BY id")).toEqual([224+		{ author: "operator", body: "Looking.", created_at: 1100 },225+		{ author: "reporter", body: "Thanks!", created_at: 1200 },226+		{ author: "operator", body: "Me too.", created_at: 1400 },227+	]);228+229+	// Kepr 3 reads what the import wrote as any Koh 3 release.230+	type Latest = { version: string; notes: string; platforms: Record<string, unknown> };231+	const latest = (await (await inst.app.request("/api/demo/releases/latest")).json()) as Latest;232+	expect(latest.version).toBe("1.1.0");233+	expect(latest.notes).toBe("Starts twice as fast and no longer crashes on start.");234+	expect(Object.keys(latest.platforms)).toEqual(["android-arm64", "macos-aarch64"]);235+	const record = await Bun.file(join(bucketDir, "demo/releases/1.0.0/release.json")).json();236+	const legacy = bytesOf("demo 1.0.0 linux");237+	expect(record).toEqual({238+		format: 1,239+		repo: "demo",240+		version: "1.0.0",241+		save,242+		published_at: 20,243+		machine: "kepr2",244+		notes: "",245+		files: [246+			{247+				platform: "linux-x86_64",248+				name: "demo-linux-x86_64",249+				size: legacy.length,250+				blake3: hex(legacy),251+				sha256: sha(legacy),252+			},253+		],254+	});255+	expect(await (await inst.app.request("/demo/issues/7")).text()).toContain("Me too.");256+	expect(inst.kepr.query("SELECT last_sync_error FROM repos WHERE name = 'demo'").get()).toEqual({257+		last_sync_error: null,258+	});259+});260+261+test("into an S3 bucket, files go up as presigned PUTs with If-None-Match, never over an existing key", async () => {262+	const s3Dir = await mkdtemp(join(tmpdir(), "kepr-import-s3-"));263+	const puts: string[] = [];264+	const server = Bun.serve({265+		port: 0,266+		async fetch(req) {267+			const url = new URL(req.url);268+			const path = join(s3Dir, decodeURIComponent(url.pathname.replace("/koh/", "")));269+			const file = Bun.file(path);270+			if (req.method === "GET")271+				return (await file.exists())272+					? new Response(file)273+					: new Response("<Error><Code>NoSuchKey</Code></Error>", { status: 404 });274+			expect([req.headers.get("if-none-match"), url.searchParams.has("X-Amz-Signature")]).toEqual([275+				"*",276+				true,277+			]);278+			if (await file.exists()) return new Response("", { status: 412 });279+			puts.push(url.pathname);280+			await Bun.write(path, await req.arrayBuffer());281+			return new Response("");282+		},283+	});284+	try {285+		const target = {286+			KOH_S3_ENDPOINT: `http://localhost:${server.port}`,287+			KOH_S3_BUCKET: "koh",288+			KOH_S3_REGION: "fr-par",289+			KOH_S3_ACCESS_KEY_ID: "write",290+			KOH_S3_SECRET_ACCESS_KEY: "key",291+		};292+		await importKepr2(target, await mkdtemp(join(tmpdir(), "kepr-import-s3data-")));293+		expect(puts.length).toBe(9);294+		for (const key of await files(s3Dir)) {295+			expect(await Bun.file(join(s3Dir, key)).bytes()).toEqual(296+				await Bun.file(join(bucketDir, key)).bytes(),297+			);298+		}299+		await importKepr2(target, await mkdtemp(join(tmpdir(), "kepr-import-s3data-")));300+		expect(puts.length).toBe(9);301+	} finally {302+		server.stop(true);303+	}304+});