Database Schema - Entity Relationship Diagram

This document provides a comprehensive overview of the Quantbot database schema, including all tables, relationships, primary keys, and foreign keys.

Overview

The Quantbot database consists of 10 tables organized into three main domains:

User Management - Authentication and user data
Portfolio Management - Investment portfolios and holdings
ADK (Agent Development Kit) - Chat sessions and conversation management

Entity Relationship Diagram

erDiagram
    %% User Management Domain
    user {
        int id PK
        varchar email UK
        varchar full_name
        boolean is_active
        boolean is_superuser
        varchar hashed_password
        timestamp created_at
        timestamp updated_at
    }

    refreshtoken {
        int id PK
        varchar token UK
        int user_id FK
        timestamp expires_at
        boolean is_revoked
        timestamp created_at
    }

    userapikey {
        int id PK
        int user_id FK
        apikeytype key_type
        varchar encrypted_key
        varchar key_name
        boolean is_active
        timestamp created_at
        timestamp updated_at
        timestamp last_used_at
    }

    %% Portfolio Management Domain
    portfolios {
        int id PK
        varchar name
        int user_id FK
        timestamp created_at
        timestamp updated_at
    }

    holdings {
        int id PK
        varchar symbol
        double shares
        double average_price
        int portfolio_id FK
        timestamp created_at
        timestamp updated_at
    }

    %% ADK (Agent Development Kit) Domain
    sessions {
        varchar app_name PK
        varchar user_id PK
        varchar id PK
        jsonb state
        timestamp create_time
        timestamp update_time
    }

    events {
        varchar id PK
        varchar app_name PK
        varchar user_id PK
        varchar session_id PK
        varchar invocation_id
        varchar author
        varchar branch
        timestamp timestamp
        jsonb content
        bytea actions
        text long_running_tool_ids_json
        jsonb grounding_metadata
        boolean partial
        boolean turn_complete
        varchar error_code
        varchar error_message
        boolean interrupted
    }

    app_states {
        varchar app_name PK
        jsonb state
        timestamp update_time
    }

    user_states {
        varchar app_name PK
        varchar user_id PK
        jsonb state
        timestamp update_time
    }

    %% Migration Management
    alembic_version {
        varchar version_num PK
    }

    %% Relationships
    user ||--o{ refreshtoken : "has many"
    user ||--o{ userapikey : "has many"
    user ||--o{ portfolios : "has many"
    portfolios ||--o{ holdings : "contains"
    sessions ||--o{ events : "generates"

Table Details

User Management Domain

1. `user` Table

Purpose: Core user authentication and profile data

Column	Type	Constraints	Description
`id`	integer	PRIMARY KEY, AUTO_INCREMENT	Unique user identifier
`email`	varchar	NOT NULL, UNIQUE	User's email address
`full_name`	varchar(100)	NOT NULL	User's full name
`is_active`	boolean	NOT NULL	Account active status
`is_superuser`	boolean	NOT NULL	Admin privileges flag
`hashed_password`	varchar	NOT NULL	Bcrypt hashed password
`created_at`	timestamp	NOT NULL	Account creation time
`updated_at`	timestamp	NULL	Last update time

Indexes: - user_pkey (PRIMARY KEY on id) - ix_user_email (UNIQUE on email)

Referenced by: refreshtoken, userapikey, portfolios

2. `refreshtoken` Table

Purpose: JWT refresh token management

Column	Type	Constraints	Description
`id`	integer	PRIMARY KEY, AUTO_INCREMENT	Token record ID
`token`	varchar	NOT NULL, UNIQUE	Refresh token value
`user_id`	integer	NOT NULL, FK → user(id)	Associated user
`expires_at`	timestamp	NOT NULL	Token expiration
`is_revoked`	boolean	NOT NULL	Revocation status
`created_at`	timestamp	NOT NULL	Token creation time

Foreign Keys: - refreshtoken_user_id_fkey: user_id → user(id) ON DELETE CASCADE

3. `userapikey` Table

Purpose: User-specific API key storage with encryption

Column	Type	Constraints	Description
`id`	integer	PRIMARY KEY, AUTO_INCREMENT	API key record ID
`user_id`	integer	NOT NULL, FK → user(id)	Associated user
`key_type`	apikeytype	NOT NULL	API key type enum
`encrypted_key`	varchar	NOT NULL	Encrypted API key
`key_name`	varchar	NULL	User-friendly name
`is_active`	boolean	NOT NULL	Active status
`created_at`	timestamp	NOT NULL	Creation time
`updated_at`	timestamp	NULL	Last update time
`last_used_at`	timestamp	NULL	Last usage time

Foreign Keys: - userapikey_user_id_fkey: user_id → user(id) ON DELETE CASCADE

Enums: - apikeytype: google_api, news_api

Portfolio Management Domain

4. `portfolios` Table

Purpose: User investment portfolios

Column	Type	Constraints	Description
`id`	integer	PRIMARY KEY, AUTO_INCREMENT	Portfolio ID
`name`	varchar	NOT NULL	Portfolio name
`user_id`	integer	NOT NULL, FK → user(id)	Portfolio owner
`created_at`	timestamp	NOT NULL	Creation time
`updated_at`	timestamp	NULL	Last update time

Foreign Keys: - portfolios_user_id_fkey: user_id → user(id) ON DELETE CASCADE

5. `holdings` Table

Purpose: Individual stock holdings within portfolios

Column	Type	Constraints	Description
`id`	integer	PRIMARY KEY, AUTO_INCREMENT	Holding ID
`symbol`	varchar	NOT NULL	Stock symbol (e.g., AAPL)
`shares`	double precision	NOT NULL	Number of shares
`average_price`	double precision	NOT NULL	Average purchase price
`portfolio_id`	integer	NOT NULL, FK → portfolios(id)	Parent portfolio
`created_at`	timestamp	NOT NULL	Creation time
`updated_at`	timestamp	NULL	Last update time

Foreign Keys: - holdings_portfolio_id_fkey: portfolio_id → portfolios(id) ON DELETE CASCADE

ADK (Agent Development Kit) Domain

6. `sessions` Table

Purpose: Chat session management for ADK agents

Column	Type	Constraints	Description
`app_name`	varchar(128)	PRIMARY KEY (composite)	Application identifier
`user_id`	varchar(128)	PRIMARY KEY (composite)	User identifier
`id`	varchar(128)	PRIMARY KEY (composite)	Session identifier
`state`	jsonb	NOT NULL	Session state data
`create_time`	timestamp	NOT NULL	Session creation time
`update_time`	timestamp	NOT NULL	Last update time

Primary Key: Composite key on (app_name, user_id, id)

7. `events` Table

Purpose: Chat conversation events and messages

Column	Type	Constraints	Description
`id`	varchar(128)	PRIMARY KEY (composite)	Event ID
`app_name`	varchar(128)	PRIMARY KEY (composite)	Application identifier
`user_id`	varchar(128)	PRIMARY KEY (composite)	User identifier
`session_id`	varchar(128)	PRIMARY KEY (composite)	Session identifier
`invocation_id`	varchar(256)	NOT NULL	Invocation identifier
`author`	varchar(256)	NOT NULL	Message author
`branch`	varchar(256)	NULL	Conversation branch
`timestamp`	timestamp	NOT NULL	Event timestamp
`content`	jsonb	NULL	Message content
`actions`	bytea	NOT NULL	Serialized actions
`long_running_tool_ids_json`	text	NULL	Tool execution IDs
`grounding_metadata`	jsonb	NULL	Grounding information
`partial`	boolean	NULL	Partial response flag
`turn_complete`	boolean	NULL	Turn completion flag
`error_code`	varchar(256)	NULL	Error code if any
`error_message`	varchar(1024)	NULL	Error message
`interrupted`	boolean	NULL	Interruption flag

Primary Key: Composite key on (id, app_name, user_id, session_id)

Foreign Keys: - events_app_name_user_id_session_id_fkey: (app_name, user_id, session_id) → sessions(app_name, user_id, id) ON DELETE CASCADE

8. `app_states` Table

Purpose: Application-level state storage

Column	Type	Constraints	Description
`app_name`	varchar(128)	PRIMARY KEY	Application identifier
`state`	jsonb	NOT NULL	Application state data
`update_time`	timestamp	NOT NULL	Last update time

9. `user_states` Table

Purpose: User-specific application state

Column	Type	Constraints	Description
`app_name`	varchar(128)	PRIMARY KEY (composite)	Application identifier
`user_id`	varchar(128)	PRIMARY KEY (composite)	User identifier
`state`	jsonb	NOT NULL	User state data
`update_time`	timestamp	NOT NULL	Last update time

Primary Key: Composite key on (app_name, user_id)

Migration Management

10. `alembic_version` Table

Purpose: Database migration version tracking

Column	Type	Constraints	Description
`version_num`	varchar(32)	PRIMARY KEY	Current migration version

Relationship Summary

One-to-Many Relationships

user → refreshtoken (1:N)
One user can have multiple refresh tokens
CASCADE DELETE: Deleting user removes all tokens
user → userapikey (1:N)
One user can have multiple API keys
CASCADE DELETE: Deleting user removes all API keys
user → portfolios (1:N)
One user can have multiple portfolios
CASCADE DELETE: Deleting user removes all portfolios
portfolios → holdings (1:N)
One portfolio can contain multiple stock holdings
CASCADE DELETE: Deleting portfolio removes all holdings
sessions → events (1:N)
One chat session can generate multiple events/messages
CASCADE DELETE: Deleting session removes all events

Key Design Patterns

Security Features

Password Security: Bcrypt hashed passwords
API Key Encryption: User API keys stored encrypted
Token Management: Refresh tokens with expiration and revocation
Cascade Deletes: Proper cleanup when users are deleted

ADK Integration

Composite Keys: ADK tables use composite primary keys for multi-tenancy
JSONB Storage: Flexible state and content storage using PostgreSQL JSONB
Event Sourcing: Complete conversation history in events table

Data Integrity

Foreign Key Constraints: Proper referential integrity
Unique Constraints: Email uniqueness, token uniqueness
NOT NULL Constraints: Required fields properly enforced
Cascade Deletes: Automatic cleanup of related records

Usage Notes

For Developers

The user.id is an integer auto-increment, while ADK tables use string IDs
ADK user_id fields are strings and separate from the main user table
JSONB fields allow flexible schema evolution for state and content
All timestamps are stored without timezone (application handles timezone conversion)

For Database Administrators

Regular cleanup of expired refresh tokens recommended
Monitor JSONB field sizes in ADK tables for performance
Consider partitioning events table if conversation volume grows large
Index optimization may be needed on JSONB fields based on query patterns

Last updated: July 2025
Database Schema Version: Current as of latest migration