Database Setup

Overview

suSHi uses PostgreSQL as its primary database with automatic migrations powered by Goose. The application automatically runs migrations on startup when MIGRATE_DB=true is set.

Database Requirements

PostgreSQL 12 or later
Support for UUID generation (gen_random_uuid())
Minimum 100MB storage for initial setup
User with CREATE TABLE and CREATE INDEX privileges

Quick Setup

Start PostgreSQL

Using Docker Compose (recommended):

docker-compose up -d postgres

Or install PostgreSQL locally:

# Ubuntu/Debian
sudo apt-get install postgresql

# macOS
brew install postgresql

Configure database connection

Set the required environment variables:

export DB_HOST=localhost
export DB_PORT=5432
export DB_USER=postgres
export DB_PASSWORD=postgres
export DB_NAME=sushi
export MIGRATE_DB=true

Start the application

The application will automatically create the database schema on first startup:

./app

Or with Docker:

docker-compose up -d sushi-backend

Verify database setup

Connect to PostgreSQL and verify tables were created:

psql -h localhost -U postgres -d sushi -c "\dt"

You should see: users, organizations, and machines tables.

Migration System

How Migrations Work

suSHi uses Goose for database migrations. The migration system is implemented in db/migrations.go:

func Migrate(config models.Config) error {
    log.Debug().Msgf("DoMigrations : %v", config.DoMigrations)

    if !config.DoMigrations {
        log.Debug().Msg("Skipping database migration")
        return nil
    }

    log.Debug().Msg("Migrating the database")
    db, error := sql.Open("postgres", config.DatabaseConfig.String)
    if error != nil {
        return error
    }
    defer db.Close()

    goose.SetDialect("postgres")
    err := goose.Up(db, "db/migrations")
    if err != nil {
        return err
    }
    
    return nil
}

Migration Files

Migrations are stored in the db/migrations/ directory and are executed in order:

000001_create_tables_users.sql - Creates the users table
000002_create_tables_organisations.sql - Creates the organizations table
000003_create_tables_machines.sql - Creates the machines table
000004_create_dummy_user.sql - Inserts a test user for development

Migrations run automatically when MIGRATE_DB=true. Each migration runs only once, tracked by Goose’s version table.

Database Schema

Users Table

Stores user accounts with UUID-based identifiers.

