Database Schema & Migrations
Relevant source files
The following files were used as context for generating this wiki page:
Database Schema & Migrations in the Hoop project encompass the structured definition of the system's data and the automated processes used to evolve that data over time. The project primarily utilizes PostgreSQL as its backend store, leveraging golang-migrate for sequential SQL migrations and custom Go-based logic for specific forward-only state management.
The system is designed with a multi-layered approach: a private schema handles the core relational data and internal state, while a public schema (and later public_b) provides views and functions for external access via PostgREST. Additionally, the project includes specialized logic for managing external database schemas (Postgres, MySQL, MSSQL, MongoDB) when those systems are connected as resources.
Sources: scripts/check-migration-safety.sh:40-52, rootfs/app/migrations/000001_init.up.sql:1-5, rootfs/app/migrations/000015_postgrest_migrations.up.sql:1-40
Migration Architecture & Workflow
The Hoop gateway manages database evolution through a series of numbered SQL migration files. These files are executed automatically on startup.
Migration Components
- SQL Migrations: Located in
rootfs/app/migrations/, these files use sequential numbering (e.g.,000001,000015). Each migration typically includes an.up.sqlfor applying changes and a.down.sqlfor rollbacks. - Go-Coded Migrations: Forward-only migrations that run after SQL migrations and do not have a rollback path.
- Migration Safety Analysis: A script-based tool using LLMs (Claude API) to analyze new migrations for destructive operations, rollback safety, and best practices.
Sources: scripts/check-migration-safety.sh:40-52
Database Migration Flow
The gateway follows a specific sequence when initializing or updating the database.
The gateway logs a warning but remains operational if it detects a newer schema version than its current binary supports.
Sources: scripts/check-migration-safety.sh:1-50
Core Data Schema (private schema)
The private schema contains the fundamental entities of the Hoop system, including organizations, users, agents, and sessions.
Entity Relationship Diagram
This diagram represents the core tables defined during the initial migration.
Core Tables Summary
| Table | Purpose | Key Fields |
|---|---|---|
orgs | Root multi-tenancy unit. | id, name |
users | System actors and their status. | id, org_id, email, status |
agents | Connection brokers/nodes. | id, org_id, name, token, status |
connections | Resource targets (DBs, SSH, etc). | id, org_id, agent_id, name, type |
sessions | Audit logs of interaction. | id, connection, verb, user_id, status |
reviews | Approval requests for actions. | id, session_id, status, owner_id |
Sources: rootfs/app/migrations/000001_init.up.sql:15-180
PostgREST and Schema Evolution
As the project evolved, the migration strategy for PostgREST views and functions changed. Starting with migration 000015, the system separated the core relational schema from the public-facing API views.
privateSchema: Continues to store data and primary tables.publicSchema: Previously held views for users, agents, and sessions. These were dropped in migration000015to be handled by a different process.appstateTable: Introduced to track the state of views and functions, including rollbacks and checksums.
Sources: rootfs/app/migrations/000015_postgrest_migrations.up.sql:1-55
External Database Schema Management
Hoop includes specialized components to inspect and manage the schemas of connected databases (Postgres, MySQL, MSSQL, MongoDB, DynamoDB).
Schema Discovery Queries
The system uses native SQL queries to retrieve table and column metadata from various database types.
| DB Type | Table Discovery Source | Column Discovery Source |
|---|---|---|
| Postgres | pg_catalog.pg_class | pg_catalog.pg_attribute |
| MySQL | INFORMATION_SCHEMA.TABLES | INFORMATION_SCHEMA.COLUMNS |
| MSSQL | sys.objects | sys.columns |
| MongoDB | getCollectionNames() | Sampling documents (up to 10) |
| DynamoDB | aws dynamodb list-tables | aws dynamodb describe-table |
Sources: gateway/api/connections/queries_schema.go:48-180
Programmatic SQL Planning
For internal PostgreSQL management, particularly when managing roles and privileges at the cluster level, the pgmanager package generates execution plans.
The sqlPlan structure deduplicates commands like GRANT CONNECT across multiple scopes and ensures that destructive REVOKE operations occur before GRANT operations to maintain a clean security state.
Sources: agent/controller/system/pgmanager/plan.go:120-300
Summary
The Database Schema & Migrations system in Hoop provides a robust framework for multi-tenant data management and infrastructure access control. By combining strictly numbered SQL migrations with programmatic SQL planning and automated safety checks, the system ensures data integrity while allowing for the dynamic inspection of external resources. This architecture supports both the internal state requirements of the gateway and the external visibility required for features like PostgREST APIs and data masking.