headquarter/docs/architecture/database.md

Database Schema

Overview

Headquarter uses PostgreSQL with SQLAlchemy ORM and Alembic for migrations. All tables use UUID primary keys and include created_at/updated_at timestamps.

Entity Relationship Diagram

┌──────────────┐       ┌─────────────────┐       ┌──────────────┐
│    users     │       │  git_repository │       │   project    │
├──────────────┤       ├─────────────────┤       ├──────────────┤
│ id (PK)      │       │ id (PK)         │       │ id (PK)      │
│ email        │       │ project_id (FK) │──┐    │ name         │
│ name         │       │ name            │  │    │ description  │
│ authentik_id │       │ remote_url      │  │    │ created_by_id│──┐
│ avatar_url   │       │ local_path      │  │    │              │  │
│ created_at   │       │ is_mirror       │  │    │              │  │
│ updated_at   │       │ created_by_id   │──┤    │              │  │
└──────────────┘       │ created_at      │  │    └──────────────┘  │
        │              │ updated_at      │  │         ▲            │
        │              └─────────────────┘  │         │            │
        │                      │             │         │            │
        │              ┌───────┘             │         │            │
        │              │                     │         │            │
┌───────▼──────┐      ┌▼────────────────┐   │         │            │
│  ssh_keys    │      │   user_config   │   │         │            │
├──────────────┤      ├─────────────────┤   │         │            │
│ id (PK)      │      │ user_id (FK)    │───┘         │            │
│ user_id (FK) │      │ theme           │             │            │
│ name         │      │ git_name        │             │            │
│ public_key   │      │ git_email       │             │            │
│ private_key  │      │ default_editor  │             │            │
│ created_at   │      │ created_at      │             │            │
│ updated_at   │      │ updated_at      │             │            │
└──────────────┘      └─────────────────┘             │            │
                                                      │            │
                              ┌───────────────────────┘            │
                              │                                    │
                              ▼                                    │
                       ┌─────────────────┐                         │
                       │   tool_types    │                         │
                       ├─────────────────┤                         │
                       │ id (PK)         │                         │
                       │ name            │                         │
                       │ display_name    │                         │
                       │ description     │                         │
                       │ compose_template│                         │
                       │ required_vars   │                         │
                       │ is_builtin      │                         │
                       │ created_by_id   │─────────────────────────┘
                       │ created_at      │
                       │ updated_at      │
                       └─────────────────┘

Table Definitions

users

Stores user accounts synchronized from Authentik.

Column	Type	Constraints	Description
id	UUID	PK	Unique identifier
email	VARCHAR(255)	NOT NULL, UNIQUE	User email
name	VARCHAR(255)		Display name
authentik_id	VARCHAR(255)	UNIQUE	Authentik user ID
avatar_url	VARCHAR(500)		Profile avatar URL
created_at	TIMESTAMP	DEFAULT now()	Creation timestamp
updated_at	TIMESTAMP	DEFAULT now()	Last update timestamp

Relationships:

• One-to-Many: users → git_repository (created_by_id)
• One-to-Many: users → project (created_by_id)
• One-to-Many: users → ssh_keys (user_id)
• One-to-One: users → user_config (user_id)
• One-to-Many: users → tool_types (created_by_id)

project

Organizes repositories into logical groups.

Column	Type	Constraints	Description
id	UUID	PK	Unique identifier
name	VARCHAR(255)	NOT NULL	Project name
description	TEXT		Project description
created_by_id	UUID	FK → users.id	Creator
created_at	TIMESTAMP	DEFAULT now()	Creation timestamp
updated_at	TIMESTAMP	DEFAULT now()	Last update timestamp

Relationships:

• One-to-Many: project → git_repository (project_id)

git_repository

Git repositories (bare/mirror clones).

Column	Type	Constraints	Description
id	UUID	PK	Unique identifier
project_id	UUID	FK → project.id, NOT NULL	Parent project
name	VARCHAR(255)	NOT NULL	Repository name
remote_url	VARCHAR(500)	NOT NULL	Remote git URL
local_path	VARCHAR(500)		Local filesystem path
is_mirror	BOOLEAN	DEFAULT false	Is mirror clone
created_by_id	UUID	FK → users.id	Creator
created_at	TIMESTAMP	DEFAULT now()	Creation timestamp
updated_at	TIMESTAMP	DEFAULT now()	Last update timestamp

