87 lines
5.1 KiB
SQL
87 lines
5.1 KiB
SQL
-- +goose Up
|
|
|
|
-- The export door's own table (canon §createExport/§getExport, unified backlog row 236, D39.178).
|
|
--
|
|
-- ⚠ IT REPLACES A TABLE THAT HAS EXISTED SINCE 00002 AND WAS NEVER WRITTEN TO. That one was
|
|
-- designed before the canon settled this resource, and its shape now CONTRADICTS the ratified one:
|
|
-- it carries `ready boolean` plus `failed_reason text`, and the canon's own `Export.state`
|
|
-- description says why that cannot stand — «A state and not a boolean: a boolean merges three
|
|
-- situations into "not ready" and a poll on it never ends». It has no reader and no writer in this
|
|
-- module (`grep -rn '\bexports\b' --include=*.go internal/ cmd/` finds one COMMENT), so nothing is
|
|
-- migrated across: it is dropped and re-made. The down path restores it exactly as 00002 left it,
|
|
-- because a released migration's shape is what a rollback owes.
|
|
--
|
|
-- An export is an ARTIFACT the platform BUILDS — `tmctl build --out` into a directory of the
|
|
-- platform's own — and never a file picked up beside the engine's project database: the set that
|
|
-- lives there is the last CLI build's and belongs to the operator, and serving it would present a
|
|
-- previous build as this request's answer (D39.175 п.2).
|
|
--
|
|
-- There is no owner column on purpose. Ownership of an export IS ownership of its book, and a
|
|
-- second copy of that fact is a second place for an authorization check to disagree with itself
|
|
-- (API1 BOLA). Every read joins `books.owner_id`.
|
|
drop table exports;
|
|
|
|
create table exports (
|
|
id text primary key,
|
|
book_id text not null references books (id) on delete cascade,
|
|
-- The format asked for, echoed back on the wire. Not a closed set in SQL: which formats exist
|
|
-- is the ENGINE's fact, declared to this deployment by the operator
|
|
-- (TM_PLATFORM_EXPORT_FORMATS), and a check constraint here would be a third copy of it.
|
|
format text not null,
|
|
-- The canon's four states, and a poll on any of them ends: pending → ready → expired, or
|
|
-- pending → failed. `ready` may still become `expired` and nothing else moves afterwards.
|
|
state text not null check (state in ('pending', 'ready', 'failed', 'expired')),
|
|
-- Absolute path of the built file while it exists. Server topology: it never crosses the wire
|
|
-- (the same rule that keeps BuildReport.files off it), and it is what the GC sweep deletes.
|
|
path text,
|
|
size_bytes bigint,
|
|
-- Machine reason when `state` is `failed`, null otherwise. The phrase is the client's.
|
|
failure_code text,
|
|
-- Whether the built file has no hole of any kind (BuildReport.complete). The door ALWAYS
|
|
-- builds — an incomplete book is written WITH the notice and the marks, never refused
|
|
-- (D39.178 п.1) — so this records which of the two a user was handed. Operator-facing, not
|
|
-- wire-facing: the honesty the reader needs is inside the file.
|
|
complete boolean,
|
|
requested_at timestamptz not null default now(),
|
|
-- When a worker actually PICKED THE BUILD UP, or null while it is still in the queue.
|
|
--
|
|
-- ⚠ It exists because the GC's "nobody is coming back for this" cannot be answered by age alone.
|
|
-- One queue serves spawns, parses and builds, and a parse may take a whole `jobs.JobTimeout`; four
|
|
-- of them ahead of an export mean the export waits without anything being wrong with it. Aging a
|
|
-- QUEUED row out on the same clock as a RUNNING one buries a build that has not started and tells
|
|
-- its user it was interrupted — which is false. The two get their own graces (exports.Config).
|
|
started_at timestamptz,
|
|
finished_at timestamptz,
|
|
-- When the link stops working. Set once the artifact exists, null while pending and on failure
|
|
-- — exactly the canon's own rule for `Export.expires_at`.
|
|
expires_at timestamptz
|
|
);
|
|
|
|
-- The reads: a book's exports, and the sweep's two questions. The sweep's indexes are PARTIAL
|
|
-- because it asks about one state at a time and the table is mostly rows it will never look at
|
|
-- again.
|
|
create index exports_book_idx on exports (book_id, requested_at desc);
|
|
create index exports_expiring_idx on exports (expires_at) where state = 'ready';
|
|
create index exports_pending_idx on exports (requested_at) where state = 'pending';
|
|
-- The GC's third question: an artifact whose row is already `expired` and whose bytes are still on
|
|
-- disk. It exists because the unlink is a SEPARATE act from the row's move and can fail — a full
|
|
-- disk, a lost mount, a crash between the two — and without a way to find those files again nothing
|
|
-- would ever retry, which is a leak that grows silently.
|
|
create index exports_unlinked_idx on exports (finished_at) where state = 'expired' and path is not null;
|
|
|
|
-- +goose Down
|
|
|
|
drop table exports;
|
|
|
|
create table exports (
|
|
id text primary key,
|
|
book_id text not null references books (id) on delete cascade,
|
|
format text not null,
|
|
ready boolean not null default false,
|
|
artifact text not null default '',
|
|
created_at timestamptz not null default now(),
|
|
ready_at timestamptz,
|
|
failed_reason text not null default ''
|
|
);
|
|
|
|
create index exports_book_idx on exports (book_id, created_at desc);
|