86 lines
3.4 KiB
SQL
86 lines
3.4 KiB
SQL
CREATE EXTENSION IF NOT EXISTS "pgcrypto";
|
|
|
|
-- Canonical user data is independent of every authentication method.
|
|
CREATE TABLE IF NOT EXISTS user_accounts (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
email VARCHAR(320) UNIQUE NOT NULL,
|
|
display_name VARCHAR(160) NOT NULL,
|
|
avatar_url VARCHAR(1000),
|
|
email_verified BOOLEAN NOT NULL DEFAULT FALSE,
|
|
status VARCHAR(24) NOT NULL DEFAULT 'active',
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS password_credentials (
|
|
user_id UUID PRIMARY KEY REFERENCES user_accounts(id) ON DELETE CASCADE,
|
|
password_hash VARCHAR(500) NOT NULL,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS oidc_identities (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
user_id UUID NOT NULL REFERENCES user_accounts(id) ON DELETE CASCADE,
|
|
issuer VARCHAR(500) NOT NULL,
|
|
subject VARCHAR(500) NOT NULL,
|
|
provider_email VARCHAR(320) NOT NULL,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
last_login_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
CONSTRAINT uq_oidc_issuer_subject UNIQUE (issuer, subject),
|
|
CONSTRAINT uq_oidc_user_issuer UNIQUE (user_id, issuer)
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_oidc_user_id ON oidc_identities(user_id);
|
|
|
|
CREATE TABLE IF NOT EXISTS organizations (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
name VARCHAR(160) NOT NULL,
|
|
slug VARCHAR(100) UNIQUE NOT NULL,
|
|
home_region VARCHAR(32) NOT NULL DEFAULT 'ap-south-1',
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS projects (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
organization_id UUID NOT NULL REFERENCES organizations(id),
|
|
name VARCHAR(160) NOT NULL,
|
|
environment VARCHAR(24) NOT NULL DEFAULT 'test',
|
|
status VARCHAR(24) NOT NULL DEFAULT 'active',
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS activity_events (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
project_id UUID NOT NULL REFERENCES projects(id),
|
|
event_type VARCHAR(80) NOT NULL,
|
|
title VARCHAR(200) NOT NULL,
|
|
detail VARCHAR(500) NOT NULL DEFAULT '',
|
|
status VARCHAR(24) NOT NULL DEFAULT 'success',
|
|
occurred_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_activity_project_time
|
|
ON activity_events(project_id, occurred_at DESC);
|
|
CREATE INDEX IF NOT EXISTS idx_activity_type
|
|
ON activity_events(event_type);
|
|
|
|
WITH org AS (
|
|
INSERT INTO organizations (name, slug)
|
|
VALUES ('Urmi Technologies', 'urmi-technologies')
|
|
ON CONFLICT (slug) DO UPDATE SET name = EXCLUDED.name
|
|
RETURNING id
|
|
), project AS (
|
|
INSERT INTO projects (organization_id, name, environment)
|
|
SELECT id, 'Production API', 'production' FROM org
|
|
RETURNING id
|
|
)
|
|
INSERT INTO activity_events (project_id, event_type, title, detail, status)
|
|
SELECT project.id, seed.event_type, seed.title, seed.detail, seed.status
|
|
FROM project
|
|
CROSS JOIN (VALUES
|
|
('call.completed', 'Outbound call completed', '+91 98765 43210 · 02:14', 'success'),
|
|
('message.delivered', 'Message delivered', 'msg_8f7g2h1 · +91 99876 54321', 'success'),
|
|
('dlt.template.approved', 'DLT template approved', 'OTP_LOGIN · URMIOT', 'success'),
|
|
('webhook.retried', 'Webhook delivery retried', 'endpoint_checkout · attempt 2', 'warning')
|
|
) AS seed(event_type, title, detail, status);
|