Database Architecture

Overview

cpm uses SQLite3 as its embedded database for storing repository metadata, user information, organization structures, SSH keys, and server configurations. This document provides comprehensive coverage of the database schema, relationships, indexes, and data access patterns.

Database Technology

Engine: SQLite3 Location: ~/.cpm/cpm.db (configurable) Driver: github.com/mattn/go-sqlite3 Version: 3.x

Why SQLite?

1. Zero Configuration: No separate database server required
2. Embedded: Runs in-process with cpm
3. Portable: Single file database, easy backup
4. Reliable: ACID compliant, battle-tested
5. Lightweight: Minimal memory and disk footprint
6. Fast: Excellent performance for local operations

Database Schema

Entity-Relationship Diagram

┌──────────────┐          ┌──────────────────┐          ┌──────────────┐
│ organizations│◄────────┤ org_members      │─────────►│    users     │
│              │          │                  │          │              │
│ - id         │          │ - org_id  (FK)   │          │ - id         │
│ - name       │          │ - user_id (FK)   │          │ - username   │
│ - description│          │ - role           │          │ - email      │
│ - created_at │          │                  │          │ - public_key │
└──────┬───────┘          └──────────────────┘          └──────┬───────┘
       │                                                        │
       │                                                        │
       │                  ┌──────────────────┐                │
       └─────────────────►│ repositories     │◄───────────────┘
                          │                  │
                          │ - id             │       (via repo_access)
                          │ - name           │
                          │ - org_id  (FK)   │
                          │ - path           │
                          │ - description    │
                          │ - created_at     │
                          └────────┬─────────┘
                                   │
                                   │
                          ┌────────┴─────────┐
                          │                  │
                    ┌─────▼──────┐    ┌─────▼──────┐
                    │ org_repos  │    │repo_access │
                    │            │    │            │
                    │- org_id    │    │- repo_id   │
                    │- repo_id   │    │- user_id   │
                    │            │    │- access_level
                    └────────────┘    └────────────┘


┌──────────────┐          ┌──────────────────┐
│  ssh_keys    │          │     servers      │
│              │          │                  │
│ - id         │◄─────────│ - id             │
│ - name       │   FK     │ - name           │
│ - public_key │          │ - host           │
│ - private_   │          │ - port           │
│   key_path   │          │ - is_neighbor    │
│ - created_at │          │ - ssh_key_id     │
└──────────────┘          │ - created_at     │
                          └──────────────────┘

Table Definitions

users

Stores user account information and SSH public keys.

CREATE TABLE IF NOT EXISTS users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    username TEXT NOT NULL UNIQUE,
    email TEXT NOT NULL,
    public_key TEXT,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

Columns:

• id: Unique user identifier
• username: Unique username (alphanumeric, hyphens, underscores)
• email: User email address
• public_key: SSH public key for authentication
• created_at: Account creation timestamp

Indexes:

• Primary key on id
• Unique constraint on username

Relationships:

• One-to-many with org_members
• One-to-many with repo_access

organizations

Stores organization definitions for grouping repositories.

