CREATE TABLE IF NOT EXISTS cache_tags_backfills (
    id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    crate_id INTEGER REFERENCES crates(id) ON DELETE SET NULL,
    crate_name TEXT NOT NULL,
    completed_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
);

COMMENT ON TABLE cache_tags_backfills IS
    'Records which crates have had their S3 objects backfilled with `cache-tags` metadata. A row exists only for crates whose backfill completed.';
COMMENT ON COLUMN cache_tags_backfills.id IS
    'Unique identifier for each backfill record.';
COMMENT ON COLUMN cache_tags_backfills.crate_id IS
    'The backfilled crate, or `NULL` if the crate was deleted after the backfill completed.';
COMMENT ON COLUMN cache_tags_backfills.crate_name IS
    'The crate name at backfill time.';
COMMENT ON COLUMN cache_tags_backfills.completed_at IS
    'Timestamp when the backfill for this crate completed.';

-- safety-assured:start
-- Suppresses the "ADD INDEX without CONCURRENTLY" diesel-guard check.
-- The table is empty immediately after recreation, so the unique index builds
-- without meaningful SHARE lock contention.
CREATE UNIQUE INDEX idx_cache_tags_backfills_crate_id ON cache_tags_backfills (crate_id);
-- safety-assured:end
