308 lines
11 KiB
SQL
308 lines
11 KiB
SQL
PRAGMA foreign_keys = ON;
|
|
|
|
CREATE TABLE IF NOT EXISTS users (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
public_id TEXT NOT NULL UNIQUE,
|
|
username TEXT NOT NULL UNIQUE,
|
|
display_name TEXT NOT NULL,
|
|
email TEXT NOT NULL,
|
|
password_hash TEXT,
|
|
is_admin INTEGER NOT NULL DEFAULT 0,
|
|
auth_source TEXT NOT NULL DEFAULT 'db',
|
|
created_at TIMESTAMP NOT NULL,
|
|
updated_at TIMESTAMP NOT NULL,
|
|
disabled INTEGER NOT NULL DEFAULT 0
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS sessions (
|
|
id TEXT PRIMARY KEY,
|
|
user_id INTEGER NOT NULL,
|
|
token TEXT NOT NULL UNIQUE,
|
|
expires_at TIMESTAMP NOT NULL,
|
|
created_at TIMESTAMP NOT NULL,
|
|
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS projects (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
public_id TEXT NOT NULL UNIQUE,
|
|
slug TEXT NOT NULL UNIQUE,
|
|
name TEXT NOT NULL,
|
|
description TEXT NOT NULL,
|
|
home_page TEXT NOT NULL DEFAULT 'info',
|
|
created_by INTEGER NOT NULL,
|
|
updated_by INTEGER NOT NULL DEFAULT 0,
|
|
created_at TIMESTAMP NOT NULL,
|
|
updated_at TIMESTAMP NOT NULL,
|
|
created_at_unix INTEGER NOT NULL DEFAULT 0,
|
|
updated_at_unix INTEGER NOT NULL DEFAULT 0,
|
|
FOREIGN KEY (created_by) REFERENCES users(id)
|
|
FOREIGN KEY (updated_by) REFERENCES users(id)
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS project_members (
|
|
project_id INTEGER NOT NULL,
|
|
user_id INTEGER NOT NULL,
|
|
role TEXT NOT NULL,
|
|
created_at TIMESTAMP NOT NULL,
|
|
PRIMARY KEY (project_id, user_id),
|
|
FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE,
|
|
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS repos (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
public_id TEXT NOT NULL UNIQUE,
|
|
project_id INTEGER NOT NULL,
|
|
name TEXT NOT NULL,
|
|
type TEXT NOT NULL DEFAULT 'git',
|
|
path TEXT NOT NULL,
|
|
created_by INTEGER NOT NULL,
|
|
created_at TIMESTAMP NOT NULL,
|
|
FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE,
|
|
FOREIGN KEY (created_by) REFERENCES users(id)
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS issues (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
public_id TEXT NOT NULL UNIQUE,
|
|
project_id INTEGER NOT NULL,
|
|
title TEXT NOT NULL,
|
|
body TEXT NOT NULL,
|
|
status TEXT NOT NULL,
|
|
created_by INTEGER NOT NULL,
|
|
assignee_id INTEGER,
|
|
created_at TIMESTAMP NOT NULL,
|
|
updated_at TIMESTAMP NOT NULL,
|
|
FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE,
|
|
FOREIGN KEY (created_by) REFERENCES users(id),
|
|
FOREIGN KEY (assignee_id) REFERENCES users(id)
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS issue_comments (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
public_id TEXT NOT NULL UNIQUE,
|
|
issue_id INTEGER NOT NULL,
|
|
body TEXT NOT NULL,
|
|
created_by INTEGER NOT NULL,
|
|
created_at TIMESTAMP NOT NULL,
|
|
FOREIGN KEY (issue_id) REFERENCES issues(id) ON DELETE CASCADE,
|
|
FOREIGN KEY (created_by) REFERENCES users(id)
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS wiki_pages (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
public_id TEXT NOT NULL UNIQUE,
|
|
project_id INTEGER NOT NULL,
|
|
title TEXT NOT NULL,
|
|
slug TEXT NOT NULL,
|
|
body TEXT NOT NULL,
|
|
created_by INTEGER NOT NULL,
|
|
updated_at TIMESTAMP NOT NULL,
|
|
UNIQUE (project_id, slug),
|
|
FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE,
|
|
FOREIGN KEY (created_by) REFERENCES users(id)
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS uploads (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
public_id TEXT NOT NULL UNIQUE,
|
|
project_id INTEGER NOT NULL,
|
|
filename TEXT NOT NULL,
|
|
content_type TEXT NOT NULL,
|
|
size INTEGER NOT NULL,
|
|
storage_path TEXT NOT NULL,
|
|
created_by INTEGER NOT NULL,
|
|
created_at TIMESTAMP NOT NULL,
|
|
FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE,
|
|
FOREIGN KEY (created_by) REFERENCES users(id)
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS project_repos (
|
|
project_id INTEGER NOT NULL,
|
|
repo_id INTEGER NOT NULL,
|
|
created_at TIMESTAMP NOT NULL,
|
|
PRIMARY KEY (project_id, repo_id),
|
|
FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE,
|
|
FOREIGN KEY (repo_id) REFERENCES repos(id) ON DELETE CASCADE
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS app_settings (
|
|
key TEXT PRIMARY KEY,
|
|
value TEXT NOT NULL,
|
|
updated_at INTEGER NOT NULL
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS api_keys (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
public_id TEXT NOT NULL UNIQUE,
|
|
user_id INTEGER NOT NULL,
|
|
name TEXT NOT NULL,
|
|
token_hash TEXT NOT NULL,
|
|
token_prefix TEXT NOT NULL,
|
|
created_at INTEGER NOT NULL,
|
|
last_used_at INTEGER NOT NULL DEFAULT 0,
|
|
expires_at INTEGER NOT NULL DEFAULT 0,
|
|
disabled INTEGER NOT NULL DEFAULT 0,
|
|
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS pki_cas (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
public_id TEXT NOT NULL UNIQUE,
|
|
name TEXT NOT NULL UNIQUE,
|
|
parent_ca_id INTEGER,
|
|
is_root INTEGER NOT NULL DEFAULT 0,
|
|
cert_pem TEXT NOT NULL,
|
|
key_pem TEXT NOT NULL,
|
|
serial_counter INTEGER NOT NULL DEFAULT 1,
|
|
status TEXT NOT NULL DEFAULT 'active',
|
|
created_at INTEGER NOT NULL,
|
|
updated_at INTEGER NOT NULL,
|
|
FOREIGN KEY(parent_ca_id) REFERENCES pki_cas(id)
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS pki_certs (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
public_id TEXT NOT NULL UNIQUE,
|
|
ca_id INTEGER,
|
|
serial_hex TEXT NOT NULL,
|
|
common_name TEXT NOT NULL,
|
|
san_dns TEXT NOT NULL DEFAULT '',
|
|
san_ips TEXT NOT NULL DEFAULT '',
|
|
is_ca INTEGER NOT NULL DEFAULT 0,
|
|
cert_pem TEXT NOT NULL,
|
|
key_pem TEXT NOT NULL,
|
|
not_before INTEGER NOT NULL,
|
|
not_after INTEGER NOT NULL,
|
|
status TEXT NOT NULL DEFAULT 'active',
|
|
revoked_at INTEGER NOT NULL DEFAULT 0,
|
|
revocation_reason TEXT NOT NULL DEFAULT '',
|
|
created_at INTEGER NOT NULL,
|
|
FOREIGN KEY(ca_id) REFERENCES pki_cas(id) ON DELETE CASCADE,
|
|
UNIQUE(ca_id, serial_hex)
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS tls_listeners (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
public_id TEXT NOT NULL UNIQUE,
|
|
name TEXT NOT NULL UNIQUE,
|
|
enabled INTEGER NOT NULL DEFAULT 1,
|
|
http_addrs TEXT NOT NULL DEFAULT '',
|
|
https_addrs TEXT NOT NULL DEFAULT '',
|
|
tls_server_cert_source TEXT NOT NULL DEFAULT 'files',
|
|
tls_cert_file TEXT NOT NULL DEFAULT '',
|
|
tls_key_file TEXT NOT NULL DEFAULT '',
|
|
tls_pki_server_cert_id TEXT NOT NULL DEFAULT '',
|
|
tls_client_auth TEXT NOT NULL DEFAULT 'none',
|
|
tls_client_ca_file TEXT NOT NULL DEFAULT '',
|
|
tls_pki_client_ca_id TEXT NOT NULL DEFAULT '',
|
|
tls_min_version TEXT NOT NULL DEFAULT '1.2',
|
|
created_at INTEGER NOT NULL,
|
|
updated_at INTEGER NOT NULL,
|
|
auth_policy TEXT NOT NULL DEFAULT 'default',
|
|
apply_policy_api INTEGER NOT NULL DEFAULT 0,
|
|
apply_policy_git INTEGER NOT NULL DEFAULT 0,
|
|
apply_policy_rpm INTEGER NOT NULL DEFAULT 0,
|
|
apply_policy_v2 INTEGER NOT NULL DEFAULT 0,
|
|
client_cert_allowlist TEXT NOT NULL DEFAULT ''
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS service_principals (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
public_id TEXT NOT NULL UNIQUE,
|
|
name TEXT NOT NULL UNIQUE,
|
|
description TEXT NOT NULL DEFAULT '',
|
|
disabled INTEGER NOT NULL DEFAULT 0,
|
|
created_at INTEGER NOT NULL,
|
|
updated_at INTEGER NOT NULL,
|
|
is_admin INTEGER NOT NULL DEFAULT 0
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS cert_principal_bindings (
|
|
fingerprint TEXT PRIMARY KEY,
|
|
principal_id INTEGER NOT NULL,
|
|
enabled INTEGER NOT NULL DEFAULT 1,
|
|
created_at INTEGER NOT NULL,
|
|
updated_at INTEGER NOT NULL,
|
|
FOREIGN KEY (principal_id) REFERENCES service_principals(id) ON DELETE CASCADE
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS principal_project_roles (
|
|
principal_id INTEGER NOT NULL,
|
|
project_id INTEGER NOT NULL,
|
|
role TEXT NOT NULL,
|
|
created_at INTEGER NOT NULL,
|
|
PRIMARY KEY (principal_id, project_id),
|
|
FOREIGN KEY (principal_id) REFERENCES service_principals(id) ON DELETE CASCADE,
|
|
FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS rpm_repo_dirs (
|
|
repo_id INTEGER NOT NULL,
|
|
path TEXT NOT NULL,
|
|
mode TEXT NOT NULL DEFAULT 'local',
|
|
allow_delete INTEGER NOT NULL DEFAULT 0,
|
|
remote_url TEXT NOT NULL DEFAULT '',
|
|
connect_host TEXT NOT NULL DEFAULT '',
|
|
host_header TEXT NOT NULL DEFAULT '',
|
|
tls_server_name TEXT NOT NULL DEFAULT '',
|
|
tls_insecure_skip_verify INTEGER NOT NULL DEFAULT 0,
|
|
sync_interval_sec INTEGER NOT NULL DEFAULT 300,
|
|
sync_enabled INTEGER NOT NULL DEFAULT 1,
|
|
dirty INTEGER NOT NULL DEFAULT 1,
|
|
next_sync_at INTEGER NOT NULL DEFAULT 0,
|
|
sync_running INTEGER NOT NULL DEFAULT 0,
|
|
sync_status TEXT NOT NULL DEFAULT 'idle',
|
|
sync_error TEXT NOT NULL DEFAULT '',
|
|
sync_step TEXT NOT NULL DEFAULT '',
|
|
sync_total INTEGER NOT NULL DEFAULT 0,
|
|
sync_done INTEGER NOT NULL DEFAULT 0,
|
|
sync_failed INTEGER NOT NULL DEFAULT 0,
|
|
sync_deleted INTEGER NOT NULL DEFAULT 0,
|
|
last_sync_started_at INTEGER NOT NULL DEFAULT 0,
|
|
last_sync_finished_at INTEGER NOT NULL DEFAULT 0,
|
|
last_sync_success_at INTEGER NOT NULL DEFAULT 0,
|
|
last_synced_revision TEXT NOT NULL DEFAULT '',
|
|
created_at INTEGER NOT NULL,
|
|
updated_at INTEGER NOT NULL,
|
|
PRIMARY KEY (repo_id, path),
|
|
FOREIGN KEY (repo_id) REFERENCES repos(id) ON DELETE CASCADE
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS rpm_mirror_runs (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
public_id TEXT NOT NULL UNIQUE,
|
|
repo_id INTEGER NOT NULL,
|
|
path TEXT NOT NULL,
|
|
started_at INTEGER NOT NULL,
|
|
finished_at INTEGER NOT NULL DEFAULT 0,
|
|
status TEXT NOT NULL DEFAULT 'running',
|
|
step TEXT NOT NULL DEFAULT '',
|
|
total INTEGER NOT NULL DEFAULT 0,
|
|
done INTEGER NOT NULL DEFAULT 0,
|
|
failed INTEGER NOT NULL DEFAULT 0,
|
|
deleted INTEGER NOT NULL DEFAULT 0,
|
|
revision TEXT NOT NULL DEFAULT '',
|
|
error TEXT NOT NULL DEFAULT '',
|
|
FOREIGN KEY(repo_id, path) REFERENCES rpm_repo_dirs(repo_id, path) ON DELETE CASCADE
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_users_disabled ON users(disabled);
|
|
CREATE INDEX IF NOT EXISTS idx_projects_name ON projects(name);
|
|
CREATE INDEX IF NOT EXISTS idx_repos_project ON repos(project_id);
|
|
CREATE UNIQUE INDEX IF NOT EXISTS idx_repos_project_name_type ON repos(project_id, name, type);
|
|
CREATE INDEX IF NOT EXISTS idx_issues_project ON issues(project_id);
|
|
CREATE INDEX IF NOT EXISTS idx_wiki_project ON wiki_pages(project_id);
|
|
CREATE INDEX IF NOT EXISTS idx_uploads_project ON uploads(project_id);
|
|
CREATE UNIQUE INDEX IF NOT EXISTS idx_api_keys_hash ON api_keys(token_hash);
|
|
CREATE INDEX IF NOT EXISTS idx_api_keys_user_id ON api_keys(user_id);
|
|
CREATE INDEX IF NOT EXISTS idx_api_keys_expires_at ON api_keys(expires_at);
|
|
CREATE INDEX IF NOT EXISTS idx_api_keys_disabled ON api_keys(disabled);
|
|
CREATE INDEX IF NOT EXISTS idx_project_repos_project ON project_repos(project_id);
|
|
CREATE INDEX IF NOT EXISTS idx_project_repos_repo ON project_repos(repo_id);
|
|
CREATE INDEX IF NOT EXISTS idx_pki_cas_parent ON pki_cas(parent_ca_id);
|
|
CREATE INDEX IF NOT EXISTS idx_pki_certs_ca ON pki_certs(ca_id);
|
|
CREATE INDEX IF NOT EXISTS idx_cert_principal_bindings_principal_id ON cert_principal_bindings(principal_id);
|
|
CREATE INDEX IF NOT EXISTS idx_principal_project_roles_project_id ON principal_project_roles(project_id);
|