synthwave/migrations/0000_init.sql

CREATE TYPE user_role AS ENUM ('regular', 'admin');

CREATE TABLE IF NOT EXISTS users (
    username VARCHAR(255) NOT NULL PRIMARY KEY,
    "password" text NOT NULL,
    user_role user_role NOT NULL DEFAULT 'regular'
);

CREATE TABLE IF NOT EXISTS user_session (
    id UUID NOT NULL PRIMARY KEY DEFAULT gen_random_uuid(),
    token text NOT NULL,
    "user" varchar(255) NOT NULL,
    FOREIGN KEY("user") REFERENCES users(username)
);

CREATE TABLE IF NOT EXISTS artist (
    id UUID NOT NULL PRIMARY KEY DEFAULT gen_random_uuid(),
    name text NOT NULL
);

CREATE TABLE IF NOT EXISTS album (
    id UUID NOT NULL PRIMARY KEY DEFAULT gen_random_uuid(),
    title text NOT NULL,
    artist UUID,
    FOREIGN KEY(artist) REFERENCES artist(id)
);

CREATE TABLE IF NOT EXISTS track (
    id UUID NOT NULL PRIMARY KEY DEFAULT gen_random_uuid(),
    path text NOT NULL,
    title text,
    date_added timestamptz NOT NULL DEFAULT current_timestamp,
    album UUID,
    artist UUID,
    meta jsonb,
    FOREIGN KEY(album) REFERENCES album(id),
    FOREIGN KEY(artist) REFERENCES artist(id)
);

CREATE TYPE event_kind AS ENUM ('play', 'played', 'stop');

CREATE TABLE IF NOT EXISTS events (
    id UUID NOT NULL DEFAULT gen_random_uuid(),
    time timestamptz NOT NULL DEFAULT current_timestamp,
    kind event_kind NOT NULL,
    "user" VARCHAR(255) NOT NULL,
    track UUID NOT NULL,
    FOREIGN KEY("user") REFERENCES users(username),
    FOREIGN KEY(track) REFERENCES track(id)
);

SELECT create_hypertable('events', by_range('time'));

CREATE TYPE visibility AS ENUM ('public', 'private');

CREATE TABLE IF NOT EXISTS playlist (
    id UUID NOT NULL PRIMARY KEY DEFAULT gen_random_uuid(),
    owner VARCHAR(255) NOT NULL,
    title text NOT NULL,
    visibility visibility NOT NULL DEFAULT 'private',
    tracks UUID[] NOT NULL DEFAULT '{}',
    FOREIGN KEY(owner) REFERENCES users(username)
);