Database Setup

POSTGRES_URL=postgresql://username:password@host:port/database

# Original URL
postgresql://user:pass@host:5432/db

# Automatically converted to
postgresql+asyncpg://user:pass@host:5432/db

CREATE TABLE IF NOT EXISTS messages (
    message_id BIGINT PRIMARY KEY,
    channel_id BIGINT NOT NULL,
    author_id BIGINT NOT NULL,
    author_name TEXT NOT NULL,
    content TEXT NOT NULL,
    created_at TIMESTAMP WITH TIME ZONE NOT NULL,
    timestamp_str TEXT NOT NULL
);

-- Optimized for fetching recent messages (DESC order)
CREATE INDEX idx_messages_channel_created
ON messages (channel_id, created_at DESC);

-- Fast message_id lookups for upserts
CREATE INDEX idx_messages_message_id
ON messages (message_id);

CREATE TABLE IF NOT EXISTS channel_status (
    channel_id BIGINT PRIMARY KEY,
    is_fully_backfilled BOOLEAN DEFAULT FALSE,
    last_updated TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

db = AsyncPostgresDb(
    db_url=async_db_url,
    session_table="agent_sessions",
    memory_table="user_memories",
)

import asyncpg
from core.config import POSTGRES_URL

pool = await asyncpg.create_pool(POSTGRES_URL)
await create_schema()  # Creates tables if they don't exist

await store_message(
    message_id=123456789,
    channel_id=987654321,
    author_id=111111111,
    author_name="User",
    content="Hello world",
    created_at=datetime.now(),
    timestamp_str="2024-01-15 10:30:00"
)

# Fetch newest messages first (uses DESC index)
SELECT * FROM messages
WHERE channel_id = $1
ORDER BY created_at DESC
LIMIT 2000

# Then reverse to chronological order in Python
return list(reversed(messages))

await close_db()