Database

Overview

Lillo uses a serverless-first database architecture powered by Vercel Postgres, designed for scalable AI agent interactions and content management.

Schema Structure

Core Tables

1. chat_messages

CREATE TABLE chat_messages (
  id SERIAL PRIMARY KEY,
  message_id BIGINT NOT NULL,
  chat_id BIGINT NOT NULL,
  user_id BIGINT NOT NULL,
  user_first_name VARCHAR(255) NOT NULL,
  username VARCHAR(255),
  role VARCHAR(50) NOT NULL,
  content TEXT NOT NULL,
  timestamp BIGINT NOT NULL,
  reply_to_message_id BIGINT,
  agent_id VARCHAR(255) NOT NULL,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
  UNIQUE(chat_id, message_id, agent_id)
);

-- Indexes
CREATE INDEX idx_chat_messages_chat_id_timestamp 
ON chat_messages(chat_id, timestamp DESC);

CREATE INDEX idx_chat_messages_agent_id
ON chat_messages(agent_id);

2. posts

CREATE TABLE posts (
  id SERIAL PRIMARY KEY,
  type VARCHAR(50) NOT NULL,
  content TEXT NOT NULL,
  timestamp BIGINT NOT NULL,
  location VARCHAR(255),
  prompt TEXT,
  image_url TEXT,
  tweet_id VARCHAR(255),
  created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

-- Index
CREATE INDEX idx_posts_timestamp 
ON posts(timestamp DESC);

3. user_data

CREATE TABLE user_data (
  id SERIAL PRIMARY KEY,
  wallet_address VARCHAR(255) UNIQUE,
  telegram_id BIGINT UNIQUE,
  username VARCHAR(255),
  first_name VARCHAR(255),
  token_balance VARCHAR(255),
  last_verified BIGINT,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

-- Indexes
CREATE INDEX idx_user_data_telegram_id 
ON user_data(telegram_id);

CREATE INDEX idx_user_data_wallet 
ON user_data(wallet_address);

4. command_metrics

CREATE TABLE command_metrics (
  id SERIAL PRIMARY KEY,
  command VARCHAR(50) NOT NULL,
  user_id BIGINT NOT NULL,
  chat_id BIGINT NOT NULL,
  duration INTEGER NOT NULL,
  success BOOLEAN NOT NULL,
  error TEXT,
  timestamp BIGINT NOT NULL,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

-- Index
CREATE INDEX idx_command_metrics_timestamp 
ON command_metrics(timestamp DESC);

5. gerta_poses

CREATE TABLE gerta_poses (
  id SERIAL PRIMARY KEY,
  filename VARCHAR(255) NOT NULL,
  last_used TIMESTAMP WITH TIME ZONE,
  usage_count INTEGER DEFAULT 0,
  command_type VARCHAR(50),
  created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT unique_filename UNIQUE (filename)
);

-- Index
CREATE INDEX idx_gerta_poses_last_used 
ON gerta_poses(last_used);

6. illustrations

CREATE TABLE illustrations (
  id SERIAL PRIMARY KEY,
  filename TEXT UNIQUE NOT NULL,
  last_used TIMESTAMP,
  usage_count INTEGER DEFAULT 0,
  command_type TEXT,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Index
CREATE INDEX idx_illustrations_last_used
ON illustrations(last_used);

7. agents

CREATE TABLE agents (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  name VARCHAR(255) NOT NULL,
  type VARCHAR(50) NOT NULL,
  
  -- Basic Info
  ticker VARCHAR(50),
  token_address VARCHAR(255),
  network VARCHAR(50),
  
  -- Encrypted Telegram Config
  telegram_config TEXT,
  
  -- Links
  website_url VARCHAR(255),
  twitter_handle VARCHAR(255),
  telegram_group VARCHAR(255),
  
  -- Agent Configuration
  system_prompt TEXT,
  capabilities JSONB DEFAULT '{}',
  
  -- Metadata
  created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
  is_active BOOLEAN DEFAULT true,
  
  -- Constraints
  CONSTRAINT unique_agent_name UNIQUE (name),
  CONSTRAINT unique_token_address UNIQUE (token_address)
);

-- Index
CREATE INDEX idx_agents_name 
ON agents(name);

8. model_preferences

CREATE TABLE model_preferences (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  agent_id UUID NOT NULL REFERENCES agents(id),
  chat_id BIGINT NOT NULL,
  model_type VARCHAR(50) NOT NULL,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT unique_agent_chat UNIQUE (agent_id, chat_id)
);

-- Index
CREATE INDEX idx_model_preferences_lookup
ON model_preferences(agent_id, chat_id);

Type Definitions

Core Types

// Agent Configuration
interface Agent {
  id: string;
  name: string;
  type: string;
  secureConfig?: SecureConfig;
  tokenConfig?: TokenConfig;
  systemIdentity?: SystemIdentity;
  characterConfig?: CharacterConfig;
  capabilities?: Record<string, boolean>;
  isActive: boolean;
  createdAt: Date;
  updatedAt: Date;
}

// Message Data
interface ChatMessageData {
  message_id: number;
  chat_id: number;
  user_id: number;
  user_first_name: string;
  username?: string;
  role: 'user' | 'assistant';
  content: string;
  timestamp: number;
  reply_to_message_id?: number;
  agent_id: string;
  created_at: Date;
}

// Command Metrics
interface CommandMetrics {
  command: string;
  userId: number;
  chatId: number;
  duration: number;
  success: boolean;
  error?: string;
  timestamp: number;
}

// Post Data
interface PostData {
  id: string;
  type: string;
  content: string;
  timestamp: number;
  location?: string;
  prompt?: string;
  imageUrl?: string;
  tweetId?: string;
  created_at: Date;
  updated_at: Date;
}

Database Operations

Message Operations

// Store message
async function storeMessage(entry: {
  messageId: number;
  chatId: number;
  userId: number;
  userFirstName: string;
  username?: string;
  role: 'user' | 'assistant';
  content: string;
  timestamp: number;
  replyToMessageId?: number;
  agentId: string;
}): Promise<void>

// Get chat history
async function getRecentChatHistory(
  chatId: number,
  agentId: string,
  limit: number = 20
): Promise<ChatMessageData[]>

Agent Operations

// Get active agent
async function getActiveAgent(): Promise<Agent | null>

// Get agent by name
async function getAgentByName(name: string): Promise<Agent | null>

// Create agent
async function createAgent(agent: Omit<Agent, 'id' | 'createdAt' | 'updatedAt'>): Promise<Agent>

// Update agent
async function updateAgent(id: string, updates: Partial<Agent>): Promise<Agent>

Metrics Operations

// Store command metrics
async function storeCommandMetrics(metrics: CommandMetrics): Promise<void>

// Get recent metrics
async function getRecentCommandMetrics(
  limit: number = 100
): Promise<CommandMetrics[]>

Performance Considerations

1. Indexing Strategy

• Compound indexes for frequently accessed fields

• Timestamp-based sorting optimization

• Unique constraints for data integrity

• Agent-specific lookups

2. Query Optimization

• Prepared statements

• Efficient joins

• Limit-based pagination

• Type-safe queries

3. Connection Management

• Connection pooling

• Automatic retries

• Error handling

• Transaction support

Security Features

1. Data Protection

• Encrypted agent configurations

• Secure credential storage

• Type validation

• SQL injection prevention

2. Access Control

• Role-based permissions

• Agent-specific access

• Command restrictions

• Data isolation

Best Practices

1. Data Access

• Use type-safe operations

• Implement proper error handling

• Follow the principle of least privilege

• Use parameterized queries

2. Schema Management

• Version control migrations

• Backward compatibility

• Clear naming conventions

• Proper constraints

3. Performance

• Regular index maintenance

• Query optimization

• Connection pooling

• Error handling

Related Documentation

• Architecture Overview
• Agent System
• API Reference