255 lines
12 KiB
PL/PgSQL
255 lines
12 KiB
PL/PgSQL
-- Migration 009: Secure Decentralized Messenger System
|
|
-- Creates tables for encrypted P2P messaging via IPFS/libp2p
|
|
|
|
-- Messenger profile for each user (one per user)
|
|
CREATE TABLE IF NOT EXISTS messenger_profiles (
|
|
id SERIAL PRIMARY KEY,
|
|
user_id INTEGER NOT NULL UNIQUE REFERENCES users(id) ON DELETE CASCADE,
|
|
messaging_id UUID NOT NULL UNIQUE DEFAULT gen_random_uuid(),
|
|
peer_id VARCHAR(100), -- libp2p peer ID (when connected)
|
|
display_name VARCHAR(100), -- Optional display name for messaging
|
|
status VARCHAR(20) DEFAULT 'offline', -- online, offline, away, busy, invisible
|
|
status_message VARCHAR(255), -- Custom status message
|
|
last_seen_at TIMESTAMP WITH TIME ZONE,
|
|
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
|
|
updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
|
|
);
|
|
|
|
-- User encryption keys (multiple keys per user for rotation)
|
|
CREATE TABLE IF NOT EXISTS messenger_keys (
|
|
id SERIAL PRIMARY KEY,
|
|
user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
|
|
key_type VARCHAR(20) NOT NULL, -- 'identity' (Ed25519), 'encryption' (X25519)
|
|
public_key BYTEA NOT NULL, -- Public key bytes
|
|
encrypted_private_key BYTEA NOT NULL, -- Private key encrypted with user's derived key
|
|
key_salt BYTEA NOT NULL, -- Salt for key derivation
|
|
is_active BOOLEAN DEFAULT TRUE, -- Current active key
|
|
expires_at TIMESTAMP WITH TIME ZONE, -- Optional expiration for rotation
|
|
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
|
|
revoked_at TIMESTAMP WITH TIME ZONE, -- When key was revoked
|
|
UNIQUE(user_id, key_type, is_active) DEFERRABLE INITIALLY DEFERRED
|
|
);
|
|
|
|
-- Contacts/friends list with fine-grained control
|
|
CREATE TABLE IF NOT EXISTS messenger_contacts (
|
|
id SERIAL PRIMARY KEY,
|
|
owner_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
|
|
contact_user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
|
|
contact_messaging_id UUID NOT NULL, -- Cached messaging_id for fast lookups
|
|
alias VARCHAR(100), -- Custom name for this contact
|
|
status VARCHAR(20) DEFAULT 'pending', -- pending, accepted, blocked, muted
|
|
trust_level INTEGER DEFAULT 0, -- 0-100 trust score
|
|
notes TEXT, -- Private notes about contact
|
|
group_ids INTEGER[], -- Array of group IDs this contact belongs to
|
|
can_see_status BOOLEAN DEFAULT TRUE, -- Can this contact see our status
|
|
can_send_messages BOOLEAN DEFAULT TRUE, -- Can this contact message us
|
|
muted_until TIMESTAMP WITH TIME ZONE, -- Temporary mute
|
|
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
|
|
updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
|
|
UNIQUE(owner_id, contact_user_id)
|
|
);
|
|
|
|
-- Contact groups for organization
|
|
CREATE TABLE IF NOT EXISTS messenger_contact_groups (
|
|
id SERIAL PRIMARY KEY,
|
|
owner_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
|
|
name VARCHAR(100) NOT NULL,
|
|
color VARCHAR(7), -- Hex color for UI
|
|
icon VARCHAR(50), -- Icon identifier
|
|
description TEXT,
|
|
sort_order INTEGER DEFAULT 0,
|
|
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
|
|
updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
|
|
UNIQUE(owner_id, name)
|
|
);
|
|
|
|
-- Blocklist with detailed control
|
|
CREATE TABLE IF NOT EXISTS messenger_blocklist (
|
|
id SERIAL PRIMARY KEY,
|
|
owner_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
|
|
blocked_user_id INTEGER REFERENCES users(id) ON DELETE CASCADE,
|
|
blocked_peer_id VARCHAR(100), -- Can block by peer ID even without user
|
|
blocked_messaging_id UUID, -- Can block by messaging ID
|
|
reason VARCHAR(255),
|
|
block_type VARCHAR(20) DEFAULT 'full', -- full, messages_only, status_only
|
|
expires_at TIMESTAMP WITH TIME ZONE, -- Temporary block
|
|
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
|
|
UNIQUE(owner_id, blocked_user_id),
|
|
CHECK (blocked_user_id IS NOT NULL OR blocked_peer_id IS NOT NULL OR blocked_messaging_id IS NOT NULL)
|
|
);
|
|
|
|
-- Conversations (1:1 and group chats)
|
|
CREATE TABLE IF NOT EXISTS messenger_conversations (
|
|
id SERIAL PRIMARY KEY,
|
|
conversation_id UUID NOT NULL UNIQUE DEFAULT gen_random_uuid(),
|
|
type VARCHAR(20) NOT NULL, -- 'direct', 'group', 'channel'
|
|
name VARCHAR(100), -- Group name (NULL for direct)
|
|
description TEXT,
|
|
owner_id INTEGER REFERENCES users(id) ON DELETE SET NULL, -- Creator/admin
|
|
avatar_ipfs_cid VARCHAR(100), -- Group avatar on IPFS
|
|
encrypted_group_key BYTEA, -- Encrypted symmetric key for group
|
|
settings JSONB DEFAULT '{}', -- Group settings
|
|
is_public BOOLEAN DEFAULT FALSE, -- Public groups can be discovered
|
|
max_members INTEGER DEFAULT 100,
|
|
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
|
|
updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
|
|
);
|
|
|
|
-- Conversation participants
|
|
CREATE TABLE IF NOT EXISTS messenger_conversation_members (
|
|
id SERIAL PRIMARY KEY,
|
|
conversation_id UUID NOT NULL REFERENCES messenger_conversations(conversation_id) ON DELETE CASCADE,
|
|
user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
|
|
role VARCHAR(20) DEFAULT 'member', -- owner, admin, moderator, member
|
|
joined_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
|
|
invited_by INTEGER REFERENCES users(id),
|
|
can_send BOOLEAN DEFAULT TRUE,
|
|
can_invite BOOLEAN DEFAULT FALSE,
|
|
is_muted BOOLEAN DEFAULT FALSE,
|
|
muted_until TIMESTAMP WITH TIME ZONE,
|
|
last_read_at TIMESTAMP WITH TIME ZONE, -- For unread tracking
|
|
encrypted_member_key BYTEA, -- Member's copy of group key
|
|
UNIQUE(conversation_id, user_id)
|
|
);
|
|
|
|
-- Messages stored with IPFS CID reference
|
|
CREATE TABLE IF NOT EXISTS messenger_messages (
|
|
id SERIAL PRIMARY KEY,
|
|
message_id UUID NOT NULL UNIQUE DEFAULT gen_random_uuid(),
|
|
conversation_id UUID NOT NULL REFERENCES messenger_conversations(conversation_id) ON DELETE CASCADE,
|
|
sender_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
|
|
sender_messaging_id UUID NOT NULL,
|
|
|
|
-- Content storage (encrypted content on IPFS)
|
|
ipfs_cid VARCHAR(100), -- IPFS CID of encrypted content
|
|
content_preview VARCHAR(100), -- Optional encrypted preview for notifications
|
|
content_type VARCHAR(50) DEFAULT 'text', -- text, image, file, audio, video, reaction
|
|
|
|
-- Message metadata
|
|
reply_to_id UUID REFERENCES messenger_messages(message_id),
|
|
edited_at TIMESTAMP WITH TIME ZONE,
|
|
deleted_at TIMESTAMP WITH TIME ZONE, -- Soft delete
|
|
|
|
-- Delivery tracking
|
|
signature BYTEA NOT NULL, -- Ed25519 signature of message
|
|
nonce BYTEA NOT NULL, -- Encryption nonce
|
|
|
|
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
|
|
);
|
|
|
|
-- Message read receipts
|
|
CREATE TABLE IF NOT EXISTS messenger_read_receipts (
|
|
id SERIAL PRIMARY KEY,
|
|
message_id UUID NOT NULL REFERENCES messenger_messages(message_id) ON DELETE CASCADE,
|
|
user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
|
|
read_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
|
|
UNIQUE(message_id, user_id)
|
|
);
|
|
|
|
-- Peer ID registry for IPFS nodes (for relay/bootstrap management)
|
|
CREATE TABLE IF NOT EXISTS messenger_peer_registry (
|
|
id SERIAL PRIMARY KEY,
|
|
peer_id VARCHAR(100) NOT NULL UNIQUE,
|
|
user_id INTEGER REFERENCES users(id) ON DELETE SET NULL, -- NULL for relay nodes
|
|
peer_type VARCHAR(20) NOT NULL, -- 'user', 'relay', 'bootstrap'
|
|
multiaddrs TEXT[], -- Known multiaddresses
|
|
is_trusted BOOLEAN DEFAULT FALSE, -- Manually trusted
|
|
is_banned BOOLEAN DEFAULT FALSE, -- Banned from network
|
|
ban_reason VARCHAR(255),
|
|
reputation INTEGER DEFAULT 50, -- 0-100 reputation score
|
|
last_seen_at TIMESTAMP WITH TIME ZONE,
|
|
first_seen_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
|
|
connection_count INTEGER DEFAULT 0,
|
|
bytes_transferred BIGINT DEFAULT 0,
|
|
metadata JSONB DEFAULT '{}'
|
|
);
|
|
|
|
-- Relay/bootstrap node configuration
|
|
CREATE TABLE IF NOT EXISTS messenger_relay_nodes (
|
|
id SERIAL PRIMARY KEY,
|
|
name VARCHAR(100) NOT NULL,
|
|
peer_id VARCHAR(100) NOT NULL UNIQUE,
|
|
multiaddrs TEXT[] NOT NULL,
|
|
region VARCHAR(50), -- Geographic region
|
|
priority INTEGER DEFAULT 50, -- Higher = preferred
|
|
is_active BOOLEAN DEFAULT TRUE,
|
|
is_public BOOLEAN DEFAULT TRUE, -- Public bootstrap or private relay
|
|
max_connections INTEGER DEFAULT 1000,
|
|
current_connections INTEGER DEFAULT 0,
|
|
uptime_percent DECIMAL(5,2) DEFAULT 100,
|
|
last_health_check TIMESTAMP WITH TIME ZONE,
|
|
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
|
|
updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
|
|
);
|
|
|
|
-- Indexes for performance
|
|
CREATE INDEX IF NOT EXISTS idx_messenger_profiles_messaging_id ON messenger_profiles(messaging_id);
|
|
CREATE INDEX IF NOT EXISTS idx_messenger_profiles_peer_id ON messenger_profiles(peer_id);
|
|
CREATE INDEX IF NOT EXISTS idx_messenger_profiles_status ON messenger_profiles(status);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_messenger_keys_user_active ON messenger_keys(user_id, is_active);
|
|
CREATE INDEX IF NOT EXISTS idx_messenger_keys_type ON messenger_keys(key_type);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_messenger_contacts_owner ON messenger_contacts(owner_id);
|
|
CREATE INDEX IF NOT EXISTS idx_messenger_contacts_status ON messenger_contacts(status);
|
|
CREATE INDEX IF NOT EXISTS idx_messenger_contacts_messaging_id ON messenger_contacts(contact_messaging_id);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_messenger_blocklist_owner ON messenger_blocklist(owner_id);
|
|
CREATE INDEX IF NOT EXISTS idx_messenger_blocklist_blocked ON messenger_blocklist(blocked_user_id);
|
|
CREATE INDEX IF NOT EXISTS idx_messenger_blocklist_peer ON messenger_blocklist(blocked_peer_id);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_messenger_conversations_type ON messenger_conversations(type);
|
|
CREATE INDEX IF NOT EXISTS idx_messenger_conversations_owner ON messenger_conversations(owner_id);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_messenger_members_conversation ON messenger_conversation_members(conversation_id);
|
|
CREATE INDEX IF NOT EXISTS idx_messenger_members_user ON messenger_conversation_members(user_id);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_messenger_messages_conversation ON messenger_messages(conversation_id);
|
|
CREATE INDEX IF NOT EXISTS idx_messenger_messages_sender ON messenger_messages(sender_id);
|
|
CREATE INDEX IF NOT EXISTS idx_messenger_messages_created ON messenger_messages(created_at DESC);
|
|
CREATE INDEX IF NOT EXISTS idx_messenger_messages_ipfs ON messenger_messages(ipfs_cid);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_messenger_peer_registry_type ON messenger_peer_registry(peer_type);
|
|
CREATE INDEX IF NOT EXISTS idx_messenger_peer_registry_trusted ON messenger_peer_registry(is_trusted);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_messenger_relay_active ON messenger_relay_nodes(is_active, priority DESC);
|
|
|
|
-- Trigger for updated_at timestamps
|
|
CREATE OR REPLACE FUNCTION update_messenger_timestamp()
|
|
RETURNS TRIGGER AS $$
|
|
BEGIN
|
|
NEW.updated_at = NOW();
|
|
RETURN NEW;
|
|
END;
|
|
$$ LANGUAGE plpgsql;
|
|
|
|
CREATE TRIGGER messenger_profiles_updated
|
|
BEFORE UPDATE ON messenger_profiles
|
|
FOR EACH ROW EXECUTE FUNCTION update_messenger_timestamp();
|
|
|
|
CREATE TRIGGER messenger_contacts_updated
|
|
BEFORE UPDATE ON messenger_contacts
|
|
FOR EACH ROW EXECUTE FUNCTION update_messenger_timestamp();
|
|
|
|
CREATE TRIGGER messenger_contact_groups_updated
|
|
BEFORE UPDATE ON messenger_contact_groups
|
|
FOR EACH ROW EXECUTE FUNCTION update_messenger_timestamp();
|
|
|
|
CREATE TRIGGER messenger_conversations_updated
|
|
BEFORE UPDATE ON messenger_conversations
|
|
FOR EACH ROW EXECUTE FUNCTION update_messenger_timestamp();
|
|
|
|
CREATE TRIGGER messenger_relay_nodes_updated
|
|
BEFORE UPDATE ON messenger_relay_nodes
|
|
FOR EACH ROW EXECUTE FUNCTION update_messenger_timestamp();
|
|
|
|
-- Comments for documentation
|
|
COMMENT ON TABLE messenger_profiles IS 'User profiles for the secure messenger system';
|
|
COMMENT ON TABLE messenger_keys IS 'Encryption and signing keys for E2E encrypted messaging';
|
|
COMMENT ON TABLE messenger_contacts IS 'User contact lists with fine-grained permissions';
|
|
COMMENT ON TABLE messenger_blocklist IS 'Blocked users/peers with granular control';
|
|
COMMENT ON TABLE messenger_conversations IS 'Direct and group conversations';
|
|
COMMENT ON TABLE messenger_messages IS 'Message metadata with IPFS content references';
|
|
COMMENT ON TABLE messenger_peer_registry IS 'Registry of all known IPFS peers';
|
|
COMMENT ON TABLE messenger_relay_nodes IS 'Dedicated relay and bootstrap nodes';
|