db

Overview ¶

Package db provides self-healing functions for database integrity.

This file implements automatic repair of catalog_template_id references in installed_applications table.

Package db provides PostgreSQL database access and management for StreamSpace.

This file implements installed application management and access control.

Purpose: - CRUD operations for installed applications - Application configuration management - Group-based access control for applications - Application enable/disable functionality

Features: - Install applications from catalog templates - Custom display names for user dashboard - Configuration storage in JSONB - Group access permissions - Enable/disable applications

Database Schema:

• installed_applications table: Installed application instances

• id (varchar): Primary key (UUID)

• catalog_template_id (int): Foreign key to catalog_templates

• name (varchar): Internal name with GUID suffix

• display_name (varchar): Custom display name for dashboard

• folder_path (varchar): Path to configuration folder

• enabled (boolean): Whether application is active

• configuration (jsonb): Application-specific settings

• created_by (varchar): User who installed the application

• created_at, updated_at: Timestamps

• application_group_access table: Group permissions for applications

• id (varchar): Primary key (UUID)

• application_id (varchar): Foreign key to installed_applications

• group_id (varchar): Foreign key to groups

• access_level (varchar): Permission level (view, launch, admin)

• created_at: When access was granted

Thread Safety: - All database operations are thread-safe via database/sql pool

Example Usage:

appDB := db.NewApplicationDB(database.DB())

// Install application
app, err := appDB.InstallApplication(ctx, &models.InstallApplicationRequest{
    CatalogTemplateID: 1,
    DisplayName:       "Firefox Browser",
})

// Grant group access
err := appDB.AddGroupAccess(ctx, appID, groupID, "launch")

// Enable/disable application
err := appDB.SetApplicationEnabled(ctx, appID, true)

Package db provides PostgreSQL database access and management for StreamSpace.

This file implements the core database connection and lifecycle management.

Purpose: - Establish and maintain PostgreSQL connection pool - Initialize database schema on startup (82+ tables) - Provide centralized database instance for all API handlers - Execute raw SQL queries and transactions - Validate database configuration for security

Features: - Connection pooling with configurable limits (25 max open, 5 max idle) - Comprehensive schema migrations (82+ tables, 200+ indexes) - Health check and ping capabilities - Graceful connection cleanup on shutdown - Configuration validation (prevents SQL injection in connection strings) - SSL/TLS warnings for production security

Database Schema:

• 82+ tables covering users, sessions, templates, plugins, quotas, audit logs
• 200+ indexes for query performance optimization
• JSONB columns for flexible metadata storage
• Foreign key constraints for referential integrity
• Composite indexes for common query patterns

Implementation Details: - Uses database/sql with lib/pq PostgreSQL driver - Connection pool configured for optimal performance (5min max lifetime) - Schema initialization runs CREATE TABLE IF NOT EXISTS on startup - Thread-safe connection pooling handled by database/sql package - Validates hostname, port, username, database name, SSL mode

Thread Safety: - Database connections are thread-safe and managed by database/sql pool - Safe for concurrent use across multiple goroutines - Connection pool prevents resource exhaustion

Dependencies: - PostgreSQL 12+ (required) - lib/pq driver for database/sql

Example Usage:

config := db.Config{
    Host:     "localhost",
    Port:     "5432",
    User:     "streamspace",
    Password: "secretpassword",
    DBName:   "streamspace",
    SSLMode:  "require",
}

database, err := db.NewDatabase(config)
if err != nil {
    log.Fatal(err)
}
defer database.Close()

// Run migrations
if err := database.Migrate(); err != nil {
    log.Fatal(err)
}

// Use database connection
rows, err := database.DB().Query("SELECT * FROM users")

Package db provides PostgreSQL database access and management for StreamSpace.

This file implements group management and authorization data access.

Purpose: - CRUD operations for user groups - Group-based access control and permissions - Group quota and resource limit management - User-to-group membership mapping - Group hierarchy support (parent/child groups)

Features: - Group-based resource quotas - Group permissions and role-based access - User membership tracking with roles - Group search and filtering by type/parent - Quota inheritance from groups to users - Member count aggregation

Database Schema:

• groups table: Group definitions and metadata

• id (varchar): Primary key (UUID)

• name (varchar): Unique group name

• display_name (varchar): Human-readable name

• description (text): Group purpose description

• type (varchar): Group type (team, department, etc.)

• parent_id (varchar): Optional parent group for hierarchy

• created_at, updated_at: Timestamps

• group_memberships table: User-to-group junction

• id (varchar): Primary key

• user_id (varchar): Foreign key to users

• group_id (varchar): Foreign key to groups

• role (varchar): Member role (owner, admin, member)

• created_at: When user joined

• group_quotas table: Resource limits per group

• group_id: Foreign key to groups

• max_sessions, max_cpu, max_memory, max_storage: Limits

• used_sessions, used_cpu, used_memory, used_storage: Current usage

Quota Hierarchy:

