47 lines
2.3 KiB
SQL
47 lines
2.3 KiB
SQL
ALTER TABLE instances RENAME TO instances_v2;
|
|
|
|
CREATE TABLE instances (
|
|
id TEXT PRIMARY KEY,
|
|
slug TEXT NOT NULL UNIQUE,
|
|
display_name TEXT NOT NULL,
|
|
template_id TEXT NOT NULL,
|
|
template_version TEXT NOT NULL,
|
|
template_digest TEXT NOT NULL,
|
|
revision INTEGER NOT NULL CHECK (revision >= 1),
|
|
lifecycle_state TEXT NOT NULL CHECK (lifecycle_state IN ('draft', 'installing', 'stopped', 'starting', 'online', 'stopping', 'backup', 'restore', 'update', 'degraded', 'error', 'unknown', 'intervention_required', 'deleting', 'deleted')),
|
|
observed_state TEXT NOT NULL DEFAULT 'unknown' CHECK (observed_state IN ('unknown', 'missing', 'stopped', 'running', 'ready', 'degraded')),
|
|
preview_json TEXT NOT NULL,
|
|
plan_digest TEXT NOT NULL,
|
|
container_id TEXT,
|
|
desired_running INTEGER NOT NULL DEFAULT 0 CHECK (desired_running IN (0, 1)),
|
|
last_error_code TEXT,
|
|
deleted_at TEXT,
|
|
created_at TEXT NOT NULL,
|
|
updated_at TEXT NOT NULL,
|
|
FOREIGN KEY (template_id, template_version) REFERENCES template_versions(template_id, version) ON DELETE RESTRICT
|
|
);
|
|
|
|
INSERT INTO instances(id, slug, display_name, template_id, template_version, template_digest, revision, lifecycle_state, preview_json, plan_digest, created_at, updated_at)
|
|
SELECT id, slug, display_name, template_id, template_version, template_digest, revision, lifecycle_state, preview_json, plan_digest, created_at, updated_at
|
|
FROM instances_v2;
|
|
|
|
DROP TABLE instances_v2;
|
|
|
|
CREATE INDEX instances_template_idx ON instances(template_id, template_version);
|
|
CREATE INDEX instances_lifecycle_idx ON instances(lifecycle_state);
|
|
|
|
CREATE TABLE instance_operations (
|
|
id TEXT PRIMARY KEY,
|
|
instance_id TEXT NOT NULL REFERENCES instances(id) ON DELETE RESTRICT,
|
|
kind TEXT NOT NULL CHECK (kind IN ('install', 'start', 'stop', 'restart', 'delete_container', 'reconcile')),
|
|
state TEXT NOT NULL CHECK (state IN ('running', 'succeeded', 'failed', 'intervention_required')),
|
|
phase TEXT NOT NULL,
|
|
error_code TEXT,
|
|
created_at TEXT NOT NULL,
|
|
updated_at TEXT NOT NULL,
|
|
completed_at TEXT
|
|
);
|
|
|
|
CREATE UNIQUE INDEX instance_operation_active_idx ON instance_operations(instance_id) WHERE state = 'running';
|
|
CREATE INDEX instance_operation_history_idx ON instance_operations(instance_id, created_at DESC);
|