Files
dbx-main/scripts/sql/outreach_db_init.sql
king 48cd7f0878 feat(안내문 발송): DM 수동 보내기 — 이름 지우기·본문 편집·저장한 본문 목록
- 이름 목록: × 한 명씩 빼기·[전체 삭제], 이름 넣기는 목록에 더함
- 본문: 바로 고침(plaintext-only), {이름} 자리 = 분홍 칩, 이름 누르면 고친 글 + 이름 복사
- 저장한 본문(본문 오른쪽): 설정 DM 문구 + 저장한 것, 고르기·[저장]·[새 본문으로 저장]·× 지우기
- outreach_db 마이그레이션 002 outreach_dm_bodies + POST/PUT/DELETE /outreach/api/dm-bodies
  (002 미적용이면 목록 안내만, API 503)
- 캐시 버전 20261030c, 단위·API·실제 PG·브라우저 테스트

Co-Authored-By: Claude Opus 5.5 <noreply@anthropic.com>
2026-10-30 16:51:10 +09:00

136 lines
8.3 KiB
PL/PgSQL

-- =====================================================================
-- outreach_db 초기화 스크립트 (PostgreSQL) — 안내문 발송 모듈(2026-10-27)
-- =====================================================================
-- 리스트(이메일 주소·인스타그램 주소 + 이름)에 안내문을 보낸다 — 이메일은 보내는 사람 본인 Gmail 로 한 통씩,
-- 인스타는 DM 문구 복사 + DM 창 열기(반자동 — 인스타 공식 API 는 처음 연락하는 사람에게 DM 을 보낼 수 없다).
-- 멱등(idempotent): 여러 번 실행해도 안전. 기존 데이터를 삭제하지 않는다. DROP/TRUNCATE 없음.
--
-- 실행 방법 (운영 PostgreSQL 컨테이너명: postgres-db, 백업 먼저):
--
-- 1) DB / 역할 / 테이블 생성 (superuser 로 1회)
-- read -s -p "outreach_app password: " APP_PWD; echo
-- docker exec -i postgres-db psql -U postgres -v app_password="$APP_PWD" < scripts/sql/outreach_db_init.sql
--
-- 2) main-app .env 에 연결 정보 등록
-- OUTREACH_DB_URL=postgresql://outreach_app:<APP_PWD>@postgres-db:5432/outreach_db
--
-- 3) main-app 재기동
-- cd /opt/www/main && docker compose up -d --build web
-- =====================================================================
\set ON_ERROR_STOP on
SELECT 'CREATE DATABASE outreach_db ENCODING ''UTF8'' TEMPLATE template0'
WHERE NOT EXISTS (SELECT 1 FROM pg_database WHERE datname = 'outreach_db')
\gexec
SELECT 'CREATE ROLE outreach_app LOGIN PASSWORD ' || quote_literal(:'app_password')
WHERE NOT EXISTS (SELECT 1 FROM pg_roles WHERE rolname = 'outreach_app')
\gexec
SELECT 'ALTER ROLE outreach_app WITH LOGIN PASSWORD ' || quote_literal(:'app_password')
\gexec
GRANT CONNECT ON DATABASE outreach_db TO outreach_app;
\connect outreach_db
CREATE OR REPLACE FUNCTION outreach_set_updated_at() RETURNS trigger AS $$
BEGIN
NEW.updated_at := now();
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
-- ════════════════════════════════════════════════════════════
-- 1) 설정 — 키 하나 = 값(JSONB). 'template' = {subject, body, sender_name, footer, ad_label}
-- ════════════════════════════════════════════════════════════
CREATE TABLE IF NOT EXISTS outreach_settings (
key TEXT PRIMARY KEY,
value JSONB NOT NULL DEFAULT '{}'::jsonb,
updated_by TEXT NOT NULL DEFAULT '',
updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
-- ════════════════════════════════════════════════════════════
-- 2) 메일 본문 아래 사진 — 설정에서 올림(서버가 가로 600px JPEG 로 줄여 저장), 순서대로
-- ════════════════════════════════════════════════════════════
CREATE TABLE IF NOT EXISTS outreach_images (
id SERIAL PRIMARY KEY,
filename TEXT NOT NULL DEFAULT '',
data BYTEA NOT NULL,
width INTEGER NOT NULL DEFAULT 0,
height INTEGER NOT NULL DEFAULT 0,
sort_order INTEGER NOT NULL DEFAULT 0,
created_by TEXT NOT NULL DEFAULT '',
created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
-- ════════════════════════════════════════════════════════════
-- 3) 리스트 — 주소 하나 = 한 줄. kind 는 주소를 보고 정함(email | instagram), handle = 정리한 주소(이메일 소문자 / 인스타 계정명)
-- status: 대기 → 발송중 → 보냄 | 실패 (다시 보내려면 대기로)
-- ════════════════════════════════════════════════════════════
CREATE TABLE IF NOT EXISTS outreach_recipients (
id BIGSERIAL PRIMARY KEY,
kind TEXT NOT NULL CHECK (kind IN ('email', 'instagram')),
handle TEXT NOT NULL CHECK (char_length(handle) BETWEEN 1 AND 320),
address TEXT NOT NULL DEFAULT '', -- 입력한 그대로(보여 주기)
name TEXT NOT NULL DEFAULT '' CHECK (char_length(name) <= 100),
memo TEXT NOT NULL DEFAULT '' CHECK (char_length(memo) <= 500),
status TEXT NOT NULL DEFAULT '대기' CHECK (status IN ('대기', '발송중', '보냄', '실패')),
sent_at TIMESTAMPTZ,
sent_by TEXT NOT NULL DEFAULT '',
sent_via TEXT NOT NULL DEFAULT '' CHECK (sent_via IN ('', 'gmail', 'dm')),
last_error TEXT NOT NULL DEFAULT '',
created_by TEXT NOT NULL DEFAULT '',
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
UNIQUE (kind, handle)
);
CREATE INDEX IF NOT EXISTS idx_outreach_recipients_status ON outreach_recipients (status, id);
DROP TRIGGER IF EXISTS trg_outreach_recipients_updated_at ON outreach_recipients;
CREATE TRIGGER trg_outreach_recipients_updated_at BEFORE UPDATE ON outreach_recipients
FOR EACH ROW EXECUTE FUNCTION outreach_set_updated_at();
-- ════════════════════════════════════════════════════════════
-- 4) 보낸 기록 — 한 번 보낼 때마다 한 줄(받는 사람을 지워도 남음). 본문·토큰은 남기지 않는다.
-- ════════════════════════════════════════════════════════════
CREATE TABLE IF NOT EXISTS outreach_logs (
id BIGSERIAL PRIMARY KEY,
recipient_id BIGINT,
kind TEXT NOT NULL DEFAULT '',
address TEXT NOT NULL DEFAULT '',
name TEXT NOT NULL DEFAULT '',
subject TEXT NOT NULL DEFAULT '',
via TEXT NOT NULL DEFAULT '',
sent_by TEXT NOT NULL DEFAULT '',
ok BOOLEAN NOT NULL DEFAULT FALSE,
error TEXT NOT NULL DEFAULT '',
sent_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS idx_outreach_logs_recipient ON outreach_logs (recipient_id, sent_at);
-- ════════════════════════════════════════════════════════════
-- 4-1) DM 수동 보내기 탭의 저장한 본문({이름} 이 든 글, 최대 1000자) — 마이그레이션 002 와 같음(2026-10-30)
-- ════════════════════════════════════════════════════════════
CREATE TABLE IF NOT EXISTS outreach_dm_bodies (
id BIGSERIAL PRIMARY KEY,
title TEXT NOT NULL DEFAULT '',
body TEXT NOT NULL,
created_by TEXT NOT NULL DEFAULT '',
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
updated_by TEXT NOT NULL DEFAULT '',
updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
-- ════════════════════════════════════════════════════════════
-- 5) 권한 (outreach_app: CRUD only)
-- ════════════════════════════════════════════════════════════
GRANT USAGE ON SCHEMA public TO outreach_app;
GRANT SELECT, INSERT, UPDATE, DELETE ON outreach_settings, outreach_images, outreach_recipients, outreach_logs, outreach_dm_bodies TO outreach_app;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO outreach_app;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO outreach_app;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT USAGE, SELECT ON SEQUENCES TO outreach_app;
SELECT 'outreach_db ready' AS status;