🗄️ Tsuwa Database Schema Reference

CREATE TABLE IF NOT EXISTS device_secrets (
    peer_id VARCHAR(50) PRIMARY KEY,
    secret_hash VARCHAR(128) NOT NULL,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS device_tokens (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    peer_id VARCHAR(50) UNIQUE NOT NULL,
    token TEXT DEFAULT '',
    platform VARCHAR(20) DEFAULT 'ios' CHECK (platform IN ('ios', 'android', 'ios_voip', 'ios_alert', 'android_fcm')),
    bundle_id VARCHAR(100) DEFAULT '',
    voip_token TEXT DEFAULT '',
    apns_token TEXT DEFAULT '',
    raw_data JSONB DEFAULT '{}'::jsonb,
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX IF NOT EXISTS device_tokens_peer_id_idx ON device_tokens(peer_id);

CREATE TABLE IF NOT EXISTS closed_group_policies (
    peer_id VARCHAR(50) PRIMARY KEY,
    group_code VARCHAR(20) NOT NULL,
    policy VARCHAR(30) DEFAULT 'closed_members_only',
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS users (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    username VARCHAR(50) UNIQUE NOT NULL,
    display_name VARCHAR(100) NOT NULL,
    password_hash VARCHAR(255) NOT NULL DEFAULT '',
    status VARCHAR(20) DEFAULT 'active' CHECK (status IN ('active', 'disabled')),
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX IF NOT EXISTS users_username_idx ON users(username);

CREATE TABLE IF NOT EXISTS api_keys (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    key_name VARCHAR(100) NOT NULL,
    key_hash VARCHAR(64) NOT NULL UNIQUE,
    key_prefix VARCHAR(16) NOT NULL,
    permissions JSONB DEFAULT '["signaling", "push", "uacreg"]'::jsonb,
    is_active BOOLEAN DEFAULT true,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    last_used_at TIMESTAMP WITH TIME ZONE NULL
);

CREATE TABLE IF NOT EXISTS sip_accounts (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id UUID REFERENCES users(id) ON DELETE CASCADE,
    extension VARCHAR(50) NOT NULL,
    domain VARCHAR(100) NOT NULL,
    auth_username VARCHAR(50) NOT NULL DEFAULT '',
    auth_password_hash VARCHAR(255) NOT NULL DEFAULT '',
    auth_ha1 VARCHAR(128) NOT NULL DEFAULT '',
    realm VARCHAR(100) NOT NULL DEFAULT '',
    proxy_host VARCHAR(100) NOT NULL DEFAULT '',
    proxy_port INT DEFAULT 5060,
    transport VARCHAR(10) DEFAULT 'udp' CHECK (transport IN ('udp', 'tcp', 'tls', 'ws', 'wss')),
    is_enabled BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    UNIQUE(user_id, extension, domain)
);

CREATE TABLE IF NOT EXISTS uacreg (
    id SERIAL PRIMARY KEY,
    l_uuid VARCHAR(64) DEFAULT '' NOT NULL,
    l_username VARCHAR(64) DEFAULT '' NOT NULL,
    l_domain VARCHAR(128) DEFAULT '' NOT NULL,
    r_username VARCHAR(64) DEFAULT '' NOT NULL,
    r_domain VARCHAR(128) DEFAULT '' NOT NULL,
    realm VARCHAR(64) DEFAULT '' NOT NULL,
    auth_username VARCHAR(64) DEFAULT '' NOT NULL,
    auth_password VARCHAR(64) DEFAULT '' NOT NULL,
    auth_ha1 VARCHAR(128) DEFAULT '' NOT NULL,
    auth_proxy VARCHAR(128) DEFAULT '' NOT NULL,
    expires INT DEFAULT 300 NOT NULL,
    flags INT DEFAULT 0 NOT NULL,
    reg_delay INT DEFAULT 0 NOT NULL,
    contact_addr VARCHAR(128) DEFAULT '' NOT NULL,
    socket VARCHAR(128) DEFAULT '' NOT NULL,
    CONSTRAINT uacreg_l_uuid_idx UNIQUE (l_uuid)
);

CREATE TABLE IF NOT EXISTS sip_account_activity (
    peer_id VARCHAR(50) PRIMARY KEY,
    last_seen TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS version (
    table_name VARCHAR(32) NOT NULL,
    table_version INT DEFAULT 0 NOT NULL,
    CONSTRAINT version_table_name_idx UNIQUE (table_name)
);

CREATE TABLE IF NOT EXISTS groups (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    group_code VARCHAR(20) UNIQUE NOT NULL,
    host_user_id UUID NULL REFERENCES users(id) ON DELETE SET NULL,
    host_peer_id VARCHAR(50) NOT NULL DEFAULT '',
    name VARCHAR(100) NOT NULL,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX IF NOT EXISTS groups_group_code_idx ON groups(group_code);

CREATE TABLE IF NOT EXISTS group_members (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    group_id UUID NOT NULL REFERENCES groups(id) ON DELETE CASCADE,
    peer_id VARCHAR(50) NOT NULL,
    sub_extension VARCHAR(10) NOT NULL,
    role VARCHAR(20) DEFAULT 'member' CHECK (role IN ('host', 'co_host', 'member')),
    status VARCHAR(20) DEFAULT 'joined' CHECK (status IN ('invited', 'joined', 'declined', 'left')),
    joined_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    UNIQUE(group_id, peer_id),
    UNIQUE(group_id, sub_extension)
);

CREATE TABLE IF NOT EXISTS membership_periods (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    group_id UUID NOT NULL REFERENCES groups(id) ON DELETE CASCADE,
    peer_id VARCHAR(50) NOT NULL,
    joined_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
    left_at TIMESTAMP WITH TIME ZONE NULL
);

CREATE TABLE IF NOT EXISTS group_timeline_events (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    group_id UUID NOT NULL REFERENCES groups(id) ON DELETE CASCADE,
    event_type VARCHAR(30) NOT NULL CHECK (event_type IN (
        'call_start', 'call_end', 'file_shared', 'invite',
        'member_joined', 'member_left', 'voice_room_start',
        'voice_room_end', 'system_notice'
    )),
    sender_peer_id VARCHAR(50) NOT NULL,
    payload JSONB DEFAULT '{}'::jsonb,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS voice_rooms (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    group_id UUID UNIQUE NOT NULL REFERENCES groups(id) ON DELETE CASCADE,
    sfu_room_id VARCHAR(100) NOT NULL,
    is_active BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS voice_room_participants (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    voice_room_id UUID NOT NULL REFERENCES voice_rooms(id) ON DELETE CASCADE,
    peer_id VARCHAR(50) NOT NULL,
    joined_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    UNIQUE(voice_room_id, peer_id)
);

CREATE TABLE IF NOT EXISTS call_logs (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    call_session_id VARCHAR(100) UNIQUE NOT NULL,
    caller_peer_id VARCHAR(50) NOT NULL,
    callee_peer_id VARCHAR(50) NOT NULL,
    call_type VARCHAR(20) CHECK (call_type IN ('webrtc_p2p', 'webrtc_pod', 'sip_inbound', 'sip_outbound')),
    status VARCHAR(20) DEFAULT 'initiated' CHECK (status IN ('initiated', 'ringing', 'answered', 'rejected', 'missed', 'failed', 'ended')),
    duration_seconds INT DEFAULT 0,
    started_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    answered_at TIMESTAMP WITH TIME ZONE NULL,
    ended_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);