21 lines
921 B
SQL
21 lines
921 B
SQL
-- Community-reported broken mods. Each (workshop_id, version) is unique;
|
|
-- re-submitting the same pair upserts (refreshes updated_at) while
|
|
-- preserving accumulated votes. ORDER BY updated_at DESC drives the
|
|
-- list view, so a re-report bubbles the entry back to the top with
|
|
-- previous up/down counts intact.
|
|
CREATE TABLE IF NOT EXISTS broken_mod_reports (
|
|
id BIGSERIAL PRIMARY KEY,
|
|
workshop_id TEXT NOT NULL,
|
|
mod_name TEXT,
|
|
version TEXT NOT NULL,
|
|
upvotes INTEGER NOT NULL DEFAULT 0,
|
|
downvotes INTEGER NOT NULL DEFAULT 0,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
UNIQUE (workshop_id, version)
|
|
);
|
|
CREATE INDEX IF NOT EXISTS broken_mod_reports_updated_idx
|
|
ON broken_mod_reports (updated_at DESC);
|
|
CREATE INDEX IF NOT EXISTS broken_mod_reports_wsid_idx
|
|
ON broken_mod_reports (workshop_id);
|