wehub-resource-sync
176 lines
7.1 KiB
PL/PgSQL
176 lines
7.1 KiB
PL/PgSQL
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
|
|
|
|
CREATE OR REPLACE FUNCTION update_updated_at_column()
|
|
RETURNS TRIGGER AS $$
|
|
BEGIN
|
|
NEW.updated_at = NOW();
|
|
RETURN NEW;
|
|
END;
|
|
$$ LANGUAGE plpgsql;
|
|
|
|
CREATE TABLE IF NOT EXISTS users (
|
|
id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
|
|
name VARCHAR(255) NOT NULL,
|
|
email VARCHAR(255) NOT NULL,
|
|
domain VARCHAR(255) NOT NULL,
|
|
is_trial BOOLEAN NOT NULL,
|
|
num_non_draft_plans INTEGER NOT NULL DEFAULT 0,
|
|
created_at TIMESTAMP NOT NULL DEFAULT NOW(),
|
|
updated_at TIMESTAMP NOT NULL DEFAULT NOW()
|
|
);
|
|
CREATE TRIGGER update_users_modtime BEFORE UPDATE ON users FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();
|
|
|
|
ALTER TABLE users ADD UNIQUE (email);
|
|
CREATE INDEX users_domain_idx ON users(domain);
|
|
|
|
CREATE TABLE IF NOT EXISTS orgs (
|
|
id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
|
|
name VARCHAR(255) NOT NULL,
|
|
domain VARCHAR(255),
|
|
auto_add_domain_users BOOLEAN NOT NULL DEFAULT FALSE,
|
|
owner_id UUID NOT NULL REFERENCES users(id),
|
|
is_trial BOOLEAN NOT NULL,
|
|
created_at TIMESTAMP NOT NULL DEFAULT NOW(),
|
|
updated_at TIMESTAMP NOT NULL DEFAULT NOW()
|
|
);
|
|
CREATE TRIGGER update_orgs_modtime BEFORE UPDATE ON orgs FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();
|
|
|
|
ALTER TABLE orgs ADD UNIQUE (domain);
|
|
|
|
CREATE TABLE IF NOT EXISTS orgs_users (
|
|
id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
|
|
org_id UUID NOT NULL REFERENCES orgs(id) ON DELETE CASCADE,
|
|
user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
|
|
created_at TIMESTAMP NOT NULL DEFAULT NOW(),
|
|
updated_at TIMESTAMP NOT NULL DEFAULT NOW()
|
|
);
|
|
CREATE TRIGGER update_orgs_users_modtime BEFORE UPDATE ON orgs_users FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();
|
|
|
|
CREATE INDEX orgs_users_user_idx ON orgs_users(user_id);
|
|
CREATE INDEX orgs_users_org_idx ON orgs_users(org_id);
|
|
|
|
CREATE TABLE IF NOT EXISTS invites (
|
|
id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
|
|
org_id UUID NOT NULL REFERENCES orgs(id) ON DELETE CASCADE,
|
|
name VARCHAR(255) NOT NULL,
|
|
email VARCHAR(255) NOT NULL,
|
|
inviter_id UUID NOT NULL REFERENCES users(id),
|
|
invitee_id UUID REFERENCES users(id),
|
|
accepted_at TIMESTAMP,
|
|
created_at TIMESTAMP NOT NULL DEFAULT NOW(),
|
|
updated_at TIMESTAMP NOT NULL DEFAULT NOW()
|
|
);
|
|
CREATE TRIGGER update_invites_modtime BEFORE UPDATE ON invites FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();
|
|
|
|
CREATE INDEX invites_pending_idx ON invites(org_id, (accepted_at IS NULL));
|
|
CREATE INDEX invites_email_idx ON invites(email, (accepted_at IS NULL));
|
|
CREATE INDEX invites_org_user_idx ON invites(org_id, invitee_id);
|
|
|
|
CREATE TABLE IF NOT EXISTS auth_tokens (
|
|
id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
|
|
user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
|
|
token_hash VARCHAR(64) NOT NULL,
|
|
is_trial BOOLEAN NOT NULL DEFAULT FALSE,
|
|
created_at TIMESTAMP NOT NULL DEFAULT NOW(),
|
|
deleted_at TIMESTAMP
|
|
);
|
|
CREATE UNIQUE INDEX auth_tokens_idx ON auth_tokens(token_hash);
|
|
|
|
CREATE TABLE IF NOT EXISTS email_verifications (
|
|
id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
|
|
email VARCHAR(255) NOT NULL,
|
|
pin_hash VARCHAR(64) NOT NULL,
|
|
user_id UUID REFERENCES users(id),
|
|
auth_token_id UUID REFERENCES auth_tokens(id),
|
|
created_at TIMESTAMP NOT NULL DEFAULT NOW(),
|
|
updated_at TIMESTAMP NOT NULL DEFAULT NOW()
|
|
);
|
|
CREATE TRIGGER update_email_verifications_modtime BEFORE UPDATE ON email_verifications FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();
|
|
|
|
CREATE UNIQUE INDEX email_verifications_idx ON email_verifications(pin_hash, email, created_at DESC);
|
|
|
|
CREATE TABLE IF NOT EXISTS projects (
|
|
id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
|
|
org_id UUID NOT NULL REFERENCES orgs(id) ON DELETE CASCADE,
|
|
name VARCHAR(255) NOT NULL,
|
|
created_at TIMESTAMP NOT NULL DEFAULT NOW(),
|
|
updated_at TIMESTAMP NOT NULL DEFAULT NOW()
|
|
);
|
|
CREATE TRIGGER update_projects_modtime BEFORE UPDATE ON projects FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();
|
|
|
|
CREATE TABLE IF NOT EXISTS plans (
|
|
id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
|
|
org_id UUID NOT NULL REFERENCES orgs(id) ON DELETE CASCADE,
|
|
owner_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
|
|
project_id UUID NOT NULL REFERENCES projects(id) ON DELETE CASCADE,
|
|
name VARCHAR(255) NOT NULL,
|
|
shared_with_org_at TIMESTAMP,
|
|
total_replies INTEGER NOT NULL DEFAULT 0,
|
|
active_branches INTEGER NOT NULL DEFAULT 0,
|
|
archived_at TIMESTAMP,
|
|
created_at TIMESTAMP NOT NULL DEFAULT NOW(),
|
|
updated_at TIMESTAMP NOT NULL DEFAULT NOW()
|
|
);
|
|
CREATE TRIGGER update_plans_modtime BEFORE UPDATE ON plans FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();
|
|
|
|
CREATE INDEX plans_name_idx ON plans(project_id, owner_id, name);
|
|
CREATE INDEX plans_archived_idx ON plans(project_id, owner_id, archived_at);
|
|
|
|
CREATE TABLE IF NOT EXISTS branches (
|
|
id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
|
|
org_id UUID NOT NULL REFERENCES orgs(id) ON DELETE CASCADE,
|
|
owner_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
|
|
plan_id UUID NOT NULL REFERENCES plans(id) ON DELETE CASCADE,
|
|
parent_branch_id UUID REFERENCES branches(id) ON DELETE CASCADE,
|
|
name VARCHAR(255) NOT NULL,
|
|
status VARCHAR(32) NOT NULL,
|
|
error TEXT,
|
|
context_tokens INTEGER NOT NULL DEFAULT 0,
|
|
convo_tokens INTEGER NOT NULL DEFAULT 0,
|
|
shared_with_org_at TIMESTAMP,
|
|
archived_at TIMESTAMP,
|
|
created_at TIMESTAMP NOT NULL DEFAULT NOW(),
|
|
updated_at TIMESTAMP NOT NULL DEFAULT NOW(),
|
|
deleted_at TIMESTAMP
|
|
);
|
|
CREATE TRIGGER update_branches_modtime BEFORE UPDATE ON branches FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();
|
|
|
|
CREATE UNIQUE INDEX branches_name_idx ON branches(plan_id, name, archived_at, deleted_at);
|
|
|
|
|
|
CREATE TABLE IF NOT EXISTS users_projects (
|
|
id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
|
|
org_id UUID NOT NULL REFERENCES orgs(id) ON DELETE CASCADE,
|
|
user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
|
|
project_id UUID NOT NULL REFERENCES projects(id) ON DELETE CASCADE,
|
|
last_active_plan_id UUID REFERENCES plans(id),
|
|
created_at TIMESTAMP NOT NULL DEFAULT NOW(),
|
|
updated_at TIMESTAMP NOT NULL DEFAULT NOW()
|
|
);
|
|
CREATE TRIGGER update_users_projects_modtime BEFORE UPDATE ON users_projects FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();
|
|
|
|
CREATE INDEX users_projects_idx ON users_projects(user_id, org_id, project_id);
|
|
|
|
CREATE TABLE IF NOT EXISTS convo_summaries (
|
|
id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
|
|
org_id UUID NOT NULL REFERENCES orgs(id) ON DELETE CASCADE,
|
|
plan_id UUID NOT NULL REFERENCES plans(id) ON DELETE CASCADE,
|
|
latest_convo_message_id UUID NOT NULL,
|
|
latest_convo_message_created_at TIMESTAMP NOT NULL,
|
|
summary TEXT NOT NULL,
|
|
tokens INTEGER NOT NULL,
|
|
num_messages INTEGER NOT NULL,
|
|
created_at TIMESTAMP NOT NULL DEFAULT NOW()
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS plan_builds (
|
|
id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
|
|
org_id UUID NOT NULL REFERENCES orgs(id) ON DELETE CASCADE,
|
|
plan_id UUID NOT NULL REFERENCES plans(id) ON DELETE CASCADE,
|
|
convo_message_id UUID NOT NULL,
|
|
file_path VARCHAR(255) NOT NULL,
|
|
error TEXT,
|
|
created_at TIMESTAMP NOT NULL DEFAULT NOW(),
|
|
updated_at TIMESTAMP NOT NULL DEFAULT NOW()
|
|
);
|
|
CREATE TRIGGER update_plan_builds_modtime BEFORE UPDATE ON plan_builds FOR EACH ROW EXECUTE FUNCTION update_updated_at_column(); |