1. User-specific quotas (most restrictive wins)
2. Group quotas (applied to all group members)
3. Platform defaults (fallback)

Implementation Details: - Groups can have resource quotas that apply to all members - Most restrictive quota wins (user vs group vs platform) - Quota stored as separate table with foreign key constraint - Supports hierarchical groups with parent_id - Member counts calculated via JOIN for efficiency

Thread Safety: - All database operations are thread-safe via database/sql pool - Safe for concurrent access from multiple goroutines

Dependencies: - github.com/google/uuid for ID generation - models package for data structures

Example Usage:

groupDB := db.NewGroupDB(database.DB())

// Create group
group, err := groupDB.CreateGroup(ctx, &models.CreateGroupRequest{
    Name:        "developers",
    DisplayName: "Developers",
    Description: "Development team",
    Type:        "team",
})

// Set group quota
maxSessions := 20
err := groupDB.SetGroupQuota(ctx, groupID, &models.SetQuotaRequest{
    MaxSessions: &maxSessions,
})

// Add user to group
err := groupDB.AddGroupMember(ctx, groupID, &models.AddGroupMemberRequest{
    UserID: userID,
    Role:   "member",
})

Package db provides PostgreSQL database access for StreamSpace.

This file implements session management operations for multi-platform support. Sessions are the source of truth in the database, updated by controller status events.

Package db provides PostgreSQL database access and management for StreamSpace.

This file defines team-related data structures for team management and collaboration.

Purpose: - Define team membership data structures - Define team role permission structures - Support team-based organization and collaboration

Features: - Team membership tracking with roles - Role-based permission system - Team hierarchy support

Data Structures:

• TeamMembership: Represents a user's membership in a team
• TeamPermission: Defines permissions for team roles
• TeamRoleInfo: Aggregates role information with permissions

Team Roles:

• owner: Full control (manage team, members, sessions, quotas)
• admin: Administrative access (manage members, sessions)
• member: Standard access (create sessions, view team data)
• viewer: Read-only access (view sessions and quotas)

Implementation Details: - This file contains only type definitions - Actual database operations are in groups.go (teams are a type of group) - Team roles and permissions stored in team_role_permissions table - Initialized with default permissions during database migration

Example Usage:

membership := &TeamMembership{
    TeamID:          "team-abc",
    TeamName:        "frontend-team",
    TeamDisplayName: "Frontend Team",
    TeamType:        "team",
    Role:            "member",
    JoinedAt:        time.Now(),
}

Package db provides PostgreSQL database access and management for StreamSpace.

This file implements user management and authentication data access.

Purpose: - CRUD operations for user accounts - Password hashing and verification with bcrypt - User authentication and authorization - User quota and resource limit management - User preferences and settings storage - Group membership management

Features: - Secure password hashing with bcrypt (cost factor 10) - User search and filtering by username/email/role/provider - Quota enforcement integration with quota system - User group membership tracking - OAuth provider linking (OIDC, SAML) - MFA (TOTP) configuration storage support - Last login tracking for auditing

Database Schema:

• users table: Core user account data

• id (varchar): Primary key (UUID)

• username (varchar): Unique username

• email (varchar): Unique email address

• password_hash (varchar): bcrypt hashed password (local auth only)

• role (varchar): User role (user, admin, superadmin)

• provider (varchar): Auth provider (local, saml, oidc)

• active (boolean): Account active status

• created_at, updated_at: Timestamps

• last_login: Last successful authentication

• user_quotas table: Resource limits per user

• user_id: Foreign key to users

• max_sessions, max_cpu, max_memory, max_storage: Limits

• used_sessions, used_cpu, used_memory, used_storage: Current usage

Implementation Details: - Passwords never stored in plaintext (bcrypt with cost 10) - User lookups indexed by username and email for performance - Quota stored as separate table with foreign key constraint - Group membership managed via group_memberships junction table - Default quota created automatically on user creation - Supports multiple authentication providers (local, SAML, OIDC)

Thread Safety: - All database operations are thread-safe via database/sql pool - Safe for concurrent access from multiple goroutines - bcrypt operations are CPU-intensive but safe for concurrent use

Dependencies: - golang.org/x/crypto/bcrypt for password hashing - github.com/google/uuid for ID generation - models package for data structures

Example Usage:

userDB := db.NewUserDB(database.DB())

// Create user with password
user, err := userDB.CreateUser(ctx, &models.CreateUserRequest{
    Username: "alice",
    Email:    "alice@example.com",
    Password: "securepassword",
    FullName: "Alice Smith",
    Role:     "user",
    Provider: "local",
})

// Authenticate user
user, err := userDB.VerifyPassword(ctx, "alice", "securepassword")

// Update user quota
maxSessions := 10
err := userDB.SetUserQuota(ctx, userID, &models.SetQuotaRequest{
    MaxSessions: &maxSessions,
})

// Get or create SAML user (SSO)
user, err := userDB.GetOrCreateSAMLUser(ctx, "bob", "bob@company.com", "Bob Jones", "saml-provider")