mrleo1nidandClaude Opus 5.5
ca106e8eb1
- Migration 00008: upload_batches, upload_files, upload_quota. - Quota reserved under a row lock when a batch is created; every status change goes through one transition that frees space in the same transaction. SHA-256 duplicates are rejected before any upload. - tus 1.0.0 core (HEAD/PATCH) for the author only; offset is the real staging file size, data beyond the declared size is refused (ADR-015). - Processing worker: ffprobe checks, SHA-256 verification, exact and Chromaprint duplicates against library and queue, metadata from tags or the dropped folder path, possible_duplicate on metadata match, and the target path from write_template reusing existing folder spelling. - Import worker: edited tags written with ffmpeg stream copy, file written by storage-agent, point scan of the new file, imported status; idempotent and resumed after restart. Stale uploads expire after 24 h, orphan staging files are removed at startup. - API: quota, precheck, batches, my uploads, withdraw, admin list, metadata edit, bulk review, retry and queue settings. Co-Authored-By: Claude Opus 5.5 <noreply@anthropic.com>
69 lines
3.2 KiB
SQL
69 lines
3.2 KiB
SQL
-- +goose Up
|
|
-- Очередь на добавление (docs/uploads.md, docs/data-model.md#очередь-на-добавление-загрузки).
|
|
CREATE TYPE upload_status AS ENUM (
|
|
'uploading', 'processing', 'pending_review', 'approved', 'importing', 'imported',
|
|
'import_failed', 'duplicate', 'invalid', 'rejected', 'withdrawn', 'expired');
|
|
|
|
CREATE TABLE upload_batches (
|
|
id uuid PRIMARY KEY,
|
|
author_id uuid NOT NULL REFERENCES users(id),
|
|
comment text,
|
|
created_at timestamptz NOT NULL DEFAULT now()
|
|
);
|
|
|
|
CREATE TABLE upload_files (
|
|
id uuid PRIMARY KEY,
|
|
batch_id uuid NOT NULL REFERENCES upload_batches(id) ON DELETE CASCADE,
|
|
author_id uuid NOT NULL REFERENCES users(id),
|
|
status upload_status NOT NULL,
|
|
original_name text NOT NULL,
|
|
client_rel_path text,
|
|
size_bytes bigint NOT NULL,
|
|
received_bytes bigint NOT NULL DEFAULT 0, -- смещение tus
|
|
sha256 bytea, -- заявленный клиентом, после обработки — фактический
|
|
staging_path text, -- относительно каталога staging
|
|
format text,
|
|
codec text,
|
|
bitrate int,
|
|
sample_rate int,
|
|
bit_depth int,
|
|
duration_ms int,
|
|
tags jsonb,
|
|
metadata jsonb,
|
|
metadata_edited boolean NOT NULL DEFAULT false,
|
|
fingerprint int[],
|
|
matched_artist_id uuid REFERENCES artists(id) ON DELETE SET NULL,
|
|
matched_album_id uuid REFERENCES albums(id) ON DELETE SET NULL,
|
|
duplicate_of_track_id uuid REFERENCES tracks(id) ON DELETE SET NULL,
|
|
duplicate_level text,
|
|
possible_duplicate boolean NOT NULL DEFAULT false,
|
|
target_source_id uuid REFERENCES library_sources(id) ON DELETE SET NULL,
|
|
target_rel_path text,
|
|
status_reason text,
|
|
reviewed_by uuid REFERENCES users(id) ON DELETE SET NULL,
|
|
reviewed_at timestamptz,
|
|
imported_file_id uuid REFERENCES media_files(id) ON DELETE SET NULL,
|
|
created_at timestamptz NOT NULL DEFAULT now(),
|
|
updated_at timestamptz NOT NULL DEFAULT now()
|
|
);
|
|
CREATE INDEX upload_files_status_idx ON upload_files (status, updated_at);
|
|
CREATE INDEX upload_files_author_idx ON upload_files (author_id, created_at DESC, id DESC);
|
|
CREATE INDEX upload_files_batch_idx ON upload_files (batch_id);
|
|
CREATE INDEX upload_files_sha256_idx ON upload_files (sha256);
|
|
|
|
-- Занятое место очереди (одна строка): меняется в той же транзакции, что и статус.
|
|
CREATE TABLE upload_quota (
|
|
id boolean PRIMARY KEY DEFAULT true CHECK (id),
|
|
used_bytes bigint NOT NULL DEFAULT 0
|
|
);
|
|
INSERT INTO upload_quota (id, used_bytes) VALUES (true, 0);
|
|
|
|
ALTER TABLE media_files ADD FOREIGN KEY (upload_file_id) REFERENCES upload_files(id) ON DELETE SET NULL;
|
|
|
|
-- +goose Down
|
|
ALTER TABLE media_files DROP CONSTRAINT media_files_upload_file_id_fkey;
|
|
DROP TABLE upload_quota;
|
|
DROP TABLE upload_files;
|
|
DROP TABLE upload_batches;
|
|
DROP TYPE upload_status;
|