mirror of
https://github.com/wahyd4/heygo.git
synced 2026-08-08 21:05:48 +10:00
97 lines
3.3 KiB
SQL
97 lines
3.3 KiB
SQL
CREATE TABLE users (
|
|
id TEXT PRIMARY KEY,
|
|
email TEXT UNIQUE,
|
|
name TEXT,
|
|
image_url TEXT,
|
|
role TEXT NOT NULL DEFAULT 'user' CHECK (role IN ('user', 'admin')),
|
|
created_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ', 'now')),
|
|
updated_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ', 'now'))
|
|
);
|
|
|
|
CREATE TABLE oauth_accounts (
|
|
id TEXT PRIMARY KEY,
|
|
user_id TEXT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
|
|
provider TEXT NOT NULL CHECK (provider IN ('google', 'github', 'apple')),
|
|
provider_account_id TEXT NOT NULL,
|
|
created_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ', 'now')),
|
|
UNIQUE(provider, provider_account_id)
|
|
);
|
|
|
|
CREATE TABLE sessions (
|
|
id TEXT PRIMARY KEY,
|
|
user_id TEXT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
|
|
session_token_hash TEXT NOT NULL UNIQUE,
|
|
expires_at TEXT NOT NULL,
|
|
created_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ', 'now'))
|
|
);
|
|
|
|
CREATE TABLE links (
|
|
id TEXT PRIMARY KEY,
|
|
scope TEXT NOT NULL CHECK (scope IN ('public', 'private')),
|
|
owner_user_id TEXT REFERENCES users(id) ON DELETE CASCADE,
|
|
alias TEXT NOT NULL,
|
|
link_type TEXT NOT NULL CHECK (link_type IN ('redirect', 'custom')),
|
|
target_url TEXT,
|
|
content_markdown TEXT,
|
|
description TEXT,
|
|
status TEXT NOT NULL DEFAULT 'active' CHECK (status IN ('active', 'archived', 'deleted')),
|
|
click_count INTEGER NOT NULL DEFAULT 0,
|
|
created_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ', 'now')),
|
|
updated_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ', 'now')),
|
|
CHECK (
|
|
(scope = 'public' AND owner_user_id IS NULL)
|
|
OR (scope = 'private' AND owner_user_id IS NOT NULL)
|
|
),
|
|
CHECK (
|
|
(link_type = 'redirect' AND target_url IS NOT NULL)
|
|
OR (link_type = 'custom' AND content_markdown IS NOT NULL)
|
|
)
|
|
);
|
|
|
|
CREATE UNIQUE INDEX links_public_alias_unique
|
|
ON links(alias)
|
|
WHERE scope = 'public' AND status != 'deleted';
|
|
|
|
CREATE UNIQUE INDEX links_private_owner_alias_unique
|
|
ON links(owner_user_id, alias)
|
|
WHERE scope = 'private' AND status != 'deleted';
|
|
|
|
CREATE INDEX links_owner_idx ON links(owner_user_id, updated_at DESC);
|
|
CREATE INDEX links_scope_updated_idx ON links(scope, updated_at DESC);
|
|
|
|
CREATE TABLE tags (
|
|
id TEXT PRIMARY KEY,
|
|
slug TEXT NOT NULL UNIQUE,
|
|
name TEXT NOT NULL,
|
|
created_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ', 'now'))
|
|
);
|
|
|
|
CREATE TABLE link_tags (
|
|
link_id TEXT NOT NULL REFERENCES links(id) ON DELETE CASCADE,
|
|
tag_id TEXT NOT NULL REFERENCES tags(id) ON DELETE CASCADE,
|
|
PRIMARY KEY (link_id, tag_id)
|
|
);
|
|
|
|
CREATE TABLE promotion_submissions (
|
|
id TEXT PRIMARY KEY,
|
|
private_link_id TEXT NOT NULL REFERENCES links(id) ON DELETE CASCADE,
|
|
submitted_by_user_id TEXT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
|
|
proposed_alias TEXT NOT NULL,
|
|
note TEXT,
|
|
status TEXT NOT NULL DEFAULT 'pending' CHECK (status IN ('pending', 'approved', 'rejected', 'needs_changes')),
|
|
reviewed_by_user_id TEXT REFERENCES users(id),
|
|
rejection_reason TEXT,
|
|
public_link_id TEXT REFERENCES links(id),
|
|
created_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ', 'now')),
|
|
reviewed_at TEXT
|
|
);
|
|
|
|
CREATE INDEX promotion_status_idx ON promotion_submissions(status, created_at DESC);
|
|
|
|
CREATE TABLE click_daily (
|
|
link_id TEXT NOT NULL REFERENCES links(id) ON DELETE CASCADE,
|
|
day TEXT NOT NULL,
|
|
count INTEGER NOT NULL DEFAULT 0,
|
|
PRIMARY KEY (link_id, day)
|
|
);
|