CREATE TABLE users (
    username UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name VARCHAR(255) NOT NULL,
    email VARCHAR(255) UNIQUE NOT NULL,
    salt VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Fields:

username

UUID

required

Primary key, auto-generated UUID for each user.

name

VARCHAR(255)

required

User’s display name.

VARCHAR(255)

required

User’s email address. Must be unique across all users.

salt

VARCHAR(255)

required

Cryptographic salt for password hashing or token generation.

created_at

TIMESTAMP

default:"CURRENT_TIMESTAMP"

Timestamp when the user was created.

updated_at

TIMESTAMP

default:"CURRENT_TIMESTAMP"

Timestamp when the user was last updated.

Migration file: db/migrations/000001_create_tables_users.sql

Organizations Table

Stores organization entities that can own machines.

CREATE TABLE organizations (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255) UNIQUE NOT NULL,
    salt VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Fields:

SERIAL

required

Auto-incrementing primary key.

name

VARCHAR(255)

required

Organization name. Must be unique across all organizations.

salt

VARCHAR(255)

required

Cryptographic salt for organization-specific encryption.

created_at

TIMESTAMP

default:"CURRENT_TIMESTAMP"

Timestamp when the organization was created.

updated_at

TIMESTAMP

default:"CURRENT_TIMESTAMP"

Timestamp when the organization was last updated.

Migration file: db/migrations/000002_create_tables_organisations.sql

Machines Table

Stores SSH connection information for remote machines with encrypted credentials.

CREATE TABLE machines (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    username VARCHAR(100) NOT NULL,
    hostname VARCHAR(255) NOT NULL,
    port INTEGER NOT NULL DEFAULT 22,
    encrypted_private_key TEXT,
    iv_private_key TEXT,
    encrypted_passphrase TEXT,
    iv_passphrase TEXT,
    owner_id UUID NOT NULL,
    owner_type VARCHAR(20) NOT NULL CHECK (owner_type IN ('user','organization')),
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

Fields:

SERIAL

required

Auto-incrementing primary key.

name

VARCHAR(100)

required

Display name for the machine.

username

VARCHAR(100)

required

SSH username for connecting to the machine.

hostname

VARCHAR(255)

required

Machine hostname or IP address.

port

INTEGER

default:"22"

SSH port number.

encrypted_private_key

TEXT

Encrypted SSH private key for authentication.

iv_private_key

TEXT

Initialization vector for private key encryption.

encrypted_passphrase

TEXT

Encrypted passphrase if the private key is passphrase-protected.

iv_passphrase

TEXT

Initialization vector for passphrase encryption.

owner_id

UUID

required

UUID of the owner (user or organization).

owner_type

VARCHAR(20)

required

Type of owner: user or organization. Constrained by CHECK constraint.

created_at

TIMESTAMP WITH TIME ZONE

default:"CURRENT_TIMESTAMP"

Timestamp when the machine was added.

updated_at

TIMESTAMP WITH TIME ZONE

default:"CURRENT_TIMESTAMP"

Timestamp when the machine was last updated.

Migration file: db/migrations/000003_create_tables_machines.sql

SSH credentials are encrypted before storage. The encrypted_private_key and encrypted_passphrase fields store sensitive data securely.

Initial Data

Dummy User

For development and testing, a dummy user is automatically created:

INSERT INTO users (username, name, email, salt)
VALUES (
    '6b338158-32ca-4b53-a273-f54e3244697e',
    'Dummy User',
    '[email protected]',
    'randomsalt123456789'
);

Migration file: db/migrations/000004_create_dummy_user.sql

Remove or disable this migration in production by editing or deleting 000004_create_dummy_user.sql before deployment.

Manual Database Operations

Connect to Database

docker exec -it postgres psql -U postgres -d sushi

View Tables

-- List all tables
\dt

-- Describe a specific table
\d users
\d organizations
\d machines

Check Migration Status

Goose maintains a version table to track applied migrations:

SELECT * FROM goose_db_version;

This shows which migrations have been applied and when.

Query Data

SELECT username, name, email, created_at FROM users;

Backup and Restore

Backup Database

pg_dump -h localhost -U postgres -d sushi > sushi_backup.sql

Restore Database

psql -h localhost -U postgres -d sushi < sushi_backup.sql

Always test your backup restoration process in a non-production environment.

Managing Migrations

Disable Automatic Migrations

To prevent migrations from running automatically:

export MIGRATE_DB=false

Or in docker-compose.yaml:

environment:
  - MIGRATE_DB=false

Run Migrations Manually

If you prefer to run migrations manually, you can use Goose directly:

Install Goose

go install github.com/pressly/goose/v3/cmd/goose@latest

Run migrations

goose -dir db/migrations postgres "host=localhost port=5432 user=postgres password=postgres dbname=sushi sslmode=disable" up

Check status

goose -dir db/migrations postgres "host=localhost port=5432 user=postgres password=postgres dbname=sushi sslmode=disable" status

Create New Migration

To add a new migration:

goose -dir db/migrations create your_migration_name sql

This creates a new file: db/migrations/YYYYMMDDHHMMSS_your_migration_name.sql Edit the file with your SQL:

-- +goose Up
-- Add your schema changes here
CREATE TABLE new_table (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255)
);

-- +goose Down
-- Add rollback logic here
DROP TABLE IF EXISTS new_table;

Production Considerations

Database Security

Use strong passwords: Change default PostgreSQL passwords
Limit network access: Use firewall rules to restrict database access
Enable SSL/TLS: Configure PostgreSQL to use encrypted connections
Regular backups: Implement automated backup schedules
Monitor disk space: Ensure adequate storage for growth

Performance Optimization

-- Add indexes for common queries
CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_machines_owner ON machines(owner_id, owner_type);
CREATE INDEX idx_organizations_name ON organizations(name);

Connection Pooling

suSHi uses pgxpool for connection pooling (from models/config.go):

type Config struct {
    DB *pgxpool.Pool  // Connection pool
    // ...
}

The connection pool is configured automatically based on your database settings.

Troubleshooting

Migration Failures

Error: “migration failed” Check the application logs for specific SQL errors:

docker-compose logs sushi-backend | grep -i migration

Error: “relation already exists” The migration may have partially completed. Check the goose_db_version table:

SELECT * FROM goose_db_version;

To fix, manually mark the migration as complete or roll back and retry.

Connection Issues

Error: “connection refused”

Verify PostgreSQL is running: docker-compose ps postgres
Check database host and port in environment variables
Ensure firewall allows connections on port 5432

Error: “authentication failed”

Verify database credentials match between docker-compose.yaml postgres service and sushi-backend service
Check PostgreSQL logs: docker-compose logs postgres

Data Issues

UUID generation not working Ensure your PostgreSQL version supports gen_random_uuid() (PostgreSQL 13+) or install the pgcrypto extension:

CREATE EXTENSION IF NOT EXISTS pgcrypto;

Next Steps

Configure Environment Variables
Set up Docker Deployment
Learn about the API endpoints in the API Reference

Get Started

Features

Deployment

Support

Database Setup

Overview

Database Requirements

Quick Setup

Migration System

How Migrations Work

Migration Files

Database Schema

Users Table

Organizations Table

Machines Table

Initial Data

Dummy User

Manual Database Operations

Connect to Database

View Tables

Check Migration Status

Query Data

Backup and Restore

Backup Database

Restore Database

Managing Migrations

Disable Automatic Migrations

Run Migrations Manually

Create New Migration

Production Considerations

Database Security

Performance Optimization

Connection Pooling

Troubleshooting

Migration Failures

Connection Issues

Data Issues

Next Steps

Build docs developers (and LLMs) love

Get Started

Features

Deployment

Support

​Overview

​Database Requirements

​Quick Setup

​Migration System

​How Migrations Work

​Migration Files

​Database Schema

​Users Table

​Organizations Table

​Machines Table

​Initial Data

​Dummy User

​Manual Database Operations

​Connect to Database

​View Tables

​Check Migration Status

​Query Data

​Backup and Restore

​Backup Database

​Restore Database

​Managing Migrations

​Disable Automatic Migrations

​Run Migrations Manually

​Create New Migration

​Production Considerations

​Database Security

​Performance Optimization

​Connection Pooling

​Troubleshooting

​Migration Failures

​Connection Issues

​Data Issues

​Next Steps

Build docs developers (and LLMs) love

Overview

Database Requirements

Quick Setup

Migration System

How Migrations Work

Migration Files

Database Schema

Users Table

Organizations Table

Machines Table

Initial Data

Dummy User

Manual Database Operations

Connect to Database

View Tables

Check Migration Status

Query Data

Backup and Restore

Backup Database

Restore Database

Managing Migrations

Disable Automatic Migrations

Run Migrations Manually

Create New Migration

Production Considerations

Database Security

Performance Optimization

Connection Pooling

Troubleshooting

Migration Failures

Connection Issues

Data Issues

Next Steps