Menu
Public repository / 3579c0413818863c25c30db0bc4ae7386c1fc69f
AkurAI-IDP / src/db/schema.rs
Latest change e1794828e5fae9a8545a66e77b2e8200cf8c090b - feat: add organization lifecycle telemetry by Ólafur Búi Ólafsson
use rusqlite::Connection;
pub fn create_tables(conn: &Connection) {
conn.execute_batch(
"
CREATE TABLE IF NOT EXISTS tenants (
id TEXT PRIMARY KEY,
name TEXT NOT NULL,
slug TEXT NOT NULL,
domain TEXT,
created_at INTEGER NOT NULL,
UNIQUE(slug)
);
CREATE TABLE IF NOT EXISTS users (
id TEXT PRIMARY KEY,
tenant_id TEXT NOT NULL REFERENCES tenants(id) ON DELETE CASCADE,
email TEXT NOT NULL,
password_hash TEXT NOT NULL,
email_verified INTEGER NOT NULL DEFAULT 0,
mfa_secret TEXT,
mfa_enabled INTEGER NOT NULL DEFAULT 0,
force_password_change INTEGER NOT NULL DEFAULT 0,
locked_until INTEGER,
failed_attempts INTEGER NOT NULL DEFAULT 0,
created_at INTEGER NOT NULL,
updated_at INTEGER NOT NULL,
UNIQUE(tenant_id, email)
);
CREATE INDEX IF NOT EXISTS users_tenant_id_idx ON users(tenant_id);
CREATE TABLE IF NOT EXISTS groups (
id TEXT PRIMARY KEY,
tenant_id TEXT NOT NULL REFERENCES tenants(id) ON DELETE CASCADE,
name TEXT NOT NULL,
description TEXT,
UNIQUE(tenant_id, name)
);
CREATE INDEX IF NOT EXISTS groups_tenant_id_idx ON groups(tenant_id);
CREATE TABLE IF NOT EXISTS user_groups (
user_id TEXT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
group_id TEXT NOT NULL REFERENCES groups(id) ON DELETE CASCADE,
PRIMARY KEY (user_id, group_id)
);
CREATE TABLE IF NOT EXISTS clients (
id TEXT PRIMARY KEY,
tenant_id TEXT NOT NULL REFERENCES tenants(id) ON DELETE CASCADE,
name TEXT NOT NULL,
client_secret_hash TEXT NOT NULL,
redirect_uris TEXT NOT NULL,
grant_types TEXT NOT NULL,
scopes TEXT NOT NULL,
first_party INTEGER NOT NULL DEFAULT 0,
created_at INTEGER NOT NULL
);
CREATE INDEX IF NOT EXISTS clients_tenant_id_idx ON clients(tenant_id);
CREATE TABLE IF NOT EXISTS auth_codes (
code TEXT PRIMARY KEY,
client_id TEXT NOT NULL REFERENCES clients(id) ON DELETE CASCADE,
user_id TEXT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
scopes TEXT NOT NULL,
redirect_uri TEXT NOT NULL,
code_challenge TEXT,
code_challenge_method TEXT,
nonce TEXT,
expires_at INTEGER NOT NULL,
used INTEGER NOT NULL DEFAULT 0
);
CREATE INDEX IF NOT EXISTS auth_codes_client_id_idx ON auth_codes(client_id);
CREATE INDEX IF NOT EXISTS auth_codes_user_id_idx ON auth_codes(user_id);
CREATE TABLE IF NOT EXISTS refresh_tokens (
token_hash TEXT PRIMARY KEY,
client_id TEXT NOT NULL REFERENCES clients(id) ON DELETE CASCADE,
user_id TEXT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
scopes TEXT NOT NULL,
expires_at INTEGER NOT NULL,
revoked INTEGER NOT NULL DEFAULT 0
);
CREATE INDEX IF NOT EXISTS refresh_tokens_client_id_idx ON refresh_tokens(client_id);
CREATE INDEX IF NOT EXISTS refresh_tokens_user_id_idx ON refresh_tokens(user_id);
CREATE TABLE IF NOT EXISTS sessions (
id TEXT PRIMARY KEY,
user_id TEXT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
ip TEXT NOT NULL,
user_agent TEXT NOT NULL,
created_at INTEGER NOT NULL,
expires_at INTEGER NOT NULL,
active_organization_id TEXT REFERENCES organizations(id) ON DELETE SET NULL
);
CREATE INDEX IF NOT EXISTS sessions_user_id_idx ON sessions(user_id);
CREATE TABLE IF NOT EXISTS backup_codes (
id TEXT PRIMARY KEY,
user_id TEXT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
code_hash TEXT NOT NULL,
used INTEGER NOT NULL DEFAULT 0
);
CREATE INDEX IF NOT EXISTS backup_codes_user_id_idx ON backup_codes(user_id);
CREATE TABLE IF NOT EXISTS audit_log (
id TEXT PRIMARY KEY,
tenant_id TEXT REFERENCES tenants(id) ON DELETE SET NULL,
user_id TEXT REFERENCES users(id) ON DELETE SET NULL,
event TEXT NOT NULL,
ip TEXT,
metadata TEXT,
created_at INTEGER NOT NULL
);
CREATE INDEX IF NOT EXISTS audit_log_tenant_id_idx ON audit_log(tenant_id);
CREATE INDEX IF NOT EXISTS audit_log_user_id_idx ON audit_log(user_id);
CREATE INDEX IF NOT EXISTS audit_log_event_idx ON audit_log(event);
CREATE INDEX IF NOT EXISTS audit_log_created_at_idx ON audit_log(created_at);
CREATE TABLE IF NOT EXISTS signing_keys (
kid TEXT PRIMARY KEY,
alg TEXT NOT NULL,
public_key TEXT NOT NULL,
private_key_enc TEXT NOT NULL,
active INTEGER NOT NULL DEFAULT 1,
created_at INTEGER NOT NULL
);
CREATE TABLE IF NOT EXISTS mfa_pending (
token_hash TEXT PRIMARY KEY,
user_id TEXT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
created_at INTEGER NOT NULL,
attempts INTEGER NOT NULL DEFAULT 0
);
CREATE INDEX IF NOT EXISTS mfa_pending_user_idx ON mfa_pending(user_id);
CREATE INDEX IF NOT EXISTS signing_keys_active_idx ON signing_keys(active);
",
)
.expect("failed to create tables");
// Organizations are the canonical tenant identity. The legacy `tenants`
// table remains the compatibility source for existing callers, but IDs are
// copied verbatim so changing a display name, slug, or email never changes
// the identity used by downstream systems.
conn.execute_batch(
"
CREATE TABLE IF NOT EXISTS organizations (
id TEXT PRIMARY KEY,
display_name TEXT NOT NULL,
slug TEXT NOT NULL UNIQUE,
domain TEXT,
created_at INTEGER NOT NULL,
updated_at INTEGER NOT NULL
);
CREATE INDEX IF NOT EXISTS organizations_slug_idx ON organizations(slug);
CREATE TABLE IF NOT EXISTS organization_memberships (
organization_id TEXT NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
user_id TEXT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
role TEXT NOT NULL CHECK (role IN ('owner', 'admin', 'member')),
status TEXT NOT NULL CHECK (status IN ('active', 'revoked')),
created_at INTEGER NOT NULL,
updated_at INTEGER NOT NULL,
PRIMARY KEY (organization_id, user_id)
);
CREATE INDEX IF NOT EXISTS organization_memberships_user_idx
ON organization_memberships(user_id, status);
CREATE TABLE IF NOT EXISTS organization_invitations (
id TEXT PRIMARY KEY,
organization_id TEXT NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
email TEXT NOT NULL,
role TEXT NOT NULL CHECK (role IN ('admin', 'member')),
invited_by TEXT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
token_hash TEXT NOT NULL UNIQUE,
expires_at INTEGER NOT NULL,
accepted_at INTEGER,
revoked_at INTEGER,
created_at INTEGER NOT NULL
);
CREATE TABLE IF NOT EXISTS organization_invitation_delivery_drafts (
invitation_id TEXT PRIMARY KEY REFERENCES organization_invitations(id) ON DELETE CASCADE,
token TEXT NOT NULL,
created_at INTEGER NOT NULL,
status TEXT NOT NULL DEFAULT 'draft'
);
CREATE INDEX IF NOT EXISTS organization_invitation_delivery_status_idx
ON organization_invitation_delivery_drafts(status, created_at);
CREATE INDEX IF NOT EXISTS organization_invitations_org_idx
ON organization_invitations(organization_id, email);
CREATE INDEX IF NOT EXISTS organization_invitations_token_idx
ON organization_invitations(token_hash);
CREATE TABLE IF NOT EXISTS organization_workspaces (
id TEXT PRIMARY KEY,
organization_id TEXT NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
workspace_id TEXT NOT NULL,
installation_id TEXT,
app_id TEXT,
created_at INTEGER NOT NULL,
UNIQUE (organization_id, workspace_id),
UNIQUE (workspace_id)
);
CREATE INDEX IF NOT EXISTS organization_workspaces_org_idx
ON organization_workspaces(organization_id);
CREATE TABLE IF NOT EXISTS organization_lifecycle_markers (
organization_id TEXT NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
marker TEXT NOT NULL,
user_id TEXT REFERENCES users(id) ON DELETE SET NULL,
occurred_at INTEGER NOT NULL,
PRIMARY KEY (organization_id, marker)
);
",
)
.expect("failed to create organization tables");
// Add organization context to one-time codes and refresh tokens so a
// secondary organization remains bound across the entire token lifecycle.
if !column_exists(conn, "auth_codes", "organization_id") {
conn.execute_batch("ALTER TABLE auth_codes ADD COLUMN organization_id TEXT;")
.expect("failed to migrate auth code organization");
}
if !column_exists(conn, "refresh_tokens", "organization_id") {
conn.execute_batch("ALTER TABLE refresh_tokens ADD COLUMN organization_id TEXT;")
.expect("failed to migrate refresh token organization");
}
conn.execute(
"UPDATE organization_invitations SET email = lower(trim(email))",
[],
)
.expect("failed to normalize invitation emails");
// Older databases could contain duplicate pending invitations. Keep the
// newest row for each normalized organization/email pair before enforcing
// the invariant for future writes.
conn.execute(
"DELETE FROM organization_invitations
WHERE accepted_at IS NULL AND revoked_at IS NULL
AND id NOT IN (
SELECT MAX(id) FROM organization_invitations
WHERE accepted_at IS NULL AND revoked_at IS NULL
GROUP BY organization_id, email
)",
[],
)
.expect("failed to deduplicate pending invitations");
conn.execute_batch(
"CREATE UNIQUE INDEX IF NOT EXISTS organization_invitations_pending_uq
ON organization_invitations(organization_id, email)
WHERE accepted_at IS NULL AND revoked_at IS NULL;",
)
.expect("failed to create pending invitation index");
// These columns are additive migrations for databases created before
// organization authorization existed. SQLite has no IF NOT EXISTS form
// for ADD COLUMN, so inspect the schema before applying each one.
if !column_exists(conn, "users", "authorization_version") {
conn.execute_batch(
"ALTER TABLE users ADD COLUMN authorization_version INTEGER NOT NULL DEFAULT 0;",
)
.expect("failed to migrate users authorization version");
}
if !column_exists(conn, "sessions", "authorization_version") {
conn.execute_batch(
"ALTER TABLE sessions ADD COLUMN authorization_version INTEGER NOT NULL DEFAULT 0;",
)
.expect("failed to migrate sessions authorization version");
}
// Session authorization is scoped to the selected organization. A NULL
// value intentionally falls back to the legacy primary tenant.
if !column_exists(conn, "sessions", "active_organization_id") {
conn.execute_batch("ALTER TABLE sessions ADD COLUMN active_organization_id TEXT;")
.expect("failed to migrate session organization");
}
conn.execute_batch(
"
INSERT OR IGNORE INTO organizations
(id, display_name, slug, domain, created_at, updated_at)
SELECT id, name, slug, domain, created_at, created_at FROM tenants;
INSERT OR IGNORE INTO organization_memberships
(organization_id, user_id, role, status, created_at, updated_at)
SELECT tenant_id, id, 'member', 'active', created_at, updated_at FROM users;
",
)
.expect("failed to migrate legacy tenant memberships");
// Deterministically promote the oldest legacy user to owner when a
// migrated organization has no owner. Existing explicit owners win.
conn.execute(
"UPDATE organization_memberships
SET role = 'owner'
WHERE status = 'active'
AND role <> 'owner'
AND NOT EXISTS (
SELECT 1 FROM organization_memberships existing
WHERE existing.organization_id = organization_memberships.organization_id
AND existing.status = 'active' AND existing.role = 'owner'
)
AND user_id = (
SELECT u.id FROM users u
WHERE u.tenant_id = organization_memberships.organization_id
ORDER BY u.created_at, u.id LIMIT 1
)",
[],
)
.expect("failed to migrate legacy organization owner");
}
fn column_exists(conn: &Connection, table: &str, column: &str) -> bool {
let sql = format!("PRAGMA table_info({table})");
conn.prepare(&sql)
.and_then(|mut stmt| {
stmt.query_map([], |row| row.get::<_, String>(1))?
.collect::<Result<Vec<_>, _>>()
})
.map(|columns| columns.iter().any(|name| name == column))
.unwrap_or(false)
}