86627f94b1
Some checks failed
Deploy to NAS / deploy (push) Failing after 26s
- Public feed (/) with infinite scroll via Intersection Observer - Self-registration (/register) - Admin area (/admin/entries, /admin/users) with user management - journal_entries: visibility (public/private) + hashtags fields - users: is_admin flag - DB schema updated (recreate DB to apply) - CI: run go test via docker run (golang:1.25-alpine) — fixes 'go not found' Co-Authored-By: Claude Sonnet 4.6 <noreply@anthropic.com>
99 lines
3.9 KiB
SQL
99 lines
3.9 KiB
SQL
-- Pamietnik database schema
|
|
-- Applied automatically at server startup via CREATE TABLE IF NOT EXISTS.
|
|
|
|
CREATE TABLE IF NOT EXISTS users (
|
|
user_id TEXT PRIMARY KEY DEFAULT gen_random_uuid()::text,
|
|
username TEXT NOT NULL UNIQUE,
|
|
password_hash TEXT NOT NULL,
|
|
is_admin BOOLEAN NOT NULL DEFAULT false,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS sessions (
|
|
session_id TEXT PRIMARY KEY,
|
|
user_id TEXT NOT NULL REFERENCES users(user_id) ON DELETE CASCADE,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
expires_at TIMESTAMPTZ NOT NULL
|
|
);
|
|
CREATE INDEX IF NOT EXISTS sessions_expires_at_idx ON sessions(expires_at);
|
|
|
|
CREATE TABLE IF NOT EXISTS devices (
|
|
device_id TEXT PRIMARY KEY,
|
|
user_id TEXT NOT NULL REFERENCES users(user_id) ON DELETE CASCADE,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS trackpoints (
|
|
id BIGSERIAL PRIMARY KEY,
|
|
event_id TEXT NOT NULL,
|
|
device_id TEXT NOT NULL,
|
|
trip_id TEXT NOT NULL DEFAULT '',
|
|
ts TIMESTAMPTZ NOT NULL,
|
|
lat DOUBLE PRECISION NOT NULL,
|
|
lon DOUBLE PRECISION NOT NULL,
|
|
source TEXT NOT NULL DEFAULT 'gps',
|
|
note TEXT NOT NULL DEFAULT '',
|
|
accuracy_m DOUBLE PRECISION,
|
|
speed_mps DOUBLE PRECISION,
|
|
bearing_deg DOUBLE PRECISION,
|
|
altitude_m DOUBLE PRECISION,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
|
|
CONSTRAINT trackpoints_device_event_uniq UNIQUE (device_id, event_id)
|
|
);
|
|
CREATE INDEX IF NOT EXISTS trackpoints_device_ts_idx ON trackpoints(device_id, ts);
|
|
CREATE INDEX IF NOT EXISTS trackpoints_ts_idx ON trackpoints(ts);
|
|
|
|
CREATE TABLE IF NOT EXISTS stops (
|
|
stop_id TEXT PRIMARY KEY DEFAULT gen_random_uuid()::text,
|
|
device_id TEXT NOT NULL,
|
|
trip_id TEXT NOT NULL DEFAULT '',
|
|
start_ts TIMESTAMPTZ NOT NULL,
|
|
end_ts TIMESTAMPTZ NOT NULL,
|
|
center_lat DOUBLE PRECISION NOT NULL,
|
|
center_lon DOUBLE PRECISION NOT NULL,
|
|
duration_s INT NOT NULL,
|
|
place_label TEXT,
|
|
place_details JSONB,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
|
);
|
|
CREATE INDEX IF NOT EXISTS stops_device_start_ts_idx ON stops(device_id, start_ts);
|
|
|
|
CREATE TABLE IF NOT EXISTS suggestions (
|
|
suggestion_id TEXT PRIMARY KEY DEFAULT gen_random_uuid()::text,
|
|
stop_id TEXT NOT NULL REFERENCES stops(stop_id) ON DELETE CASCADE,
|
|
type TEXT NOT NULL,
|
|
title TEXT NOT NULL DEFAULT '',
|
|
text TEXT NOT NULL DEFAULT '',
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
dismissed_at TIMESTAMPTZ
|
|
);
|
|
CREATE INDEX IF NOT EXISTS suggestions_stop_id_idx ON suggestions(stop_id);
|
|
|
|
CREATE TABLE IF NOT EXISTS journal_entries (
|
|
entry_id TEXT PRIMARY KEY DEFAULT gen_random_uuid()::text,
|
|
user_id TEXT NOT NULL REFERENCES users(user_id) ON DELETE CASCADE,
|
|
entry_date DATE NOT NULL,
|
|
entry_time TIME NOT NULL,
|
|
title TEXT NOT NULL DEFAULT '',
|
|
description TEXT NOT NULL DEFAULT '',
|
|
lat DOUBLE PRECISION,
|
|
lon DOUBLE PRECISION,
|
|
visibility TEXT NOT NULL DEFAULT 'private',
|
|
hashtags TEXT[] NOT NULL DEFAULT '{}',
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
|
);
|
|
CREATE INDEX IF NOT EXISTS journal_entries_user_date_idx ON journal_entries(user_id, entry_date);
|
|
CREATE INDEX IF NOT EXISTS journal_entries_public_idx ON journal_entries(visibility, created_at DESC);
|
|
|
|
CREATE TABLE IF NOT EXISTS journal_images (
|
|
image_id TEXT PRIMARY KEY DEFAULT gen_random_uuid()::text,
|
|
entry_id TEXT NOT NULL REFERENCES journal_entries(entry_id) ON DELETE CASCADE,
|
|
filename TEXT NOT NULL,
|
|
original_name TEXT NOT NULL DEFAULT '',
|
|
mime_type TEXT NOT NULL DEFAULT '',
|
|
size_bytes BIGINT NOT NULL DEFAULT 0,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
|
);
|
|
CREATE INDEX IF NOT EXISTS journal_images_entry_id_idx ON journal_images(entry_id);
|