arimelody.space
home to your local SPACEGIRL 馃挮
arimelody.space
1--
2-- Tables
3--
4
5-- Audit logs
6CREATE TABLE arimelody.auditlog (
7 id UUID DEFAULT gen_random_uuid(),
8 level int NOT NULL DEFAULT 0,
9 type TEXT NOT NULL,
10 content TEXT NOT NULL,
11 created_at TIMESTAMP NOT NULL DEFAULT current_timestamp
12);
13
14-- Accounts
15CREATE TABLE arimelody.account (
16 id UUID DEFAULT gen_random_uuid(),
17 username TEXT NOT NULL UNIQUE,
18 password TEXT NOT NULL,
19 email TEXT,
20 avatar_url TEXT,
21 created_at TIMESTAMP NOT NULL DEFAULT current_timestamp,
22 fail_attempts INT NOT NULL DEFAULT 0,
23 locked BOOLEAN DEFAULT false
24);
25ALTER TABLE arimelody.account ADD CONSTRAINT account_pk PRIMARY KEY (id);
26
27-- Privilege
28CREATE TABLE arimelody.privilege (
29 account UUID NOT NULL,
30 privilege TEXT NOT NULL
31);
32ALTER TABLE arimelody.privilege ADD CONSTRAINT privilege_pk PRIMARY KEY (account, privilege);
33
34-- Invites
35CREATE TABLE arimelody.invite (
36 code text NOT NULL,
37 created_at TIMESTAMP NOT NULL DEFAULT current_timestamp,
38 expires_at TIMESTAMP NOT NULL
39);
40ALTER TABLE arimelody.invite ADD CONSTRAINT invite_pk PRIMARY KEY (code);
41
42-- Sessions
43CREATE TABLE arimelody.session (
44 token TEXT,
45 user_agent TEXT NOT NULL,
46 created_at TIMESTAMP NOT NULL DEFAULT current_timestamp,
47 expires_at TIMESTAMP DEFAULT NULL,
48 account UUID,
49 attempt_account UUID,
50 message TEXT,
51 error TEXT
52);
53ALTER TABLE arimelody.session ADD CONSTRAINT session_pk PRIMARY KEY (token);
54
55-- TOTP methods
56CREATE TABLE arimelody.totp (
57 name TEXT NOT NULL,
58 account UUID NOT NULL,
59 secret TEXT,
60 created_at TIMESTAMP NOT NULL DEFAULT current_timestamp,
61 confirmed BOOLEAN DEFAULT false
62);
63ALTER TABLE arimelody.totp ADD CONSTRAINT totp_pk PRIMARY KEY (account, name);
64
65
66
67-- Artists (should be applicable to all art)
68CREATE TABLE arimelody.artist (
69 id character varying(64),
70 name text NOT NULL,
71 website text,
72 avatar text
73);
74ALTER TABLE arimelody.artist ADD CONSTRAINT artist_pk PRIMARY KEY (id);
75
76-- Music releases
77CREATE TABLE arimelody.musicrelease (
78 id character varying(64) NOT NULL,
79 visible bool DEFAULT false,
80 title text NOT NULL,
81 description text,
82 type text,
83 release_date TIMESTAMP NOT NULL,
84 artwork text,
85 buyname text,
86 buylink text,
87 copyright text,
88 copyrightURL text,
89 created_at TIMESTAMP NOT NULL DEFAULT current_timestamp
90);
91ALTER TABLE arimelody.musicrelease ADD CONSTRAINT musicrelease_pk PRIMARY KEY (id);
92
93-- Music links (external platform links under a release)
94CREATE TABLE arimelody.musiclink (
95 release character varying(64) NOT NULL,
96 name text NOT NULL,
97 url text NOT NULL
98);
99ALTER TABLE arimelody.musiclink ADD CONSTRAINT musiclink_pk PRIMARY KEY (release, name);
100
101-- Music credits (artist credits under a release)
102CREATE TABLE arimelody.musiccredit (
103 release character varying(64) NOT NULL,
104 artist character varying(64) NOT NULL,
105 role text NOT NULL,
106 is_primary boolean DEFAULT false
107);
108ALTER TABLE arimelody.musiccredit ADD CONSTRAINT musiccredit_pk PRIMARY KEY (release, artist);
109
110-- Music tracks (tracks under a release)
111CREATE TABLE arimelody.musictrack (
112 id uuid DEFAULT gen_random_uuid(),
113 title text NOT NULL,
114 description text,
115 lyrics text,
116 preview_url text
117);
118ALTER TABLE arimelody.musictrack ADD CONSTRAINT musictrack_pk PRIMARY KEY (id);
119
120-- Music release/track pairs
121CREATE TABLE arimelody.musicreleasetrack (
122 release character varying(64) NOT NULL,
123 track uuid NOT NULL,
124 number integer NOT NULL
125);
126ALTER TABLE arimelody.musicreleasetrack ADD CONSTRAINT musicreleasetrack_pk PRIMARY KEY (release, track);
127
128
129
130--
131-- Foreign keys
132--
133
134ALTER TABLE arimelody.privilege ADD CONSTRAINT privilege_account_fk FOREIGN KEY (account) REFERENCES account(id) ON DELETE CASCADE;
135ALTER TABLE arimelody.session ADD CONSTRAINT session_account_fk FOREIGN KEY (account) REFERENCES account(id) ON DELETE CASCADE;
136ALTER TABLE arimelody.session ADD CONSTRAINT session_attempt_account_fk FOREIGN KEY (account) REFERENCES account(id) ON DELETE CASCADE;
137ALTER TABLE arimelody.totp ADD CONSTRAINT totp_account_fk FOREIGN KEY (account) REFERENCES account(id) ON DELETE CASCADE;
138
139ALTER TABLE arimelody.musiccredit ADD CONSTRAINT musiccredit_artist_fk FOREIGN KEY (artist) REFERENCES artist(id) ON DELETE CASCADE ON UPDATE CASCADE;
140ALTER TABLE arimelody.musiccredit ADD CONSTRAINT musiccredit_release_fk FOREIGN KEY (release) REFERENCES musicrelease(id) ON DELETE CASCADE;
141ALTER TABLE arimelody.musiclink ADD CONSTRAINT musiclink_release_fk FOREIGN KEY (release) REFERENCES musicrelease(id) ON UPDATE CASCADE ON DELETE CASCADE;
142ALTER TABLE arimelody.musicreleasetrack ADD CONSTRAINT music_pair_trackref_fk FOREIGN KEY (release) REFERENCES musicrelease(id) ON DELETE CASCADE;
143ALTER TABLE arimelody.musicreleasetrack ADD CONSTRAINT music_pair_releaseref_fk FOREIGN KEY (track) REFERENCES musictrack(id) ON DELETE CASCADE;