migrations/001_initial_schema.sql - master - PostmenBlake/NotificationPreferencesService

migrations/001_initial_schema.sql
61 строка2 KB
kurava
feat: initial commit - Notification Preferences Service
17 июн 2026, 09:11
17 июн 2026, 09:1148825c3
-- Создаём таблицу пользовательских настроек
CREATE TABLE IF NOT EXISTS user_preferences (
  id SERIAL PRIMARY KEY,
  user_id VARCHAR(255) NOT NULL,
  notification_type VARCHAR(50) NOT NULL,
  channel VARCHAR(20) NOT NULL,
  enabled BOOLEAN NOT NULL DEFAULT false,
  quiet_hours JSONB,
  updated_at TIMESTAMP NOT NULL DEFAULT NOW(),
  CONSTRAINT unique_user_preference UNIQUE (user_id, notification_type, channel)
);

-- Создаём индексы для быстрого поиска
CREATE INDEX idx_user_preferences_user_id ON user_preferences(user_id);
CREATE INDEX idx_user_preferences_type_channel ON user_preferences(notification_type, channel);

-- Создаём таблицу глобальных политик
CREATE TABLE IF NOT EXISTS global_policies (
  id SERIAL PRIMARY KEY,
  notification_type VARCHAR(50) NOT NULL,
  channel VARCHAR(20) NOT NULL,
  region VARCHAR(20) NOT NULL,
  allowed BOOLEAN NOT NULL DEFAULT true,
  reason TEXT NOT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT NOW(),
  CONSTRAINT unique_policy UNIQUE (notification_type, channel, region)
);

-- Индексы для глобальных политик
CREATE INDEX idx_global_policies_lookup ON global_policies(notification_type, channel, region);

-- Добавляем начальные дефолтные политики
INSERT INTO global_policies (notification_type, channel, region, allowed, reason)
VALUES 
  ('marketing_sms', 'sms', 'EU', false, 'GDPR compliance: marketing SMS prohibited in EU region')
ON CONFLICT (notification_type, channel, region) DO NOTHING;

-- Создаём функцию для автоматического обновления updated_at
CREATE OR REPLACE FUNCTION update_updated_at_column()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = NOW();
    RETURN NEW;
END;
$$ language 'plpgsql';

CREATE TRIGGER update_user_preferences_updated_at 
  BEFORE UPDATE ON user_preferences 
  FOR EACH ROW 
  EXECUTE FUNCTION update_updated_at_column();

-- Создаём представление для быстрой проверки
CREATE OR REPLACE VIEW user_preferences_view AS
SELECT 
  user_id,
  notification_type,
  channel,
  enabled,
  quiet_hours,
  updated_at
FROM user_preferences;