-- D1 schema for the data portal (data-portal skill).
--
-- Four tables: who may authenticate (people), who currently is (sessions),
-- what they have dropped (manifest), and how hard they have been trying
-- (auth_attempts). Everything is IF NOT EXISTS so re-applying is safe.

-- One row per enrolled person. No plaintext passcode is ever stored: the
-- enrolment script generates the passcode, prints it to the operator once, and
-- persists only the salt and a SHA-256 of salt || passcode.
--
-- There is no `iterations` column. Verification is a single fast hash, which is
-- safe only because passcodes are machine-generated at a pinned length — see the
-- note at hashPasscode in functions/api/_lib/passcode.mjs. A cost column here
-- would imply a tunable that does not exist.
--
-- `iterations` was dropped outright rather than migrated because no portal had
-- been deployed when it went (Task 1708). If you meet a `people` table that still
-- has the column, this file will NOT remove it — IF NOT EXISTS is a no-op on an
-- existing table — and enrolment's INSERT will fail on `iterations NOT NULL`.
-- Drop the column by hand in that case; do not add it back here.
--
-- `accountId` binds a person to the sub-account whose deliverable folders they
-- may read. It carries a DEFAULT so this file stays a no-op on a table that
-- predates it — but an existing table will NOT gain the column, and a person
-- whose row has the empty default sees an empty folder list. Re-enrol those
-- people; the upsert on ownerId makes that a re-run, not a migration.
--
-- `folders` is a per-person allowlist of top-level folder names, comma
-- separated, that narrows what this person sees below the account's exposed
-- set. Empty (the default) means the full set, so an existing person keeps
-- today's behaviour. Like `accountId`, IF NOT EXISTS will NOT add this column
-- to a `people` table that predates it, and enrolment's INSERT names `folders`,
-- so it fails loudly on such a table. Add it by hand in that case:
--   ALTER TABLE people ADD COLUMN folders TEXT NOT NULL DEFAULT '';
CREATE TABLE IF NOT EXISTS people (
  ownerId    TEXT PRIMARY KEY,
  name       TEXT NOT NULL,
  salt       TEXT NOT NULL,
  hash       TEXT NOT NULL,
  createdAt  TEXT NOT NULL,
  accountId  TEXT NOT NULL DEFAULT '',
  folders    TEXT NOT NULL DEFAULT ''
);

-- `pcCheck` is a short digest of the person's passcode hash at mint time, and
-- resolveSession requires it to still match `people.hash`. It is what makes
-- revocation structural: rotating a passcode necessarily changes the hash, so
-- every session minted against the old one stops resolving immediately, with
-- no cleanup step for an operator to remember and no 12-hour window to wait
-- out. A leaked passcode is exactly the case where a procedural remedy fails.
CREATE TABLE IF NOT EXISTS sessions (
  sessionId TEXT PRIMARY KEY,
  ownerId   TEXT NOT NULL,
  pcCheck   TEXT NOT NULL,
  expiresAt INTEGER NOT NULL,
  createdAt TEXT NOT NULL
);

CREATE INDEX IF NOT EXISTS sessions_owner ON sessions (ownerId);

-- One row per stored object, carrying a three-state lifecycle (Task 1910).
--
--   in transit  ingested=0, devicePath=''   bytes in R2
--   moved       ingested=1, devicePath set  bytes on the device, R2 object gone
--   withdrawn   deleted=1                   device file pending removal
--
-- `ingested` is the claim flag the admin server's `portal-uploads-pull` loop
-- reads and sets; a row stays 0 until its object is on the device at the size
-- recorded here, so the backlog survives the device being offline and a
-- half-finished transfer never reads as delivered.
--
-- `targetPath` is the exposed folder the client chose at upload, '' when they
-- chose none. It is part of `objectKey` (ownerId/targetPath/filename), because
-- a flat key collides the same filename dropped into two folders: the upsert
-- below would overwrite the first object, reset ingested, and the first file
-- would never land on the device at all.
--
-- `devicePath` is written only after a verified device write, so its presence
-- IS the proof the move happened. A boolean would say a move occurred without
-- saying where, and the audit's unpublishedRouted check needs the path to
-- compare against the published index.
--
-- `deleted` marks a withdrawal of an already-moved file, whose bytes are on the
-- device and whose R2 object is already gone. The row is removed outright once
-- the device file is confirmed absent — not kept as a tombstone, which would
-- grow this table without bound and force every listing read to filter it while
-- describing bytes that no longer exist anywhere.
--
-- IF NOT EXISTS adds no column to an existing table. A portal deployed before
-- this needs the three run by hand, once:
--   ALTER TABLE manifest ADD COLUMN targetPath TEXT NOT NULL DEFAULT '';
--   ALTER TABLE manifest ADD COLUMN devicePath TEXT NOT NULL DEFAULT '';
--   ALTER TABLE manifest ADD COLUMN deleted INTEGER NOT NULL DEFAULT 0;
-- Their absence is loud: upload names targetPath in its INSERT.
CREATE TABLE IF NOT EXISTS manifest (
  id         INTEGER PRIMARY KEY AUTOINCREMENT,
  fileId     TEXT UNIQUE NOT NULL,
  ownerId    TEXT NOT NULL,
  filename   TEXT NOT NULL,
  objectKey  TEXT UNIQUE NOT NULL,
  size       INTEGER NOT NULL,
  uploadedAt TEXT NOT NULL,
  ingested   INTEGER NOT NULL DEFAULT 0,
  targetPath TEXT NOT NULL DEFAULT '',
  devicePath TEXT NOT NULL DEFAULT '',
  deleted    INTEGER NOT NULL DEFAULT 0
);

