Something went wrong. Try again.
home to your local SPACEGIRL 💫 arimelody.space
Something went wrong. Try again.
4.9 kB · 143 lines
SQL
at dev
123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144---- Tables--
-- Audit logsCREATE TABLE arimelody.auditlog ( id UUID DEFAULT gen_random_uuid(), level int NOT NULL DEFAULT 0, type TEXT NOT NULL, content TEXT NOT NULL, created_at TIMESTAMP NOT NULL DEFAULT current_timestamp);
-- AccountsCREATE TABLE arimelody.account ( id UUID DEFAULT gen_random_uuid(), username TEXT NOT NULL UNIQUE, password TEXT NOT NULL, email TEXT, avatar_url TEXT, created_at TIMESTAMP NOT NULL DEFAULT current_timestamp, fail_attempts INT NOT NULL DEFAULT 0, locked BOOLEAN DEFAULT false);ALTER TABLE arimelody.account ADD CONSTRAINT account_pk PRIMARY KEY (id);
-- PrivilegeCREATE TABLE arimelody.privilege ( account UUID NOT NULL, privilege TEXT NOT NULL);ALTER TABLE arimelody.privilege ADD CONSTRAINT privilege_pk PRIMARY KEY (account, privilege);
-- InvitesCREATE TABLE arimelody.invite ( code text NOT NULL, created_at TIMESTAMP NOT NULL DEFAULT current_timestamp, expires_at TIMESTAMP NOT NULL);ALTER TABLE arimelody.invite ADD CONSTRAINT invite_pk PRIMARY KEY (code);
-- SessionsCREATE TABLE arimelody.session ( token TEXT, user_agent TEXT NOT NULL, created_at TIMESTAMP NOT NULL DEFAULT current_timestamp, expires_at TIMESTAMP DEFAULT NULL, account UUID, attempt_account UUID, message TEXT, error TEXT);ALTER TABLE arimelody.session ADD CONSTRAINT session_pk PRIMARY KEY (token);
-- TOTP methodsCREATE TABLE arimelody.totp ( name TEXT NOT NULL, account UUID NOT NULL, secret TEXT, created_at TIMESTAMP NOT NULL DEFAULT current_timestamp, confirmed BOOLEAN DEFAULT false);ALTER TABLE arimelody.totp ADD CONSTRAINT totp_pk PRIMARY KEY (account, name);
-- Artists (should be applicable to all art)CREATE TABLE arimelody.artist ( id character varying(64), name text NOT NULL, website text, avatar text);ALTER TABLE arimelody.artist ADD CONSTRAINT artist_pk PRIMARY KEY (id);
-- Music releasesCREATE TABLE arimelody.musicrelease ( id character varying(64) NOT NULL, visible bool DEFAULT false, title text NOT NULL, description text, type text, release_date TIMESTAMP NOT NULL, artwork text, buyname text, buylink text, copyright text, copyrightURL text, created_at TIMESTAMP NOT NULL DEFAULT current_timestamp);ALTER TABLE arimelody.musicrelease ADD CONSTRAINT musicrelease_pk PRIMARY KEY (id);
-- Music links (external platform links under a release)CREATE TABLE arimelody.musiclink ( release character varying(64) NOT NULL, name text NOT NULL, url text NOT NULL);ALTER TABLE arimelody.musiclink ADD CONSTRAINT musiclink_pk PRIMARY KEY (release, name);
-- Music credits (artist credits under a release)CREATE TABLE arimelody.musiccredit ( release character varying(64) NOT NULL, artist character varying(64) NOT NULL, role text NOT NULL, is_primary boolean DEFAULT false);ALTER TABLE arimelody.musiccredit ADD CONSTRAINT musiccredit_pk PRIMARY KEY (release, artist);
-- Music tracks (tracks under a release)CREATE TABLE arimelody.musictrack ( id uuid DEFAULT gen_random_uuid(), title text NOT NULL, description text, lyrics text, preview_url text);ALTER TABLE arimelody.musictrack ADD CONSTRAINT musictrack_pk PRIMARY KEY (id);
-- Music release/track pairsCREATE TABLE arimelody.musicreleasetrack ( release character varying(64) NOT NULL, track uuid NOT NULL, number integer NOT NULL);ALTER TABLE arimelody.musicreleasetrack ADD CONSTRAINT musicreleasetrack_pk PRIMARY KEY (release, track);
---- Foreign keys--
ALTER TABLE arimelody.privilege ADD CONSTRAINT privilege_account_fk FOREIGN KEY (account) REFERENCES account(id) ON DELETE CASCADE;ALTER TABLE arimelody.session ADD CONSTRAINT session_account_fk FOREIGN KEY (account) REFERENCES account(id) ON DELETE CASCADE;ALTER TABLE arimelody.session ADD CONSTRAINT session_attempt_account_fk FOREIGN KEY (account) REFERENCES account(id) ON DELETE CASCADE;ALTER TABLE arimelody.totp ADD CONSTRAINT totp_account_fk FOREIGN KEY (account) REFERENCES account(id) ON DELETE CASCADE;
ALTER TABLE arimelody.musiccredit ADD CONSTRAINT musiccredit_artist_fk FOREIGN KEY (artist) REFERENCES artist(id) ON DELETE CASCADE ON UPDATE CASCADE;ALTER TABLE arimelody.musiccredit ADD CONSTRAINT musiccredit_release_fk FOREIGN KEY (release) REFERENCES musicrelease(id) ON DELETE CASCADE;ALTER TABLE arimelody.musiclink ADD CONSTRAINT musiclink_release_fk FOREIGN KEY (release) REFERENCES musicrelease(id) ON UPDATE CASCADE ON DELETE CASCADE;ALTER TABLE arimelody.musicreleasetrack ADD CONSTRAINT music_pair_trackref_fk FOREIGN KEY (release) REFERENCES musicrelease(id) ON DELETE CASCADE;ALTER TABLE arimelody.musicreleasetrack ADD CONSTRAINT music_pair_releaseref_fk FOREIGN KEY (track) REFERENCES musictrack(id) ON DELETE CASCADE;