53 lines
2.2 KiB
SQL
53 lines
2.2 KiB
SQL
-- TACHYON site schema
|
|
-- SQLite. Timestamps stored as ISO-8601 strings (TEXT), sorted lexically = chronologically.
|
|
|
|
CREATE TABLE IF NOT EXISTS news_posts (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
title TEXT NOT NULL,
|
|
content TEXT NOT NULL,
|
|
created_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ', 'now'))
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS media_items (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
title TEXT NOT NULL,
|
|
media_type TEXT NOT NULL CHECK (media_type IN ('image', 'video')),
|
|
url TEXT NOT NULL,
|
|
created_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ', 'now'))
|
|
);
|
|
|
|
-- Registered/tracked users (distinct from "who is currently online" -- see sessions below)
|
|
CREATE TABLE IF NOT EXISTS users (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
username TEXT NOT NULL UNIQUE,
|
|
joined_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ', 'now')),
|
|
last_seen_at TEXT
|
|
);
|
|
|
|
-- One row per active session; presence of a row (or non-null ended_at) drives "current users"
|
|
CREATE TABLE IF NOT EXISTS sessions (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
|
|
started_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ', 'now')),
|
|
ended_at TEXT
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS activity_log (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
description TEXT NOT NULL,
|
|
created_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ', 'now'))
|
|
);
|
|
|
|
-- Freeform key/value store for the "General Statistics" cards (build number, version, etc.)
|
|
CREATE TABLE IF NOT EXISTS site_stats (
|
|
key TEXT PRIMARY KEY,
|
|
value TEXT NOT NULL,
|
|
updated_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ', 'now'))
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_news_created_at ON news_posts(created_at DESC);
|
|
CREATE INDEX IF NOT EXISTS idx_media_created_at ON media_items(created_at DESC);
|
|
CREATE INDEX IF NOT EXISTS idx_activity_created_at ON activity_log(created_at DESC);
|
|
CREATE INDEX IF NOT EXISTS idx_sessions_user ON sessions(user_id);
|
|
CREATE INDEX IF NOT EXISTS idx_sessions_active ON sessions(ended_at);
|