439489bde4
- Add auth.notifications table for storing user notifications - Add auth.user_push_tokens table for FCM/APNs tokens - Add investment.distribution_history for daily distribution records - Add investment.distribution_runs for job execution logs Co-Authored-By: Claude Opus 4.5 <noreply@anthropic.com>
74 lines
3.0 KiB
SQL
74 lines
3.0 KiB
SQL
-- ============================================================================
|
|
-- OrbiQuant IA - Trading Platform
|
|
-- Schema: auth
|
|
-- File: tables/11-notifications.sql
|
|
-- Description: User notifications for alerts, trades, distributions, etc.
|
|
-- ============================================================================
|
|
|
|
CREATE TABLE auth.notifications (
|
|
-- Primary Key
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
|
|
-- Foreign Key to Users
|
|
user_id UUID NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
|
|
|
|
-- Notification Content
|
|
type VARCHAR(50) NOT NULL,
|
|
title VARCHAR(255) NOT NULL,
|
|
message TEXT NOT NULL,
|
|
priority VARCHAR(20) NOT NULL DEFAULT 'normal',
|
|
|
|
-- Additional Data
|
|
data JSONB,
|
|
action_url VARCHAR(500),
|
|
icon_type VARCHAR(20) DEFAULT 'info',
|
|
|
|
-- Delivery
|
|
channels TEXT[] NOT NULL DEFAULT '{}',
|
|
|
|
-- Read Status
|
|
is_read BOOLEAN NOT NULL DEFAULT FALSE,
|
|
read_at TIMESTAMPTZ,
|
|
|
|
-- Audit
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
|
|
-- Constraints
|
|
CONSTRAINT valid_priority CHECK (priority IN ('low', 'normal', 'high', 'urgent')),
|
|
CONSTRAINT valid_type CHECK (type IN (
|
|
'alert_triggered',
|
|
'trade_executed',
|
|
'deposit_confirmed',
|
|
'withdrawal_completed',
|
|
'distribution_received',
|
|
'system_announcement',
|
|
'security_alert',
|
|
'account_update'
|
|
)),
|
|
CONSTRAINT valid_icon_type CHECK (icon_type IN ('success', 'warning', 'error', 'info'))
|
|
);
|
|
|
|
-- Indexes for Performance
|
|
CREATE INDEX idx_notifications_user_id ON auth.notifications(user_id);
|
|
CREATE INDEX idx_notifications_created_at ON auth.notifications(created_at DESC);
|
|
CREATE INDEX idx_notifications_unread ON auth.notifications(user_id) WHERE is_read = FALSE;
|
|
CREATE INDEX idx_notifications_type ON auth.notifications(type);
|
|
|
|
-- Table Comments
|
|
COMMENT ON TABLE auth.notifications IS 'User notifications for various platform events';
|
|
|
|
-- Column Comments
|
|
COMMENT ON COLUMN auth.notifications.id IS 'Unique identifier for the notification';
|
|
COMMENT ON COLUMN auth.notifications.user_id IS 'Reference to the user receiving the notification';
|
|
COMMENT ON COLUMN auth.notifications.type IS 'Type of notification (alert_triggered, trade_executed, etc.)';
|
|
COMMENT ON COLUMN auth.notifications.title IS 'Notification title displayed to user';
|
|
COMMENT ON COLUMN auth.notifications.message IS 'Full notification message content';
|
|
COMMENT ON COLUMN auth.notifications.priority IS 'Notification priority (low, normal, high, urgent)';
|
|
COMMENT ON COLUMN auth.notifications.data IS 'Additional structured data as JSON';
|
|
COMMENT ON COLUMN auth.notifications.action_url IS 'URL to navigate when notification is clicked';
|
|
COMMENT ON COLUMN auth.notifications.icon_type IS 'Icon type to display (success, warning, error, info)';
|
|
COMMENT ON COLUMN auth.notifications.channels IS 'Delivery channels used (push, email, in_app, sms)';
|
|
COMMENT ON COLUMN auth.notifications.is_read IS 'Whether the notification has been read';
|
|
COMMENT ON COLUMN auth.notifications.read_at IS 'Timestamp when notification was read';
|
|
COMMENT ON COLUMN auth.notifications.created_at IS 'Timestamp when notification was created';
|