CREATE INDEX IF NOT EXISTS manifest_uningested ON manifest (ingested);
CREATE INDEX IF NOT EXISTS manifest_owner ON manifest (ownerId);
-- The pull selects `ingested = 0 OR deleted = 1`, so the withdrawal half needs
-- its own index or a withdrawal costs a full scan every sixty seconds.
CREATE INDEX IF NOT EXISTS manifest_deleted ON manifest (deleted);

-- Fixed-window auth attempt counter. Pages Functions are stateless, so the
-- counter cannot live in process memory the way every other rate limiter in
-- this platform does. The unique index is what makes the upsert in
-- _lib/ratelimit.ts collapse onto one row per window.
CREATE TABLE IF NOT EXISTS auth_attempts (
  id          INTEGER PRIMARY KEY AUTOINCREMENT,
  scope       TEXT NOT NULL,
  windowStart INTEGER NOT NULL,
  count       INTEGER NOT NULL
);

CREATE UNIQUE INDEX IF NOT EXISTS auth_attempts_scope_window ON auth_attempts (scope, windowStart);

-- One row per file OR folder the device has published to this portal. Metadata
-- only: the bytes stay on the device and are fetched on demand through a
-- short-lived signed link, which is what lets the folder tree render while the
-- device is offline.
--
-- Written by a full replace per account (delete-all, then insert the current
-- walk), which is why there is no tombstone column: a file deleted or renamed on
-- the device simply stops being inserted on the next cycle. The UNIQUE key is
-- what makes that replace safe to retry — a double insert of the same path
-- fails loudly instead of silently doubling the row.
--
-- `isDir` marks a folder row. Folders are stored rather than derived from file
-- path prefixes, because a folder holding no files at any depth has no prefix
-- to derive it from and would not exist to the client at all. A folder row
-- carries sizeBytes 0 and the folder's own modified time, and the download path
-- refuses to mint a signed link for one.
--
-- Like `people.accountId` and `people.folders`, IF NOT EXISTS will NOT add this
-- column to a `directory` table that predates it, and the push names `isDir` in
-- its INSERT, so such a table fails loudly on the next cycle. Add it by hand in
-- that case:
--   ALTER TABLE directory ADD COLUMN isDir INTEGER NOT NULL DEFAULT 0;
-- No backfill follows it: the push replaces every row each cycle.
--
-- `generation` is which staging round a row belongs to (Task 1910). The push
-- claims a generation above every generation the account already holds, writes
-- the whole tree there, moves the pointer below in ONE forward-only statement,
-- then deletes strictly older generations (Task 1926). Uniqueness is scoped by
-- generation because two of them hold the same relPath between the write and
-- the flip; UNIQUE (accountId, relPath) would reject the staged write outright.
--   ALTER TABLE directory ADD COLUMN generation INTEGER NOT NULL DEFAULT 0;
CREATE TABLE IF NOT EXISTS directory (
  id         INTEGER PRIMARY KEY AUTOINCREMENT,
  accountId  TEXT NOT NULL,
  relPath    TEXT NOT NULL,
  sizeBytes  INTEGER NOT NULL,
  modifiedAt TEXT NOT NULL,
  indexedAt  TEXT NOT NULL,
  isDir      INTEGER NOT NULL DEFAULT 0,
  generation INTEGER NOT NULL DEFAULT 0,
  UNIQUE (accountId, relPath, generation)
);

CREATE INDEX IF NOT EXISTS directory_account ON directory (accountId);

-- Which generation of an account's rows is the live one.
--
-- The flip of this single row is the atomic point of a publish: a client
-- listing that races a push reads the old tree or the new one, and never a
-- half-built one. Cleanup of superseded rows afterwards need not be atomic,
-- because nothing reads a generation this pointer does not name.
--
-- This replaces the delete-then-insert window recorded as backlog Task 1842.
-- At sixty-second cadence that window would be hit sixty times more often than
-- it was hourly, which is what turned a known defect into a blocking one.
--
-- An account with no row here has never completed a publish, and every reader
-- joins through this table, so it correctly shows nothing rather than showing
-- a staged generation that no flip ever blessed.
CREATE TABLE IF NOT EXISTS directory_state (
  accountId         TEXT PRIMARY KEY,
  currentGeneration INTEGER NOT NULL
);