CREATE TABLE IF NOT EXISTS organizations (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL UNIQUE,
    description TEXT,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

Columns:

• id: Unique organization identifier
• name: Organization name (unique)
• description: Optional organization description
• created_at: Creation timestamp

Indexes:

• Primary key on id
• Unique constraint on name

Relationships:

• One-to-many with repositories
• One-to-many with org_members

repositories

Stores repository metadata and file system paths.

CREATE TABLE IF NOT EXISTS repositories (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    org_id INTEGER NOT NULL,
    path TEXT NOT NULL,
    description TEXT,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (org_id) REFERENCES organizations(id) ON DELETE CASCADE,
    UNIQUE(org_id, name)
);

Columns:

• id: Unique repository identifier
• name: Repository name (unique within organization)
• org_id: Organization ID (foreign key)
• path: Absolute file system path to bare repository
• description: Optional repository description
• created_at: Creation timestamp

Indexes:

• Primary key on id
• Index on org_id
• Unique constraint on (org_id, name)

Relationships:

• Many-to-one with organizations
• One-to-many with repo_access

ssh_keys

Stores SSH key pair information for authentication.

CREATE TABLE IF NOT EXISTS ssh_keys (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL UNIQUE,
    public_key TEXT NOT NULL,
    private_key_path TEXT NOT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

Columns:

• id: Unique key identifier
• name: Key name for reference
• public_key: SSH public key content
• private_key_path: Path to private key file
• created_at: Generation timestamp

Indexes:

• Primary key on id
• Unique constraint on name

Relationships:

• One-to-many with servers

servers

Stores remote server configurations.

CREATE TABLE IF NOT EXISTS servers (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL UNIQUE,
    host TEXT NOT NULL,
    port INTEGER NOT NULL DEFAULT 22,
    is_neighbor BOOLEAN NOT NULL DEFAULT 0,
    ssh_key_id INTEGER,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (ssh_key_id) REFERENCES ssh_keys(id) ON DELETE SET NULL
);

Columns:

• id: Unique server identifier
• name: Server name for reference
• host: Hostname or IP address
• port: SSH port number
• is_neighbor: Boolean flag for peer servers
• ssh_key_id: Associated SSH key (foreign key)
• created_at: Registration timestamp

Indexes:

• Primary key on id
• Unique constraint on name
• Index on is_neighbor

Relationships:

• Many-to-one with ssh_keys

repo_access

Junction table for user-repository access permissions.

CREATE TABLE IF NOT EXISTS repo_access (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    repo_id INTEGER NOT NULL,
    user_id INTEGER NOT NULL,
    access_level TEXT NOT NULL CHECK(access_level IN ('read', 'write', 'admin')),
    FOREIGN KEY (repo_id) REFERENCES repositories(id) ON DELETE CASCADE,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    UNIQUE(repo_id, user_id)
);

Columns:

• id: Unique access record identifier
• repo_id: Repository ID (foreign key)
• user_id: User ID (foreign key)
• access_level: Permission level (read, write, admin)

Indexes:

• Primary key on id
• Index on repo_id
• Index on user_id
• Unique constraint on (repo_id, user_id)

Access Levels:

• read: Read-only access
• write: Read and write access
• admin: Full administrative access

Relationships:

• Many-to-one with repositories
• Many-to-one with users

org_members

Junction table for organization membership with roles. (Implicit from schema analysis)

CREATE TABLE IF NOT EXISTS org_members (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    org_id INTEGER NOT NULL,
    user_id INTEGER NOT NULL,
    role TEXT NOT NULL CHECK(role IN ('owner', 'admin', 'member')),
    joined_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (org_id) REFERENCES organizations(id) ON DELETE CASCADE,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    UNIQUE(org_id, user_id)
);

Roles:

• owner: Full control including organization deletion
• admin: Manage members and repositories
• member: Read access to organization repositories

Data Access Layer

Database Connection

Location: internal/db/db.go

type DB struct {
    conn *sql.DB
}

func Open(dbPath string) (*DB, error) {
    conn, err := sql.Open("sqlite3", dbPath)
    if err != nil {
        return nil, err
    }

    // Initialize schema
    if err := initSchema(conn); err != nil {
        return nil, err
    }

    return &DB{conn: conn}, nil
}

CRUD Operations

Location: internal/db/crud.go

Repository Operations

// Create repository
func (db *DB) CreateRepository(name, path string, orgID int64) (int64, error)

// Get repository by ID
func (db *DB) GetRepository(id int64) (*Repository, error)

// Get repository by name and org
func (db *DB) GetRepositoryByName(name string, orgID int64) (*Repository, error)

// List all repositories
func (db *DB) ListRepositories() ([]Repository, error)

// List repositories by organization
func (db *DB) ListRepositoriesByOrg(orgID int64) ([]Repository, error)

// Update repository
func (db *DB) UpdateRepository(id int64, updates map[string]interface{}) error

// Delete repository
func (db *DB) DeleteRepository(id int64) error

User Operations

// Create user
func (db *DB) CreateUser(username, email, publicKey string) (int64, error)

// Get user by ID
func (db *DB) GetUser(id int64) (*User, error)

// Get user by username
func (db *DB) GetUserByUsername(username string) (*User, error)

// List all users
func (db *DB) ListUsers() ([]User, error)

// Update user
func (db *DB) UpdateUser(id int64, updates map[string]interface{}) error

// Delete user
func (db *DB) DeleteUser(id int64) error

Organization Operations

// Create organization
func (db *DB) CreateOrganization(name, description string) (int64, error)

// Get organization
func (db *DB) GetOrganization(id int64) (*Organization, error)

// List organizations
func (db *DB) ListOrganizations() ([]Organization, error)

// Add member to organization
func (db *DB) AddOrgMember(orgID, userID int64, role string) error

// Remove member from organization
func (db *DB) RemoveOrgMember(orgID, userID int64) error

// Delete organization
func (db *DB) DeleteOrganization(id int64) error

Data Models

Location: internal/db/models.go

type User struct {
    ID        int64     `json:"id"`
    Username  string    `json:"username"`
    Email     string    `json:"email"`
    PublicKey string    `json:"public_key,omitempty"`
    CreatedAt time.Time `json:"created_at"`
}

type Organization struct {
    ID          int64     `json:"id"`
    Name        string    `json:"name"`
    Description string    `json:"description"`
    CreatedAt   time.Time `json:"created_at"`
}

type Repository struct {
    ID          int64     `json:"id"`
    Name        string    `json:"name"`
    OrgID       int64     `json:"org_id"`
    Path        string    `json:"path"`
    Description string    `json:"description"`
    CreatedAt   time.Time `json:"created_at"`
}

type SSHKey struct {
    ID             int64     `json:"id"`
    Name           string    `json:"name"`
    PublicKey      string    `json:"public_key"`
    PrivateKeyPath string    `json:"private_key_path"`
    CreatedAt      time.Time `json:"created_at"`
}

type Server struct {
    ID         int64     `json:"id"`
    Name       string    `json:"name"`
    Host       string    `json:"host"`
    Port       int       `json:"port"`
    IsNeighbor bool      `json:"is_neighbor"`
    SSHKeyID   *int64    `json:"ssh_key_id,omitempty"`
    CreatedAt  time.Time `json:"created_at"`
}

Query Patterns

Common Queries

Get repositories for user

func (db *DB) GetUserRepositories(userID int64) ([]Repository, error) {
    query := `
        SELECT DISTINCT r.*
        FROM repositories r
        LEFT JOIN org_members om ON r.org_id = om.org_id
        LEFT JOIN repo_access ra ON r.id = ra.repo_id
        WHERE om.user_id = ? OR ra.user_id = ?
    `
    // Execute query...
}

Get organization members with roles

func (db *DB) GetOrgMembers(orgID int64) ([]OrgMember, error) {
    query := `
        SELECT u.id, u.username, u.email, om.role, om.joined_at
        FROM users u
        JOIN org_members om ON u.id = om.user_id
        WHERE om.org_id = ?
        ORDER BY om.role, u.username
    `
    // Execute query...
}

Database Migrations

Schema Initialization

On first run, cpm creates all tables with proper indexes:

func initSchema(db *sql.DB) error {
    _, err := db.Exec(schema)
    return err
}

Future Migrations

Migration strategy for schema updates:

type Migration struct {
    Version int
    SQL     string
}

var migrations = []Migration{
    {1, "CREATE TABLE ..."},
    {2, "ALTER TABLE ..."},
    // Future migrations
}

Performance Optimization

Indexes

Strategic indexes for common query patterns:

-- Repository organization lookup
CREATE INDEX IF NOT EXISTS idx_repositories_org_id
    ON repositories(org_id);

-- Repository access user lookup
CREATE INDEX IF NOT EXISTS idx_repo_access_repo_id
    ON repo_access(repo_id);

-- Repository access repository lookup
CREATE INDEX IF NOT EXISTS idx_repo_access_user_id
    ON repo_access(user_id);

-- Neighbor filtering
CREATE INDEX IF NOT EXISTS idx_servers_is_neighbor
    ON servers(is_neighbor);

Query Optimization

1. Use prepared statements
2. Batch inserts where possible
3. Appropriate index usage
4. Limit result sets
5. Use transactions for related operations

Data Integrity

Foreign Key Constraints

All foreign key relationships enforce referential integrity:

FOREIGN KEY (org_id) REFERENCES organizations(id) ON DELETE CASCADE
FOREIGN KEY (ssh_key_id) REFERENCES ssh_keys(id) ON DELETE SET NULL

Cascade Behavior

• ON DELETE CASCADE: Deleting organization removes repositories
• ON DELETE SET NULL: Deleting SSH key nullifies server reference

Unique Constraints

• Usernames must be unique
• Organization names must be unique
• Repository names unique within organization
• SSH key names must be unique

Backup and Recovery

Backup Strategy

# Simple file copy (when cpm not running)
cp ~/.cpm/cpm.db ~/.cpm/backups/cpm-$(date +%Y%m%d).db

# Online backup using SQLite backup API
sqlite3 ~/.cpm/cpm.db ".backup ~/.cpm/backups/cpm-$(date +%Y%m%d).db"

# Using cpm (future feature)
cpm db backup --output ~/backups/

Recovery

# Restore from backup
cp ~/.cpm/backups/cpm-20240115.db ~/.cpm/cpm.db

# Verify integrity
sqlite3 ~/.cpm/cpm.db "PRAGMA integrity_check;"

See Also

• Architecture Overview
• API Reference
• Configuration Guide