SLOzy

Appearance

Database Migrations ​

This directory contains database migrations for SLOzy.

Migration Files ​

Foundation (000001-000003) ​

  • 000001_organizations.up.sql - Organizations table
  • 000001_organizations.down.sql - Rollback organizations
  • 000002_teams.up.sql - Teams table
  • 000002_teams.down.sql - Rollback teams
  • 000003_users.up.sql - Users table with authentication
  • 000003_users.down.sql - Rollback users

SLO Core (000004-000006) ​

  • 000004_slos.up.sql - SLO definitions with RBAC
  • 000004_slos.down.sql - Rollback SLOs
  • 000005_slo_versions.up.sql - SLO versioning
  • 000005_slo_versions.down.sql - Rollback versions
  • 000006_slo_audit_log.up.sql - Audit logging
  • 000006_slo_audit_log.down.sql - Rollback audit logs

Data Sources & Teams (000007-000008) ​

  • 000007_prometheus_data_sources.up.sql - Prometheus integration
  • 000007_prometheus_data_sources.down.sql - Rollback data sources
  • 000008_team_memberships.up.sql - Team memberships
  • 000008_team_memberships.down.sql - Rollback memberships

Metrics & Monitoring (000009-000012) ​

  • 000009_slo_metrics.up.sql - Time-series metrics storage
  • 000009_slo_metrics.down.sql - Rollback metrics tables
  • 000010_update_function.up.sql - Timestamp update function
  • 000010_update_function.down.sql - Rollback update function
  • 000011_anomaly_detections.up.sql - Anomaly detection tables
  • 000011_anomaly_detections.down.sql - Rollback anomaly detection
  • 000012_slo_recommendations.up.sql - SLO recommendations
  • 000012_slo_recommendations.down.sql - Rollback recommendations

Running Migrations ​

Using Scripts ​

bash
# Validate migrations
./scripts/validate-migrations.sh

# Test migrations (up then down)
./scripts/test-migrations.sh

Using Goose Directly ​

bash
# Create database
createdb slozy

# Run all migrations
goose -dir migrations postgres "postgres://user:password@localhost:5432/slozy?sslmode=disable" up

# Check status
goose -dir migrations postgres "postgres://user:password@localhost:5432/slozy?sslmode=disable" version

# Rollback one migration
goose -dir migrations postgres "postgres://user:password@localhost:5432/slozy?sslmode=disable" down

# Rollback all migrations
goose -dir migrations postgres "postgres://user:password@localhost:5432/slozy?sslmode=disable" down

Creating New Migrations ​

bash
# Create new migration
goose -dir migrations create migration_name sql

# This creates up and down migration files

Migration Rules ​

  • All migrations must be reversible (both .up and .down files)
  • No seed data or fixed data in migrations
  • Use proper indexes for performance
  • Follow naming conventions
  • Test both up and down migrations before committing
  • Document schema changes here

Database Schema Overview ​

Core Tables ​

  • organizations: Multi-organization support for enterprise deployments
  • **teams organizational units within organizations
  • users: User accounts with authentication and profile data
  • organizations: Manage teams and users

SLO Tables ​

  • slos: Service Level Objective definitions with metadata
  • slo_versions: Version history for SLO changes
  • slo_metrics: Time-series metric data from Prometheus
  • slo_audit_log: Audit trail for all SLO modifications
  • anomaly_detections: Automated anomaly detection results
  • slo_recommendations: AI-powered SLO improvement recommendations

Integration Tables ​

  • prometheus_data_sources: Prometheus server configurations
  • team_memberships: Many-to-many relationship between users and teams

Indexes ​

Performance-critical indexes are included:

  • Foreign key lookups (slo_id, user_id, team_id, org_id)
  • Time-series queries (timestamp indices on slo_metrics)
  • Common query patterns (status, active flags, resolved flags)
  • Cascading deletes with proper ON DELETE CASCADE

Data Models ​

See Architecture for detailed data model documentation.