5. Initialise database

5. Initialise database

1. Ensure the flyway-core and flyway-database-postgresql dependencies are in your build.gradle.kts. Spring Initializr should have added org.flywaydb:flyway-core. You might need to explicitly add org.flywaydb:flyway-database-postgresql if you encounter driver issues with Flyway.
2. Create the directory src/main/resources/db/migration.
3. Create a file named V1__Initial_schema.sql inside that directory.
4. Paste the following SQL (PostgreSQL syntax) into the file:

-- V1__Initial_schema.sql
 
-- Enable UUID generation if not already enabled
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
 
-- Users Table
CREATE TABLE users (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    username VARCHAR(255) NOT NULL UNIQUE,
    hashed_password VARCHAR(255) NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
);
 
-- Projects Table
CREATE TABLE projects (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    name VARCHAR(255) NOT NULL UNIQUE,
    created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
);
 
-- Roles Enum Type (Postgres specific)
DO $$ BEGIN
    CREATE TYPE role_enum AS ENUM ('ADMIN', 'USER', 'VIEWER');
EXCEPTION
    WHEN duplicate_object THEN null;
END $$;
 
-- Project User Roles Table
CREATE TABLE project_user_roles (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(), -- Surrogate Key
    project_id UUID NOT NULL REFERENCES projects(id) ON DELETE CASCADE,
    user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    role role_enum NOT NULL,
    UNIQUE (project_id, user_id) -- Ensure user has only one role per project
);
CREATE INDEX idx_project_user_roles_project_id ON project_user_roles(project_id);
CREATE INDEX idx_project_user_roles_user_id ON project_user_roles(user_id);
 
-- Runtime Type Enum Type (Postgres specific)
DO $$ BEGIN
    CREATE TYPE runtime_type_enum AS ENUM ('HTTP', 'CELERY');
EXCEPTION
    WHEN duplicate_object THEN null;
END $$;
 
-- Worker Definitions Table
CREATE TABLE worker_definitions (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    project_id UUID NOT NULL REFERENCES projects(id) ON DELETE CASCADE,
    name VARCHAR(255) NOT NULL,
    runtime_type runtime_type_enum NOT NULL,
    config_json TEXT NOT NULL, -- Store as TEXT, validate JSON in application
    parameter_schema_json TEXT NOT NULL, -- Store as TEXT, validate JSON in application
    created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE (project_id, name) -- Worker name unique within a project
);
CREATE INDEX idx_worker_definitions_project_id ON worker_definitions(project_id);
 
-- Job Status Enum Type (Postgres specific)
DO $$ BEGIN
    CREATE TYPE job_status_enum AS ENUM ('PENDING', 'QUEUED', 'STARTED', 'SUCCESS', 'FAILURE');
EXCEPTION
    WHEN duplicate_object THEN null;
END $$;
 
-- Jobs Table
CREATE TABLE jobs (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    project_id UUID NOT NULL REFERENCES projects(id) ON DELETE CASCADE,
    worker_definition_id UUID NOT NULL REFERENCES worker_definitions(id) ON DELETE RESTRICT, -- Don't delete worker if jobs exist? Or CASCADE?
    submitted_by_user_id UUID NOT NULL REFERENCES users(id),
    parameters_json TEXT NOT NULL,
    status job_status_enum NOT NULL DEFAULT 'PENDING',
    result_json TEXT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    started_at TIMESTAMPTZ NULL,
    completed_at TIMESTAMPTZ NULL
);
CREATE INDEX idx_jobs_project_id ON jobs(project_id);
CREATE INDEX idx_jobs_status ON jobs(status);
CREATE INDEX idx_jobs_created_at ON jobs(created_at);
 
-- Job Logs Table
CREATE TABLE job_logs (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    job_id UUID NOT NULL REFERENCES jobs(id) ON DELETE CASCADE,
    timestamp TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    message TEXT NOT NULL
);
CREATE INDEX idx_job_logs_job_id_timestamp ON job_logs(job_id, timestamp);
 
-- Schedule Type Enum Type (Postgres specific)
DO $$ BEGIN
    CREATE TYPE schedule_type_enum AS ENUM ('ONE_OFF', 'RECURRING');
EXCEPTION
    WHEN duplicate_object THEN null;
END $$;
 
-- Schedules Table
CREATE TABLE schedules (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    project_id UUID NOT NULL REFERENCES projects(id) ON DELETE CASCADE,
    worker_definition_id UUID NOT NULL REFERENCES worker_definitions(id) ON DELETE CASCADE, -- Cascade delete schedule if worker is deleted
    created_by_user_id UUID NOT NULL REFERENCES users(id),
    type schedule_type_enum NOT NULL,
    run_at TIMESTAMPTZ NULL, -- For ONE_OFF
    cron_expression VARCHAR(255) NULL, -- For RECURRING
    parameters_json TEXT NOT NULL,
    is_enabled BOOLEAN NOT NULL DEFAULT TRUE,
    last_run_at TIMESTAMPTZ NULL,
    next_run_at TIMESTAMPTZ NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX idx_schedules_project_id ON schedules(project_id);
CREATE INDEX idx_schedules_next_run_at ON schedules(next_run_at) WHERE is_enabled = TRUE; -- Index for scheduler polling
 
-- Log Level Enum Type (Postgres specific)
DO $$ BEGIN
    CREATE TYPE log_level_enum AS ENUM ('INFO', 'WARN', 'ERROR');
EXCEPTION
    WHEN duplicate_object THEN null;
END $$;
 
-- Regent System Logs Table
CREATE TABLE regent_logs (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    timestamp TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    level log_level_enum NOT NULL,
    message TEXT NOT NULL,
    details_json TEXT NULL
);
CREATE INDEX idx_regent_logs_timestamp ON regent_logs(timestamp);
CREATE INDEX idx_regent_logs_level ON regent_logs(level);
 

Next Step