xinmi/migrations/versioned/000201_resumable_uploads.up.sql

CREATE TABLE IF NOT EXISTS resumable_uploads (
 id VARCHAR(36) PRIMARY KEY,
 tenant_id BIGINT NOT NULL REFERENCES tenants(id),
 owner_id VARCHAR(36) NOT NULL REFERENCES users(id),
 kind VARCHAR(32) NOT NULL CHECK(kind IN ('knowledge','digital-human')),
 target_id VARCHAR(36) NOT NULL,
 name TEXT NOT NULL,
 size BIGINT NOT NULL CHECK(size > 0 AND size <= 21474836480),
 "offset" BIGINT NOT NULL DEFAULT 0,
 chunk_size BIGINT NOT NULL DEFAULT 4194304,
 sha256 VARCHAR(64) NOT NULL DEFAULT '',
 fingerprint VARCHAR(128) NOT NULL DEFAULT '',
 state VARCHAR(24) NOT NULL DEFAULT 'uploading',
 error TEXT NOT NULL DEFAULT '',
 options JSONB NOT NULL DEFAULT '{}',
 result JSONB NOT NULL DEFAULT '{}',
 ingest_offset BIGINT NOT NULL DEFAULT 0,
 part INTEGER NOT NULL DEFAULT 0,
 expires_at TIMESTAMPTZ NOT NULL,
 lease_until TIMESTAMPTZ,
 lease_token VARCHAR(36) NOT NULL DEFAULT '',
 created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
 updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
CREATE INDEX IF NOT EXISTS resumable_upload_owner ON resumable_uploads(tenant_id,owner_id,created_at DESC);
CREATE INDEX IF NOT EXISTS resumable_upload_queue ON resumable_uploads(state,lease_until,created_at);
CREATE INDEX IF NOT EXISTS resumable_upload_expiry ON resumable_uploads(expires_at);
CREATE TABLE IF NOT EXISTS resumable_upload_chunks (
 upload_id VARCHAR(36) NOT NULL REFERENCES resumable_uploads(id) ON DELETE CASCADE,
 "offset" BIGINT NOT NULL,
 size BIGINT NOT NULL CHECK(size > 0 AND size <= 4194304),
 sha256 VARCHAR(64) NOT NULL,
 PRIMARY KEY(upload_id,"offset")
);