home to your local SPACEGIRL 馃挮
arimelody.space
1--
2-- New items
3--
4
5-- Accounts
6CREATE TABLE arimelody.account (
7 id UUID DEFAULT gen_random_uuid(),
8 username TEXT NOT NULL UNIQUE,
9 password TEXT NOT NULL,
10 email TEXT,
11 avatar_url TEXT,
12 created_at TIMESTAMP DEFAULT current_timestamp
13);
14ALTER TABLE arimelody.account ADD CONSTRAINT account_pk PRIMARY KEY (id);
15
16-- Privilege
17CREATE TABLE arimelody.privilege (
18 account UUID NOT NULL,
19 privilege TEXT NOT NULL
20);
21ALTER TABLE arimelody.privilege ADD CONSTRAINT privilege_pk PRIMARY KEY (account, privilege);
22
23-- Invites
24CREATE TABLE arimelody.invite (
25 code text NOT NULL,
26 created_at TIMESTAMP NOT NULL DEFAULT current_timestamp,
27 expires_at TIMESTAMP NOT NULL
28);
29ALTER TABLE arimelody.invite ADD CONSTRAINT invite_pk PRIMARY KEY (code);
30
31-- Sessions
32CREATE TABLE arimelody.session (
33 token TEXT,
34 user_agent TEXT NOT NULL,
35 created_at TIMESTAMP NOT NULL DEFAULT current_timestamp,
36 expires_at TIMESTAMP DEFAULT NULL,
37 account UUID,
38 attempt_account UUID,
39 message TEXT,
40 error TEXT
41);
42ALTER TABLE arimelody.session ADD CONSTRAINT session_pk PRIMARY KEY (token);
43
44-- TOTP methods
45CREATE TABLE arimelody.totp (
46 name TEXT NOT NULL,
47 account UUID NOT NULL,
48 secret TEXT,
49 created_at TIMESTAMP NOT NULL DEFAULT current_timestamp,
50 confirmed BOOLEAN DEFAULT false
51);
52ALTER TABLE arimelody.totp ADD CONSTRAINT totp_pk PRIMARY KEY (account, name);
53
54-- Foreign keys
55ALTER TABLE arimelody.privilege ADD CONSTRAINT privilege_account_fk FOREIGN KEY (account) REFERENCES account(id) ON DELETE CASCADE;
56ALTER TABLE arimelody.session ADD CONSTRAINT session_account_fk FOREIGN KEY (account) REFERENCES account(id) ON DELETE CASCADE;
57ALTER TABLE arimelody.session ADD CONSTRAINT session_attempt_account_fk FOREIGN KEY (account) REFERENCES account(id) ON DELETE CASCADE;
58ALTER TABLE arimelody.totp ADD CONSTRAINT totp_account_fk FOREIGN KEY (account) REFERENCES account(id) ON DELETE CASCADE;