Tsuwa Database Schema Reference (本番稼働データベース仕様書

稼働テーブル数

Unified SSOT Schema (稼働中テーブルのみ)

ホスティング環境

PostgreSQL / Supabase

AWS 東京リージョン (ap-northeast-1)

通話番号＆認証空間

6桁番号 ↔ User UUID

number_directory (単一真実源: SSOT)

セキュリティ保護

RLS 有効＆ HA1暗号化

MD5 ダイジェスト認証 / 平文パスワード完全消去

🛡️ 実稼働データベース（SSOT）適正化とプライバシーアーキテクチャ

本仕様書は、バックエンド（signaling_server_ws.py）で実稼働・マイグレーションされている 7 つの真実源テーブル（SSOT） のみを正確に記載しています。

ポッド・グループ管理: 高速なオンメモリ＋永続化 JSON ドキュメント（/app/data/groups_store.json）および NATS パブサブで管理されており、RDBMS のオーバーヘッドを排除しています。
完全プライバシー保護（通話ログ非保持）: Tsuwa のゼロ知識設計に基づき、通話履歴・連絡先は端末ローカルにのみ保存され、サーバー側のデータベースには一切保存・記録されません（旧架空テーブル call_logs, users は完全削除）。
SIP パスワード保護: Kamailio uacreg は平文パスワードを即時消去し、ハッシュ値 auth_ha1（MD5 ダイジェスト）に一本化して運用されています。

📊 エンティティ・リレーション図 (ER Diagram)

erDiagram
    number_directory ||--|| device_secrets : "user_uuid (SSOT)"
    device_secrets ||--o| device_tokens : "user_uuid"
    device_secrets ||--o| closed_group_policies : "user_uuid"
    device_secrets ||--o| sip_account_activity : "user_uuid"
    uacreg ||--|| version : "table_version"

    number_directory {
        varchar tsuwa_number PK "6桁公開通話番号"
        varchar user_uuid UK "端末不変UUID (SSOT)"
        timestamptz created_at
        timestamptz updated_at
    }

    device_secrets {
        uuid user_uuid PK "端末不変UUID"
        varchar secret_hash "SHA-256 シークレット"
        timestamptz created_at
        timestamptz updated_at
    }

    device_tokens {
        uuid id PK "トークンレコードID"
        uuid user_uuid UK "端末不変UUID"
        text token "Pushトークン"
        varchar platform "ios / android"
        varchar bundle_id "アプリID"
        text voip_token "VoIP Pushトークン"
        text apns_token "APNs通常通知トークン"
        jsonb raw_data "通知メタデータ"
        timestamptz updated_at
    }

    closed_group_policies {
        uuid user_uuid PK "端末不変UUID"
        varchar group_code "所属ポッド番号"
        varchar policy "closed_members_only"
        timestamptz updated_at
    }

    sip_account_activity {
        uuid user_uuid PK "端末不変UUID"
        timestamptz last_seen "最終アクティビティ"
    }

    uacreg {
        serial id PK
        varchar l_uuid UK "UACセッション識別子"
        varchar l_username "ローカル番号"
        varchar l_domain "ローカルドメイン"
        varchar r_username "キャリア側ユーザー名"
        varchar r_domain "キャリアドメイン"
        varchar realm "SIPレルム"
        varchar auth_username "認証ユーザー名"
        varchar auth_ha1 "MD5 HA1ダイジェスト"
        varchar auth_proxy "SIPプロキシURI"
        int expires "レジスト間隔(秒)"
        varchar contact_addr "コンタクトアドレス"
        varchar socket "リッスンソケット"
    }

    version {
        varchar table_name PK "uacreg"
        int table_version "Kamailioテーブルバージョン(5)"
    }

number_directory 通話番号ディレクトリ（公開ルーティング）

端末・通話番号 RLS 有効

公開ダイヤル用6桁番号（tsuwa_number）と端末内部識別子（user_uuid）のマッピングを管理する公開ディレクトリテーブルです。番号再割り当て（reassign）時にも内部 UUID が維持されるため、ユーザーの所属ポッドや設定情報を一切失わずに番号のみを切り替えることが可能です。

カラム名	データ型	キー/制約	NULL	デフォルト値	説明
tsuwa_number	VARCHAR(10)	PK	NO	-	公開用6桁通話番号（例: `590004`）。ダイヤル時に指定する公開アドレス。
user_uuid	UUID	UNIQUE	NO	-	端末固有の内部不変識別子（UUID v4）。
created_at	TIMESTAMPTZ	-	YES	CURRENT_TIMESTAMP	番号紐付け日時。
updated_at	TIMESTAMPTZ	-	YES	CURRENT_TIMESTAMP	最終番号変更・更新日時。

RLS ポリシー: anon_deny_number_directory FOR ALL USING (false); (PostgREST anon 直接参照禁止)

CREATE TABLE IF NOT EXISTS number_directory (
    tsuwa_number VARCHAR(10) PRIMARY KEY,
    user_uuid UUID NOT NULL UNIQUE,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX IF NOT EXISTS idx_number_directory_uuid ON number_directory(user_uuid);

device_secrets 端末認証シークレットマスター

端末・通話番号 RLS 有効

端末固有内部識別子（user_uuid）ごとの認証シークレットマスターテーブルです。端末登録時に生成された認証キーのソルト付きハッシュ値を保持し、他端末による不正ログインやなりすましを暗号学的に防止します。

カラム名	データ型	キー/制約	NULL	デフォルト値	説明
user_uuid	UUID	PK	NO	-	端末内部識別子 (UUID)。推測不可能な内部ID。
secret_hash	VARCHAR(128)	-	NO	-	端末認証シークレットの SHA-256 ソルト付きハッシュ（`SHA256(user_uuid:secret)`）。
created_at	TIMESTAMPTZ	-	YES	CURRENT_TIMESTAMP	アカウント初回発行日時。
updated_at	TIMESTAMPTZ	-	YES	CURRENT_TIMESTAMP	最終シークレット更新・再ログイン日時。

RLS ポリシー: anon_deny_device_secrets FOR ALL USING (false); (PostgREST anon 直接参照禁止)

CREATE TABLE IF NOT EXISTS device_secrets (
    user_uuid UUID 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
);

device_tokens Push通知デバイストークン

端末・通話番号 RLS 有効

アプリの着信時 CallKit (Apple Push Notification VoIP) および FCM (Firebase Cloud Messaging) の通知トークンを保持します。iOS では通話用と通常アラート用トークンが独立して登録されます。

カラム名	データ型	キー/制約	NULL	デフォルト値	説明
id	UUID	PK	NO	gen_random_uuid()	レコード一意識別子。
user_uuid	UUID	UNIQUE	NO	-	端末内部識別子 (UUID)。1端末につき1つの最新トークンが紐付きます。
token	TEXT	-	YES	''	代表 Push トークン（VoIP / FCM）。
platform	VARCHAR(20)	CHECK	YES	'ios'	OS プラットフォーム (`'ios'`, `'android'`, `'ios_voip'`, `'ios_alert'`, `'android_fcm'`)。
voip_token	TEXT	-	YES	''	iOS CallKit 専用 VoIP PushKit トークン。
apns_token	TEXT	-	YES	''	iOS 通常 Alert / バッジ更新用 APNs トークン。
raw_data	JSONB	-	YES	'{}'::jsonb	バッジ数や端末メタデータ等の生 JSON 辞書。
bundle_id	VARCHAR(100)	-	YES	''	アプリバンドル ID（例: `uk.kicensa.tsuwa`）。
updated_at	TIMESTAMPTZ	-	YES	CURRENT_TIMESTAMP	トークン最終登録・リフレッシュ日時。

インデックス: CREATE INDEX idx_device_tokens_uuid ON device_tokens(user_uuid);

CREATE TABLE IF NOT EXISTS device_tokens (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_uuid UUID UNIQUE NOT NULL,
    token TEXT DEFAULT '',
    platform VARCHAR(20) DEFAULT 'ios',
    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 idx_device_tokens_uuid ON device_tokens(user_uuid);

closed_group_policies クローズドグループ招待制限ポリシー

端末・通話番号

指定された番号が所属するクローズドポッドへの招待制限・着信許可ポリシーを保持します。

カラム名	データ型	キー/制約	NULL	デフォルト値	説明
user_uuid	UUID	PK	NO	-	対象端末の内部識別子 (UUID)。
group_code	VARCHAR(20)	-	NO	-	対象ポッド番号（8桁）。
policy	VARCHAR(30)	-	YES	'closed_members_only'	制限種別（クローズドメンバー限定など）。
updated_at	TIMESTAMPTZ	-	YES	CURRENT_TIMESTAMP	ポリシー最終設定日時。

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

uacreg Kamailio 代理レジストラ (UAC Registrant)

SIP・Kamailio RLS 有効

Kamailio SIP サーバーが対向キャリア（楽天0038等）および内線 PBX（Asterisk 等）へ常時代理レジスト（REGISTER）するためのレコードを管理します。セキュリティ強化のため平文パスワード（auth_password）は即時空文字消去され、MD5 ダイジェスト（auth_ha1）に統一されています。アプリ休眠時でも Kamailio が代理着信し、VoIP Push で端末を起床させます。

カラム名	データ型	キー/制約	NULL	デフォルト値	説明
id	SERIAL	PK	NO	-	自動連番 ID。
l_uuid	VARCHAR(64)	UNIQUE	NO	''	代理登録一意キー（形式: `uac_{peer_id}_{sip_username}`）。
r_username	VARCHAR(64)	INDEX	NO	''	対向 PBX の内線番号（例: `208`）。
r_domain	VARCHAR(128)	-	NO	''	対向 PBX の IP / ホスト名（例: `172.29.13.1`）。
auth_ha1	VARCHAR(128)	-	NO	''	Kamailio 5.8 認証ハッシュ（`MD5(user:realm:pass)`）。
auth_proxy	VARCHAR(128)	-	NO	''	送信先 SIP URI（例: `sip:172.29.13.1:5060`）。空文字不可。
expires	INT	-	NO	300	SIP レジストレーション有効期間（秒）。

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)
);

sip_account_activity SIP アカウント最終アクセス履歴

SIP・Kamailio

各端末の最終オンライン・アクティビティ時刻を記録します。7 日間以上アクセスのない休眠アカウントの代理登録（uacreg）を自動パージするために使用されます。

カラム名	データ型	キー/制約	NULL	デフォルト値	説明
user_uuid	UUID	PK	NO	-	端末内部識別子 (UUID)。
last_seen	TIMESTAMPTZ	-	YES	CURRENT_TIMESTAMP	最終アクセス日時。

CREATE TABLE IF NOT EXISTS sip_account_activity (
    user_uuid UUID PRIMARY KEY,
    last_seen TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

version Kamailio スキーマバージョン

SIP・Kamailio

Kamailio モジュールが起動時にテーブル構造の整合性を確認するための標準バージョン管理テーブルです。

カラム名	データ型	キー/制約	NULL	デフォルト値	説明
table_name	VARCHAR(32)	PK	NO	-	テーブル名（例: `uacreg`）。
table_version	INT	-	NO	0	バージョン番号（例: `5`）。

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)
);