1-- SPDX-FileCopyrightText: Amolith <amolith@secluded.site>
 2--
 3-- SPDX-License-Identifier: CC0-1.0
 4
 5-- Create table of users with username, password hash, salt, and creation
 6-- timestamp
 7CREATE TABLE users (
 8    username       VARCHAR(255)  NOT         NULL,
 9    hash           VARCHAR(255)  NOT         NULL,
10    salt           VARCHAR(255)  NOT         NULL,
11    created_at     TIMESTAMP     NOT         NULL   DEFAULT  CURRENT_TIMESTAMP,
12    PRIMARY        KEY           (username)
13);
14
15-- Create table of sessions with session GUID, username, and timestamp of when
16-- the session was created
17CREATE TABLE sessions (
18    token        VARCHAR(255)  NOT          NULL,
19    username     VARCHAR(255)  NOT          NULL,
20    expires      TIMESTAMP     NOT          NULL,
21    created_at   TIMESTAMP     NOT          NULL   DEFAULT  CURRENT_TIMESTAMP,
22    PRIMARY      KEY           (token)
23);
24
25-- Create table of tracked projects with URL, name, forge, running version, and
26-- timestamp of when the project was added
27CREATE TABLE projects (
28    url         VARCHAR(255)  NOT    NULL,
29    name        VARCHAR(255)  NOT    NULL,
30    forge       VARCHAR(255)  NOT    NULL,
31    version     VARCHAR(255)  NOT    NULL,
32    created_at  TIMESTAMP     NOT    NULL   DEFAULT  CURRENT_TIMESTAMP,
33    PRIMARY     KEY           (url)
34);
35
36-- Create table of project releases with the project URL and the release tags,
37-- contents, URLs, and dates
38CREATE TABLE releases (
39    project_url  VARCHAR(255)  NOT    NULL,
40    release_url  VARCHAR(255)  NOT    NULL,
41    tag          VARCHAR(255)  NOT    NULL,
42    content      TEXT          NOT    NULL,
43    date         TIMESTAMP     NOT    NULL,
44    created_at   TIMESTAMP     NOT    NULL   DEFAULT  CURRENT_TIMESTAMP,
45    PRIMARY      KEY           (release_url)
46);