Indexes:

• idx_repo_project: (project_id)
• idx_repo_name: (project_id, name) - UNIQUE

ssh_keys

User SSH keys for git authentication.

Column	Type	Constraints	Description
id	UUID	PK	Unique identifier
user_id	UUID	FK → users.id, NOT NULL	Owner
name	VARCHAR(255)	NOT NULL	Key name
public_key	TEXT	NOT NULL	Public key
private_key	TEXT	NOT NULL, ENCRYPTED	Encrypted private key
created_at	TIMESTAMP	DEFAULT now()	Creation timestamp
updated_at	TIMESTAMP	DEFAULT now()	Last update timestamp

Indexes:

• idx_ssh_user: (user_id)

user_config

User preferences and settings.

Column	Type	Constraints	Description
id	UUID	PK	Unique identifier
user_id	UUID	FK → users.id, NOT NULL, UNIQUE	Owner
theme	VARCHAR(50)	DEFAULT 'system'	UI theme (system/light/dark)
git_name	VARCHAR(255)		Git user name
git_email	VARCHAR(255)		Git user email
default_editor	VARCHAR(50)	DEFAULT 'vscode'	Preferred editor
created_at	TIMESTAMP	DEFAULT now()	Creation timestamp
updated_at	TIMESTAMP	DEFAULT now()	Last update timestamp

tool_types

Types of development tools that can be spawned.

Column	Type	Constraints	Description
id	UUID	PK	Unique identifier
name	VARCHAR(255)	NOT NULL, UNIQUE	Machine name
display_name	VARCHAR(255)	NOT NULL	Human-readable name
description	TEXT		Description
compose_template	TEXT	NOT NULL	Docker Compose template
required_variables	JSONB	NOT NULL	Template variables
is_builtin	BOOLEAN	DEFAULT false	Built-in type
created_by_id	UUID	FK → users.id	Creator (null for built-in)
created_at	TIMESTAMP	DEFAULT now()	Creation timestamp
updated_at	TIMESTAMP	DEFAULT now()	Last update timestamp

Indexes:

• idx_tool_builtin: (is_builtin)

Migration History

Version	Date	Description
0001_initial_schema	2024-01-XX	Initial tables: users, projects, git_repositories
0002_refresh_tokens	2024-01-XX	Added refresh_tokens table
0003_user_configs	2024-05-18	Added user_config table
0004_tool_types	2024-05-18	Added tool_types table

Data Types

PostgreSQL Types

• UUID: uuid - All primary keys
• Timestamps: TIMESTAMP WITH TIME ZONE
• JSONB: JSONB - For flexible config (user_config, tool_types)
• Strings: VARCHAR(n) - With appropriate length limits
• Text: TEXT - For unbounded content
• Boolean: BOOLEAN - True/false flags

SQLAlchemy Configuration

# Base model features
class Base:
    id: UUID = Column(UUID(as_uuid=True), primary_key=True, default=uuid.uuid4)
    created_at: datetime = Column(DateTime(timezone=True), server_default=func.now())
    updated_at: datetime = Column(DateTime(timezone=True), onupdate=func.now())

Backup Strategy

Automated Backups

• Frequency: Daily at 2 AM
• Retention: 7 daily, 4 weekly, 12 monthly
• Method: pg_dump to S3/object storage
• Encryption: AES-256 encrypted backups

Manual Backup

# Full backup
pg_dump -Fc -f headquarter_backup.dump postgresql://user:pass@host/db

# Restore
pg_restore -d postgresql://user:pass@host/db headquarter_backup.dump

Performance

Query Optimization

• All foreign keys indexed
• Frequently queried columns indexed
• Composite indexes for multi-column queries

Connection Pooling

• SQLAlchemy async pool: 5-20 connections
• PgBouncer for production: transaction mode

Future Schema Changes

Planned additions:

• teams table - Group users into teams
• team_memberships table - Link users to teams
• tool_instances table - Running tool containers
• audit_logs table - Track important actions
• notifications table - User notifications