ae
62b1a58e56
- build: somewhat polished dockerization setup - build: io/fs migrations with `golang-migrate` - feat: automatic init. admin account creation (.env creds) - feat(routers): combined user & token routers into single auth router - feat(routers): improved route layouts (`Routes`) - feat(middlewares): removed redundant `userCtx` middleware - fix(schema): note <-> note_versions relation (versioning) - feat(queries): removed redundant rollback functionality - feat(queries): combined duplicate version check & insertion/creation - tests: decreased redundancy by removing 'unnecessary' unit tests - refactor: hid internal packages behind `server/internal` - docs: notes & auth handler comments
50 lines
1.9 KiB
SQL
50 lines
1.9 KiB
SQL
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
|
|
|
|
CREATE TABLE IF NOT EXISTS users (
|
|
id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
|
|
username TEXT UNIQUE NOT NULL,
|
|
password_hash TEXT NOT NULL,
|
|
is_admin BOOLEAN NOT NULL DEFAULT false,
|
|
created_at TIMESTAMPTZ DEFAULT NOW(),
|
|
updated_at TIMESTAMPTZ DEFAULT NOW()
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS refresh_tokens (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
|
|
token_hash TEXT NOT NULL,
|
|
expires_at TIMESTAMPTZ NOT NULL,
|
|
created_at TIMESTAMPTZ DEFAULT NOW(),
|
|
revoked BOOLEAN NOT NULL DEFAULT false
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS notes (
|
|
id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
|
|
user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
|
|
current_version INT NOT NULL DEFAULT 1, -- active version (can be historical)
|
|
latest_version INT NOT NULL DEFAULT 1, -- highest version number
|
|
created_at TIMESTAMPTZ DEFAULT NOW(),
|
|
updated_at TIMESTAMPTZ DEFAULT NOW()
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS note_versions (
|
|
id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
|
|
note_id UUID NOT NULL REFERENCES notes(id) ON DELETE CASCADE,
|
|
title TEXT NOT NULL,
|
|
content TEXT NOT NULL,
|
|
version_number INT NOT NULL DEFAULT 1,
|
|
content_hash TEXT NOT NULL,
|
|
created_at TIMESTAMPTZ DEFAULT NOW(),
|
|
UNIQUE (note_id, version_number)
|
|
);
|
|
|
|
CREATE UNIQUE INDEX IF NOT EXISTS idx_users_username ON users(username);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_refresh_tokens_user_id ON refresh_tokens(user_id);
|
|
CREATE INDEX IF NOT EXISTS idx_refresh_tokens_expires_at ON refresh_tokens(expires_at);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_notes_user_updated ON notes(user_id, updated_at DESC);
|
|
CREATE INDEX IF NOT EXISTS idx_notes_current_version ON notes(current_version);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_note_versions_content_hash ON note_versions(note_id, content_hash);
|