The Database module provides async PostgreSQL operations for storing and retrieving Discord messages.
init_db
Initialize the database connection pool and create schema.
Creates:
- Connection pool to PostgreSQL
messages table for message storagechannel_status table for backfill tracking- Optimized indexes for queries
Example
from core.database import init_db
await init_db()
close_db
Close the database connection pool.
Should be called on application shutdown to gracefully close connections.
Example
from core.database import close_db
await close_db()
store_message
Store or update a message in the database.
async def store_message(
message_id: int,
channel_id: int,
author_id: int,
author_name: str,
content: str,
created_at: datetime,
timestamp_str: str
)
Discord user ID of message author
Display name of message author
Message creation timestamp
Formatted timestamp string
Behavior
Uses ON CONFLICT to update existing messages, enabling edit tracking.
Example
from core.database import store_message
from datetime import datetime
await store_message(
message_id=123456789,
channel_id=987654321,
author_id=111222333,
author_name="JohnDoe",
content="Hello world!",
created_at=datetime.now(),
timestamp_str="2024-03-04 10:30:00"
)
delete_message
Delete a message from the database.
async def delete_message(message_id: int)
Discord message ID to delete
Example
from core.database import delete_message
await delete_message(123456789)
get_messages
Retrieve the most recent messages for a channel in chronological order.
async def get_messages(channel_id: int, limit: int = 2000) -> List[Dict]
Maximum number of messages to retrieve
List of message dictionaries, oldest to newest
{
"message_id": 123456789,
"channel_id": 987654321,
"author_id": 111222333,
"author_name": "JohnDoe",
"content": "Hello world!",
"created_at": datetime(...)
}
Example
from core.database import get_messages
messages = await get_messages(channel_id=987654321, limit=100)
for msg in messages:
print(f"{msg['author_name']}: {msg['content']}")
get_message_count
Get the number of messages stored for a channel.
async def get_message_count(channel_id: int) -> int
Count of messages in the database
Example
from core.database import get_message_count
count = await get_message_count(987654321)
print(f"Channel has {count} messages")
get_latest_message_id
Get the ID of the newest message stored for a channel.
async def get_latest_message_id(channel_id: int) -> Optional[int]
Message ID of newest message, or None if no messages
Example
from core.database import get_latest_message_id
latest_id = await get_latest_message_id(987654321)
if latest_id:
print(f"Latest message ID: {latest_id}")
get_oldest_message_id
Get the ID of the oldest message stored for a channel.
async def get_oldest_message_id(channel_id: int) -> Optional[int]
Message ID of oldest message, or None if no messages
Example
from core.database import get_oldest_message_id
oldest_id = await get_oldest_message_id(987654321)
if oldest_id:
print(f"Oldest message ID: {oldest_id}")
is_channel_fully_backfilled
Check if a channel is marked as fully backfilled.
async def is_channel_fully_backfilled(channel_id: int) -> bool
True if channel is fully backfilled
Example
from core.database import is_channel_fully_backfilled
if await is_channel_fully_backfilled(987654321):
print("Channel is fully backfilled")
mark_channel_fully_backfilled
Mark a channel as fully backfilled.
async def mark_channel_fully_backfilled(channel_id: int, status: bool = True)
Example
from core.database import mark_channel_fully_backfilled
await mark_channel_fully_backfilled(987654321)
Database Schema
messages table
CREATE TABLE 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
);
CREATE INDEX idx_messages_channel_created
ON messages (channel_id, created_at DESC);
channel_status table
CREATE TABLE channel_status (
channel_id BIGINT PRIMARY KEY,
is_fully_backfilled BOOLEAN DEFAULT FALSE,
last_updated TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);
Configuration
Requires POSTGRES_URL environment variable:
POSTGRES_URL=postgresql://user:pass@localhost:5432/junkie