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

Last updated 4 months ago

Overview

Schema Structure

Core Tables

1. chat_messages

2. posts

3. user_data

4. command_metrics

5. gerta_poses

6. illustrations

7. agents

8. model_preferences

Type Definitions

Core Types

Database Operations

Message Operations

Agent Operations

Metrics Operations

Performance Considerations

1. Indexing Strategy

2. Query Optimization

3. Connection Management

Security Features

1. Data Protection

2. Access Control

Best Practices

1. Data Access

2. Schema Management

3. Performance

Related Documentation