init-db.sql

-- 1. Создаем пользователя для наших микросервисов NestJS и выдаем ему права репликации
CREATE USER orders_user WITH PASSWORD 'orders_pass' REPLICATION;

-- НАСТРОЙКА ТАЙМ-АУТА: Теперь у orders_user любой запрос во всех БД принудительно ограничен 30 секундами
ALTER USER orders_user SET statement_timeout = 30000; --

-- СОЗДАЕМ ФУНКЦИЮ ДЛЯ PGBOUNCER (Добавьте этот кусок) --
CREATE OR REPLACE FUNCTION public.get_auth_text(p_usename text)
RETURNS TABLE(username text, password text) AS $$
BEGIN
    RETURN QUERY SELECT usename::text, passwd::text FROM pg_catalog.pg_shadow WHERE usename = p_usename;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- Даем права пулеру выполнять эту функцию
GRANT EXECUTE ON FUNCTION public.get_auth_text(text) TO postgres; --

GRANT ALL PRIVILEGES ON DATABASE orders_db TO orders_user;

-- 2. Создаем дополнительные базы данных
-- CREATE DATABASE notifications_db;
-- CREATE DATABASE dlq_db;
CREATE DATABASE notifications_db OWNER orders_user; --
CREATE DATABASE dlq_db OWNER orders_user; --

-- Меняем владельца для автоматически созданной дефолтной базы
ALTER DATABASE orders_db OWNER TO orders_user; --

GRANT ALL PRIVILEGES ON DATABASE notifications_db TO orders_user;
GRANT ALL PRIVILEGES ON DATABASE dlq_db TO orders_user;
GRANT ALL PRIVILEGES ON DATABASE orders_db TO orders_user; --

-- 3. Переключаемся на orders_db и создаем её таблицы
\c orders_db

CREATE TABLE IF NOT EXISTS orders_table (
    id UUID PRIMARY KEY,
    user_id VARCHAR(255) NOT NULL,
    items JSONB NOT NULL DEFAULT '[]',
    status VARCHAR(50) NOT NULL DEFAULT 'CREATED',
    created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS outbox_table (
    id UUID PRIMARY KEY,
    aggregate_type VARCHAR(100) NOT NULL,
    aggregate_id UUID NOT NULL,
    payload JSONB NOT NULL,
    lsn pg_lsn DEFAULT pg_current_wal_lsn(),
    created_at TIMESTAMPTZ DEFAULT NOW()
);

ALTER TABLE outbox_table REPLICA IDENTITY FULL;

ALTER TABLE outbox_table OWNER TO orders_user;

CREATE TABLE IF NOT EXISTS idempotency_commands (
    command_id UUID PRIMARY KEY,
    response JSONB,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS idempotency_events (
    event_id UUID PRIMARY KEY,
    processed_at TIMESTAMPTZ DEFAULT NOW()
);

-- Передаем права на таблицы пользователю микросервисов
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO orders_user;

-- 4. Переключаемся на notifications_db и даем права для pg-boss
\c notifications_db
GRANT ALL ON SCHEMA public TO orders_user;

-- 5. Переключаемся на dlq_db и создаем таблицу для логгера
\c dlq_db
GRANT ALL ON SCHEMA public TO orders_user;

CREATE TABLE IF NOT EXISTS dlq_messages (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    queue_name VARCHAR(255) NOT NULL,
    payload JSONB NOT NULL,
    headers JSONB,
    status VARCHAR(20) NOT NULL DEFAULT 'DEAD',
    created_at TIMESTAMPTZ DEFAULT NOW()
);

GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO orders_user;