Moltbot으로 대량 콘텐츠 관리하기: SQLite 연동 전략

-- 메인 콘텐츠 테이블
CREATE TABLE content (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  title TEXT NOT NULL,
  topic TEXT,
  category TEXT,
  tags TEXT,  -- JSON array로 저장
  idea TEXT,  -- 초기 아이디어
  status TEXT DEFAULT 'idea',  -- idea, draft, review, approved, published
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- 콘텐츠 버전 관리
CREATE TABLE content_version (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  content_id INTEGER NOT NULL,
  version_number INTEGER NOT NULL,
  body TEXT NOT NULL,  -- 실제 콘텐츠
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (content_id) REFERENCES content(id)
);

-- 배포 채널
CREATE TABLE channel (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  name TEXT NOT NULL UNIQUE,  -- blog, twitter, linkedin, newsletter
  config TEXT  -- JSON으로 채널별 설정 저장
);

-- 배포 정보
CREATE TABLE publication (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  content_id INTEGER NOT NULL,
  channel_id INTEGER NOT NULL,
  scheduled_at DATETIME,
  published_at DATETIME,
  status TEXT DEFAULT 'scheduled',  -- scheduled, published, failed
  metadata TEXT,  -- JSON으로 배포 결과 저장
  FOREIGN KEY (content_id) REFERENCES content(id),
  FOREIGN KEY (channel_id) REFERENCES channel(id)
);

sqlite3 /path/to/content.db \
  "SELECT c.title, ch.name, p.scheduled_at
   FROM publication p
   JOIN content c ON p.content_id = c.id
   JOIN channel ch ON p.channel_id = ch.id
   WHERE DATE(p.scheduled_at) = DATE('now')
   AND p.status = 'scheduled';"

# publish_content.py
import sqlite3
import sys

def publish_content(content_id, channel_id):
    conn = sqlite3.connect('/path/to/content.db')
    cursor = conn.cursor()

    try:
        # 콘텐츠와 채널 정보 조회
        cursor.execute("""
            SELECT c.title, cv.body, ch.name, ch.config
            FROM content c
            JOIN content_version cv ON c.id = cv.content_id
            JOIN channel ch ON ch.id = ?
            WHERE c.id = ?
            ORDER BY cv.version_number DESC
            LIMIT 1
        """, (channel_id, content_id))

        result = cursor.fetchone()
        if not result:
            raise Exception("Content not found")

        title, body, channel_name, config = result

        # 실제 배포 로직 (생략)
        # publish_to_channel(channel_name, title, body, config)

        # 배포 완료 상태 업데이트
        cursor.execute("""
            UPDATE publication
            SET status = 'published',
                published_at = CURRENT_TIMESTAMP
            WHERE content_id = ? AND channel_id = ?
        """, (content_id, channel_id))

        conn.commit()
        print(f"Successfully published: {title} to {channel_name}")

    except Exception as e:
        conn.rollback()
        print(f"Failed to publish: {e}")
        sys.exit(1)
    finally:
        conn.close()

if __name__ == "__main__":
    publish_content(int(sys.argv[1]), int(sys.argv[2]))

import anthropic
import base64

def analyze_image(image_path, prompt):
    with open(image_path, "rb") as f:
        image_data = base64.standard_b64encode(f.read()).decode("utf-8")

    # 확장자로 media_type 결정
    ext = image_path.split('.')[-1].lower()
    media_types = {'png': 'image/png', 'jpg': 'image/jpeg', 'jpeg': 'image/jpeg', 'gif': 'image/gif', 'webp': 'image/webp'}
    media_type = media_types.get(ext, 'image/png')

    client = anthropic.Anthropic()
    response = client.messages.create(
        model="claude-sonnet-4-20250514",
        max_tokens=1024,
        messages=[{
            "role": "user",
            "content": [
                {
                    "type": "image",
                    "source": {
                        "type": "base64",
                        "media_type": media_type,
                        "data": image_data,
                    },
                },
                {"type": "text", "text": prompt}
            ],
        }],
    )
    return response.content[0].text

{
    "type": "image",
    "source": {
        "type": "url",
        "url": "https://example.com/image.png",
    },
}

from PIL import Image
import io
import base64

def prepare_image_for_api(path, max_size_mb=4, max_dimension=4000):
    """API 제한에 맞게 이미지 리사이즈 후 base64 반환"""
    img = Image.open(path)

    # 해상도 줄이기
    if max(img.size) > max_dimension:
        ratio = max_dimension / max(img.size)
        new_size = (int(img.size[0] * ratio), int(img.size[1] * ratio))
        img = img.resize(new_size, Image.LANCZOS)

    # 품질 조정하며 크기 맞추기
    quality = 95
    while quality > 20:
        buffer = io.BytesIO()
        img.save(buffer, format='JPEG', quality=quality)
        size_mb = buffer.tell() / (1024 * 1024)
        if size_mb <= max_size_mb:
            buffer.seek(0)
            return base64.standard_b64encode(buffer.read()).decode('utf-8')
        quality -= 10

    raise ValueError("이미지를 충분히 압축할 수 없습니다")

-- 콘텐츠에 연결된 이미지
CREATE TABLE content_image (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  content_id INTEGER NOT NULL,
  file_path TEXT NOT NULL,        -- 로컬 파일 경로
  alt_text TEXT,                  -- 대체 텍스트
  description TEXT,               -- AI가 분석한 이미지 설명
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (content_id) REFERENCES content(id)
);

INSERT INTO content (title, topic, category, tags, status)
VALUES (
  'Moltbot으로 콘텐츠 관리하기',
  'AI 에이전트와 DB 연동',
  'AI-ML',
  '["moltbot", "automation"]',
  'idea'
);

INSERT INTO content_version (content_id, version_number, body)
VALUES (1, 1, '초안 내용...');

UPDATE content SET status = 'draft' WHERE id = 1;

INSERT INTO content_version (content_id, version_number, body)
VALUES (1, 2, '수정된 내용...');

UPDATE content SET status = 'approved' WHERE id = 1;

INSERT INTO publication (content_id, channel_id, scheduled_at, status)
VALUES
  (1, 1, '2026-01-31 10:00:00', 'scheduled'),
  (1, 2, '2026-01-31 10:00:00', 'scheduled');

# Moltbot Cron 설정
0 10 * * * python /path/to/check_and_publish.py