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(
 9    username   TEXT      NOT NULL PRIMARY KEY,
10    hash       TEXT      NOT NULL,
11    salt       TEXT      NOT NULL,
12    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
13);
14
15-- Create table of sessions with session GUID, username, and timestamp of when
16-- the session was created
17CREATE TABLE sessions
18(
19    token      TEXT      NOT NULL PRIMARY KEY,
20    username   TEXT      NOT NULL,
21    expires    TIMESTAMP NOT NULL,
22    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
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(
29    url        TEXT      NOT NULL PRIMARY KEY,
30    name       TEXT      NOT NULL,
31    forge      TEXT      NOT NULL,
32    version    TEXT      NOT NULL,
33    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
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(
40    id          TEXT      NOT NULL PRIMARY KEY,
41    project_url TEXT      NOT NULL,
42    release_url TEXT      NOT NULL,
43    tag         TEXT      NOT NULL,
44    content     TEXT      NOT NULL,
45    date        TIMESTAMP NOT NULL,
46    created_at  TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
47);