Database Schema

Staque IO uses PostgreSQL to store user data, conversations, deployed resources, and model configurations.

Core Tables

users

Stores user account information and authentication credentials.

Column	Type	Description
`id`	UUID	Primary key
`username`	VARCHAR(255)	Unique username
`email`	VARCHAR(255)	Unique email address
`password_hash`	TEXT	Bcrypt password hash
`name`	VARCHAR(255)	Full name
`organization`	VARCHAR(255)	Organization name
`aws_region`	VARCHAR(50)	Default AWS region
`role`	VARCHAR(50)	User role (super_admin, admin, user)
`is_active`	BOOLEAN	Account status
`created_at`	TIMESTAMP	Account creation time
`updated_at`	TIMESTAMP	Last update time
`last_login`	TIMESTAMP	Last login timestamp

conversations

Top-level container for chat conversations.

Column	Type	Description
`id`	UUID	Primary key
`user_id`	UUID	Foreign key to users
`title`	VARCHAR(255)	Conversation title
`use_case`	VARCHAR(255)	Use case category
`status`	VARCHAR(50)	Conversation status
`created_at`	TIMESTAMP	Creation time
`updated_at`	TIMESTAMP	Last update time

conversation_threads

Individual chat threads with message history and usage tracking.

Column	Type	Description
`id`	UUID	Primary key
`conversation_id`	UUID	Foreign key to conversations
`user_id`	UUID	Foreign key to users
`thread_data`	JSONB	Message history and metadata
`model_id`	VARCHAR(255)	AI model identifier
`tokens_input_total`	INTEGER	Total input tokens
`tokens_output_total`	INTEGER	Total output tokens
`tokens_total`	INTEGER	Total tokens used
`requests_count`	INTEGER	Number of requests
`cost_total_usd`	DECIMAL(10,6)	Total cost in USD
`last_usage_at`	TIMESTAMP	Last activity timestamp
`created_at`	TIMESTAMP	Thread creation time
`updated_at`	TIMESTAMP	Last update time

deployed_resources

Tracks deployed AI model resources across different platforms.

Column	Type	Description
`id`	UUID	Primary key
`conversation_id`	UUID	Foreign key to conversations
`user_id`	UUID	Foreign key to users
`resource_name`	VARCHAR(255)	Resource display name
`resource_type`	VARCHAR(50)	bedrock, sagemaker, nvidia-nim
`aws_resource_id`	VARCHAR(255)	AWS resource identifier
`instance_type`	VARCHAR(100)	Instance type (for SageMaker)
`region`	VARCHAR(50)	AWS region
`status`	VARCHAR(50)	Resource status
`endpoint_url`	TEXT	API endpoint URL
`estimated_hourly_cost`	DECIMAL(10,4)	Hourly cost estimate
`deployment_config`	JSONB	Deployment configuration
`is_active`	BOOLEAN	Active status

model_configurations

Stores model-specific configurations including system prompts and pricing.

Column	Type	Description
`id`	UUID	Primary key (matches resource ID)
`model_id`	VARCHAR(255)	Unique model identifier
`system_prompt`	TEXT	System prompt for the model
`per_1k_input_tokens_usd`	DECIMAL(10,6)	Cost per 1K input tokens
`per_1k_output_tokens_usd`	DECIMAL(10,6)	Cost per 1K output tokens
`updated_by`	UUID	Foreign key to users
`created_at`	TIMESTAMP	Creation timestamp
`updated_at`	TIMESTAMP	Last update timestamp

chat_messages (Legacy)

Legacy table for storing individual chat messages. Being phased out in favor of conversation_threads.

Relationships

users
  ├─ conversations (one-to-many)
  │   ├─ deployed_resources (one-to-many)
  │   └─ conversation_threads (one-to-many)
  │       └─ thread_data.messages (embedded array)
  └─ chat_messages (one-to-many, legacy)

deployed_resources
  └─ model_configurations (one-to-one via id)

Indexes

Key indexes for query performance:

users.username - Unique index for login
users.email - Unique index for email lookup
conversations.user_id - For user's conversation list
conversation_threads.conversation_id - For thread lookup
deployed_resources.aws_resource_id - For resource lookup
model_configurations.model_id - Unique index for model config

Sample Queries

Get User Conversations with Resources

SELECT 
  c.*,
  json_agg(dr.*) as deployed_resources
FROM conversations c
LEFT JOIN deployed_resources dr ON dr.conversation_id = c.id
WHERE c.user_id = $1
GROUP BY c.id
ORDER BY c.updated_at DESC;

Get Thread with Usage Statistics

SELECT 
  id,
  thread_data,
  model_id,
  tokens_input_total,
  tokens_output_total,
  tokens_total,
  requests_count,
  cost_total_usd,
  last_usage_at
FROM conversation_threads
WHERE conversation_id = $1
ORDER BY created_at DESC;

Calculate Monthly Usage by User

SELECT 
  u.username,
  SUM(ct.tokens_total) as total_tokens,
  SUM(ct.cost_total_usd) as total_cost,
  SUM(ct.requests_count) as total_requests
FROM users u
JOIN conversations c ON c.user_id = u.id
JOIN conversation_threads ct ON ct.conversation_id = c.id
WHERE ct.updated_at >= date_trunc('month', now())
GROUP BY u.id, u.username
ORDER BY total_cost DESC;

Migration Scripts

Initial database setup:

-- See database/schema.sql for complete schema
-- Run migrations:
psql -U postgres -d staque_io -f database/schema.sql

Backup and Maintenance

Regular Backups

# Full database backup
pg_dump -U postgres -d staque_io > backup_$(date +%Y%m%d).sql

# Compressed backup
pg_dump -U postgres -d staque_io | gzip > backup_$(date +%Y%m%d).sql.gz

Restore from Backup

# Restore from SQL file
psql -U postgres -d staque_io < backup_20240101.sql

# Restore from compressed backup
gunzip -c backup_20240101.sql.gz | psql -U postgres -d staque_io

💡 Best Practices

Enable connection pooling for better performance
Use prepared statements to prevent SQL injection
Regularly backup the database
Monitor query performance and add indexes as needed
Archive old conversation threads to maintain performance
Set up automated backup schedules