51 lines
2.9 KiB
SQL
51 lines
2.9 KiB
SQL
-- 0007: artifacts that live somewhere else.
|
|
--
|
|
-- The audio node is the master for the audiobooks. UmbraTrans holds the metadata needed
|
|
-- to reason about a file and a reference to where it actually is; it does not hold the
|
|
-- bytes. DECISION-6 (object storage) stays unresolved, and this is what lets the system
|
|
-- be useful before it is resolved rather than after.
|
|
--
|
|
-- `storage_key` was already NOT NULL and already carried a path, so nothing here is
|
|
-- required to register a file - these columns make the *reference* explicit and checkable
|
|
-- rather than implicit in a string.
|
|
|
|
ALTER TABLE public.artifacts
|
|
ADD COLUMN IF NOT EXISTS storage_uri text,
|
|
ADD COLUMN IF NOT EXISTS storage_node_id uuid REFERENCES public.compute_nodes (id) ON DELETE SET NULL,
|
|
ADD COLUMN IF NOT EXISTS integrity text NOT NULL DEFAULT 'unverified',
|
|
ADD COLUMN IF NOT EXISTS verified_at timestamptz;
|
|
|
|
-- The audio node is the master, so the reference has to say which machine and how. A bare
|
|
-- path means nothing to anybody reading this row six months later: `/audio/book.mp3` is
|
|
-- on whichever node happened to serve it that day.
|
|
COMMENT ON COLUMN public.artifacts.storage_uri IS
|
|
'A URI naming where the bytes actually are, e.g. file:///media/audiobooks/… or '
|
|
's3://…. Deliberately not assumed to be retrievable by this system: with no object '
|
|
'storage behind it, nothing here fetches from it, and integrity is how a reader can '
|
|
'tell a checked reference from an unchecked one.';
|
|
|
|
COMMENT ON COLUMN public.artifacts.integrity IS
|
|
'unverified - a node reported it and nobody has checked the bytes against '
|
|
'content_hash. verified - the bytes were read back and hashed, and verified_at says '
|
|
'when. The default is unverified because a node reporting a hash is a claim, and '
|
|
'treating the claim as the check is the mistake this column exists to prevent.';
|
|
|
|
-- Re-ingesting a library must not duplicate it. Content-addressed, so the natural key is
|
|
-- the hash within a project: the same bytes under the same project are the same artifact
|
|
-- however many times they are declared.
|
|
--
|
|
-- Partial on storage_uri rather than unique outright, so two projects may legitimately
|
|
-- reference the same file - which is what happens when a shared library is referenced by
|
|
-- more than one book - while one project cannot end up with two rows for one file.
|
|
CREATE UNIQUE INDEX IF NOT EXISTS artifacts_once_per_project
|
|
ON public.artifacts (project_id, content_hash)
|
|
WHERE storage_uri IS NOT NULL;
|
|
|
|
CREATE INDEX IF NOT EXISTS artifacts_by_node
|
|
ON public.artifacts (storage_node_id)
|
|
WHERE storage_node_id IS NOT NULL;
|
|
|
|
-- Listing a project's catalogue is the only read the console and evaluation tooling do,
|
|
-- and without this it is a sequential scan of every artifact ever ingested.
|
|
CREATE INDEX IF NOT EXISTS artifacts_by_project
|
|
ON public.artifacts (project_id, created_at); |