Fusion
83f94b1f09
Add complete documentation structure: - Frontend architecture documentation - Database schema documentation - Deployment guides (Docker, Traefik, Authentik, Environment) - Development guides (Setup, Testing, Contributing, Quality Gates) - Deployment architecture documentation - Updated docs README with complete navigation All new features and APIs are now documented. Quality gates: docs only, no code changes
232 lines
10 KiB
Markdown
232 lines
10 KiB
Markdown
# 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
|
|
```python
|
|
# 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
|
|
```bash
|
|
# 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
|