Juergen Kunz
ad89f2cc1f
- Added AuthRepository for user and settings management with CRUD operations. - Introduced CertificateRepository to handle domains, certificates, and requirements. - Created MetricsRepository for managing metrics and logs. - Developed PlatformRepository for platform services and resources management. - Established RegistryRepository for registry and token operations. - Implemented ServiceRepository for CRUD operations on services. - Defined types and interfaces in types.ts for database interactions.
1082 lines
36 KiB
TypeScript
1082 lines
36 KiB
TypeScript
/**
|
|
* Database layer for Onebox using SQLite
|
|
* Refactored into repository pattern
|
|
*/
|
|
|
|
import * as plugins from '../plugins.ts';
|
|
import type {
|
|
IService,
|
|
IRegistry,
|
|
IRegistryToken,
|
|
ISslCertificate,
|
|
IMetric,
|
|
ILogEntry,
|
|
IUser,
|
|
IPlatformService,
|
|
IPlatformResource,
|
|
TPlatformServiceType,
|
|
IDomain,
|
|
ICertificate,
|
|
ICertRequirement,
|
|
} from '../types.ts';
|
|
import type { TBindValue } from './types.ts';
|
|
import { logger } from '../logging.ts';
|
|
import { getErrorMessage } from '../utils/error.ts';
|
|
|
|
// Import repositories
|
|
import {
|
|
ServiceRepository,
|
|
RegistryRepository,
|
|
CertificateRepository,
|
|
AuthRepository,
|
|
MetricsRepository,
|
|
PlatformRepository,
|
|
} from './repositories/index.ts';
|
|
|
|
export class OneboxDatabase {
|
|
private db: InstanceType<typeof plugins.sqlite.DB> | null = null;
|
|
private dbPath: string;
|
|
|
|
// Repositories
|
|
private serviceRepo!: ServiceRepository;
|
|
private registryRepo!: RegistryRepository;
|
|
private certificateRepo!: CertificateRepository;
|
|
private authRepo!: AuthRepository;
|
|
private metricsRepo!: MetricsRepository;
|
|
private platformRepo!: PlatformRepository;
|
|
|
|
constructor(dbPath = './.nogit/onebox.db') {
|
|
this.dbPath = dbPath;
|
|
}
|
|
|
|
/**
|
|
* Initialize database connection and create tables
|
|
*/
|
|
async init(): Promise<void> {
|
|
try {
|
|
// Ensure data directory exists
|
|
const dbDir = plugins.path.dirname(this.dbPath);
|
|
await Deno.mkdir(dbDir, { recursive: true });
|
|
|
|
// Open database
|
|
this.db = new plugins.sqlite.DB(this.dbPath);
|
|
logger.info(`Database initialized at ${this.dbPath}`);
|
|
|
|
// Create tables
|
|
await this.createTables();
|
|
|
|
// Run migrations if needed
|
|
await this.runMigrations();
|
|
|
|
// Initialize repositories with bound query function
|
|
const queryFn = this.query.bind(this);
|
|
this.serviceRepo = new ServiceRepository(queryFn);
|
|
this.registryRepo = new RegistryRepository(queryFn);
|
|
this.certificateRepo = new CertificateRepository(queryFn);
|
|
this.authRepo = new AuthRepository(queryFn);
|
|
this.metricsRepo = new MetricsRepository(queryFn);
|
|
this.platformRepo = new PlatformRepository(queryFn);
|
|
} catch (error) {
|
|
logger.error(`Failed to initialize database: ${getErrorMessage(error)}`);
|
|
throw error;
|
|
}
|
|
}
|
|
|
|
/**
|
|
* Create all database tables
|
|
*/
|
|
private async createTables(): Promise<void> {
|
|
if (!this.db) throw new Error('Database not initialized');
|
|
|
|
// Services table
|
|
this.query(`
|
|
CREATE TABLE IF NOT EXISTS services (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
name TEXT NOT NULL UNIQUE,
|
|
image TEXT NOT NULL,
|
|
registry TEXT,
|
|
env_vars TEXT NOT NULL,
|
|
port INTEGER NOT NULL,
|
|
domain TEXT,
|
|
container_id TEXT,
|
|
status TEXT NOT NULL DEFAULT 'stopped',
|
|
created_at INTEGER NOT NULL,
|
|
updated_at INTEGER NOT NULL
|
|
)
|
|
`);
|
|
|
|
// Registries table
|
|
this.query(`
|
|
CREATE TABLE IF NOT EXISTS registries (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
url TEXT NOT NULL UNIQUE,
|
|
username TEXT NOT NULL,
|
|
password_encrypted TEXT NOT NULL,
|
|
created_at INTEGER NOT NULL
|
|
)
|
|
`);
|
|
|
|
// Nginx configs table
|
|
this.query(`
|
|
CREATE TABLE IF NOT EXISTS nginx_configs (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
service_id INTEGER NOT NULL,
|
|
domain TEXT NOT NULL,
|
|
port INTEGER NOT NULL,
|
|
ssl_enabled INTEGER NOT NULL DEFAULT 0,
|
|
config_template TEXT NOT NULL,
|
|
created_at INTEGER NOT NULL,
|
|
updated_at INTEGER NOT NULL,
|
|
FOREIGN KEY (service_id) REFERENCES services(id) ON DELETE CASCADE
|
|
)
|
|
`);
|
|
|
|
// SSL certificates table
|
|
this.query(`
|
|
CREATE TABLE IF NOT EXISTS ssl_certificates (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
domain TEXT NOT NULL UNIQUE,
|
|
cert_path TEXT NOT NULL,
|
|
key_path TEXT NOT NULL,
|
|
full_chain_path TEXT NOT NULL,
|
|
expiry_date INTEGER NOT NULL,
|
|
issuer TEXT NOT NULL,
|
|
created_at INTEGER NOT NULL,
|
|
updated_at INTEGER NOT NULL
|
|
)
|
|
`);
|
|
|
|
// DNS records table
|
|
this.query(`
|
|
CREATE TABLE IF NOT EXISTS dns_records (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
domain TEXT NOT NULL UNIQUE,
|
|
type TEXT NOT NULL,
|
|
value TEXT NOT NULL,
|
|
cloudflare_id TEXT,
|
|
zone_id TEXT,
|
|
created_at INTEGER NOT NULL,
|
|
updated_at INTEGER NOT NULL
|
|
)
|
|
`);
|
|
|
|
// Metrics table
|
|
this.query(`
|
|
CREATE TABLE IF NOT EXISTS metrics (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
service_id INTEGER NOT NULL,
|
|
timestamp INTEGER NOT NULL,
|
|
cpu_percent REAL NOT NULL,
|
|
memory_used INTEGER NOT NULL,
|
|
memory_limit INTEGER NOT NULL,
|
|
network_rx_bytes INTEGER NOT NULL,
|
|
network_tx_bytes INTEGER NOT NULL,
|
|
FOREIGN KEY (service_id) REFERENCES services(id) ON DELETE CASCADE
|
|
)
|
|
`);
|
|
|
|
// Create index for metrics queries
|
|
this.query(`
|
|
CREATE INDEX IF NOT EXISTS idx_metrics_service_timestamp
|
|
ON metrics(service_id, timestamp DESC)
|
|
`);
|
|
|
|
// Logs table
|
|
this.query(`
|
|
CREATE TABLE IF NOT EXISTS logs (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
service_id INTEGER NOT NULL,
|
|
timestamp INTEGER NOT NULL,
|
|
message TEXT NOT NULL,
|
|
level TEXT NOT NULL,
|
|
source TEXT NOT NULL,
|
|
FOREIGN KEY (service_id) REFERENCES services(id) ON DELETE CASCADE
|
|
)
|
|
`);
|
|
|
|
// Create index for logs queries
|
|
this.query(`
|
|
CREATE INDEX IF NOT EXISTS idx_logs_service_timestamp
|
|
ON logs(service_id, timestamp DESC)
|
|
`);
|
|
|
|
// Users table
|
|
this.query(`
|
|
CREATE TABLE IF NOT EXISTS users (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
username TEXT NOT NULL UNIQUE,
|
|
password_hash TEXT NOT NULL,
|
|
role TEXT NOT NULL DEFAULT 'user',
|
|
created_at INTEGER NOT NULL,
|
|
updated_at INTEGER NOT NULL
|
|
)
|
|
`);
|
|
|
|
// Settings table
|
|
this.query(`
|
|
CREATE TABLE IF NOT EXISTS settings (
|
|
key TEXT PRIMARY KEY,
|
|
value TEXT NOT NULL,
|
|
updated_at INTEGER NOT NULL
|
|
)
|
|
`);
|
|
|
|
// Version table for migrations
|
|
this.query(`
|
|
CREATE TABLE IF NOT EXISTS migrations (
|
|
version INTEGER PRIMARY KEY,
|
|
applied_at INTEGER NOT NULL
|
|
)
|
|
`);
|
|
|
|
logger.debug('Database tables created successfully');
|
|
}
|
|
|
|
/**
|
|
* Run database migrations
|
|
*/
|
|
private async runMigrations(): Promise<void> {
|
|
if (!this.db) throw new Error('Database not initialized');
|
|
|
|
try {
|
|
const currentVersion = this.getMigrationVersion();
|
|
logger.info(`Current database migration version: ${currentVersion}`);
|
|
|
|
// Migration 1: Initial schema
|
|
if (currentVersion === 0) {
|
|
logger.info('Setting initial migration version to 1');
|
|
this.setMigrationVersion(1);
|
|
}
|
|
|
|
// Migration 2: Convert timestamp columns from INTEGER to REAL
|
|
const updatedVersion = this.getMigrationVersion();
|
|
if (updatedVersion < 2) {
|
|
logger.info('Running migration 2: Converting timestamps to REAL...');
|
|
|
|
// SSL certificates
|
|
this.query(`
|
|
CREATE TABLE ssl_certificates_new (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
domain TEXT NOT NULL UNIQUE,
|
|
cert_path TEXT NOT NULL,
|
|
key_path TEXT NOT NULL,
|
|
full_chain_path TEXT NOT NULL,
|
|
expiry_date REAL NOT NULL,
|
|
issuer TEXT NOT NULL,
|
|
created_at REAL NOT NULL,
|
|
updated_at REAL NOT NULL
|
|
)
|
|
`);
|
|
this.query(`INSERT INTO ssl_certificates_new SELECT * FROM ssl_certificates`);
|
|
this.query(`DROP TABLE ssl_certificates`);
|
|
this.query(`ALTER TABLE ssl_certificates_new RENAME TO ssl_certificates`);
|
|
|
|
// Services
|
|
this.query(`
|
|
CREATE TABLE services_new (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
name TEXT NOT NULL UNIQUE,
|
|
image TEXT NOT NULL,
|
|
registry TEXT,
|
|
env_vars TEXT NOT NULL,
|
|
port INTEGER NOT NULL,
|
|
domain TEXT,
|
|
container_id TEXT,
|
|
status TEXT NOT NULL DEFAULT 'stopped',
|
|
created_at REAL NOT NULL,
|
|
updated_at REAL NOT NULL
|
|
)
|
|
`);
|
|
this.query(`INSERT INTO services_new SELECT * FROM services`);
|
|
this.query(`DROP TABLE services`);
|
|
this.query(`ALTER TABLE services_new RENAME TO services`);
|
|
|
|
// Registries
|
|
this.query(`
|
|
CREATE TABLE registries_new (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
url TEXT NOT NULL UNIQUE,
|
|
username TEXT NOT NULL,
|
|
password_encrypted TEXT NOT NULL,
|
|
created_at REAL NOT NULL
|
|
)
|
|
`);
|
|
this.query(`INSERT INTO registries_new SELECT * FROM registries`);
|
|
this.query(`DROP TABLE registries`);
|
|
this.query(`ALTER TABLE registries_new RENAME TO registries`);
|
|
|
|
// Nginx configs
|
|
this.query(`
|
|
CREATE TABLE nginx_configs_new (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
service_id INTEGER NOT NULL,
|
|
domain TEXT NOT NULL,
|
|
port INTEGER NOT NULL,
|
|
ssl_enabled INTEGER NOT NULL DEFAULT 0,
|
|
config_template TEXT NOT NULL,
|
|
created_at REAL NOT NULL,
|
|
updated_at REAL NOT NULL,
|
|
FOREIGN KEY (service_id) REFERENCES services(id) ON DELETE CASCADE
|
|
)
|
|
`);
|
|
this.query(`INSERT INTO nginx_configs_new SELECT * FROM nginx_configs`);
|
|
this.query(`DROP TABLE nginx_configs`);
|
|
this.query(`ALTER TABLE nginx_configs_new RENAME TO nginx_configs`);
|
|
|
|
// DNS records
|
|
this.query(`
|
|
CREATE TABLE dns_records_new (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
domain TEXT NOT NULL UNIQUE,
|
|
type TEXT NOT NULL,
|
|
value TEXT NOT NULL,
|
|
cloudflare_id TEXT,
|
|
zone_id TEXT,
|
|
created_at REAL NOT NULL,
|
|
updated_at REAL NOT NULL
|
|
)
|
|
`);
|
|
this.query(`INSERT INTO dns_records_new SELECT * FROM dns_records`);
|
|
this.query(`DROP TABLE dns_records`);
|
|
this.query(`ALTER TABLE dns_records_new RENAME TO dns_records`);
|
|
|
|
// Metrics
|
|
this.query(`
|
|
CREATE TABLE metrics_new (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
service_id INTEGER NOT NULL,
|
|
timestamp REAL NOT NULL,
|
|
cpu_percent REAL NOT NULL,
|
|
memory_used INTEGER NOT NULL,
|
|
memory_limit INTEGER NOT NULL,
|
|
network_rx_bytes INTEGER NOT NULL,
|
|
network_tx_bytes INTEGER NOT NULL,
|
|
FOREIGN KEY (service_id) REFERENCES services(id) ON DELETE CASCADE
|
|
)
|
|
`);
|
|
this.query(`INSERT INTO metrics_new SELECT * FROM metrics`);
|
|
this.query(`DROP TABLE metrics`);
|
|
this.query(`ALTER TABLE metrics_new RENAME TO metrics`);
|
|
this.query(`CREATE INDEX IF NOT EXISTS idx_metrics_service_timestamp ON metrics(service_id, timestamp DESC)`);
|
|
|
|
// Logs
|
|
this.query(`
|
|
CREATE TABLE logs_new (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
service_id INTEGER NOT NULL,
|
|
timestamp REAL NOT NULL,
|
|
message TEXT NOT NULL,
|
|
level TEXT NOT NULL,
|
|
source TEXT NOT NULL,
|
|
FOREIGN KEY (service_id) REFERENCES services(id) ON DELETE CASCADE
|
|
)
|
|
`);
|
|
this.query(`INSERT INTO logs_new SELECT * FROM logs`);
|
|
this.query(`DROP TABLE logs`);
|
|
this.query(`ALTER TABLE logs_new RENAME TO logs`);
|
|
this.query(`CREATE INDEX IF NOT EXISTS idx_logs_service_timestamp ON logs(service_id, timestamp DESC)`);
|
|
|
|
// Users
|
|
this.query(`
|
|
CREATE TABLE users_new (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
username TEXT NOT NULL UNIQUE,
|
|
password_hash TEXT NOT NULL,
|
|
role TEXT NOT NULL DEFAULT 'user',
|
|
created_at REAL NOT NULL,
|
|
updated_at REAL NOT NULL
|
|
)
|
|
`);
|
|
this.query(`INSERT INTO users_new SELECT * FROM users`);
|
|
this.query(`DROP TABLE users`);
|
|
this.query(`ALTER TABLE users_new RENAME TO users`);
|
|
|
|
// Settings
|
|
this.query(`
|
|
CREATE TABLE settings_new (
|
|
key TEXT PRIMARY KEY,
|
|
value TEXT NOT NULL,
|
|
updated_at REAL NOT NULL
|
|
)
|
|
`);
|
|
this.query(`INSERT INTO settings_new SELECT * FROM settings`);
|
|
this.query(`DROP TABLE settings`);
|
|
this.query(`ALTER TABLE settings_new RENAME TO settings`);
|
|
|
|
// Migrations table itself
|
|
this.query(`
|
|
CREATE TABLE migrations_new (
|
|
version INTEGER PRIMARY KEY,
|
|
applied_at REAL NOT NULL
|
|
)
|
|
`);
|
|
this.query(`INSERT INTO migrations_new SELECT * FROM migrations`);
|
|
this.query(`DROP TABLE migrations`);
|
|
this.query(`ALTER TABLE migrations_new RENAME TO migrations`);
|
|
|
|
this.setMigrationVersion(2);
|
|
logger.success('Migration 2 completed: All timestamps converted to REAL');
|
|
}
|
|
|
|
// Migration 3: Domain management tables
|
|
const version3 = this.getMigrationVersion();
|
|
if (version3 < 3) {
|
|
logger.info('Running migration 3: Creating domain management tables...');
|
|
|
|
this.query(`
|
|
CREATE TABLE domains (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
domain TEXT NOT NULL UNIQUE,
|
|
dns_provider TEXT,
|
|
cloudflare_zone_id TEXT,
|
|
is_obsolete INTEGER NOT NULL DEFAULT 0,
|
|
default_wildcard INTEGER NOT NULL DEFAULT 1,
|
|
created_at REAL NOT NULL,
|
|
updated_at REAL NOT NULL
|
|
)
|
|
`);
|
|
|
|
this.query(`
|
|
CREATE TABLE certificates (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
domain_id INTEGER NOT NULL,
|
|
cert_domain TEXT NOT NULL,
|
|
is_wildcard INTEGER NOT NULL DEFAULT 0,
|
|
cert_path TEXT NOT NULL,
|
|
key_path TEXT NOT NULL,
|
|
full_chain_path TEXT NOT NULL,
|
|
expiry_date REAL NOT NULL,
|
|
issuer TEXT NOT NULL,
|
|
is_valid INTEGER NOT NULL DEFAULT 1,
|
|
created_at REAL NOT NULL,
|
|
updated_at REAL NOT NULL,
|
|
FOREIGN KEY (domain_id) REFERENCES domains(id) ON DELETE CASCADE
|
|
)
|
|
`);
|
|
|
|
this.query(`
|
|
CREATE TABLE cert_requirements (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
service_id INTEGER NOT NULL,
|
|
domain_id INTEGER NOT NULL,
|
|
subdomain TEXT NOT NULL,
|
|
certificate_id INTEGER,
|
|
status TEXT NOT NULL DEFAULT 'pending',
|
|
created_at REAL NOT NULL,
|
|
updated_at REAL NOT NULL,
|
|
FOREIGN KEY (service_id) REFERENCES services(id) ON DELETE CASCADE,
|
|
FOREIGN KEY (domain_id) REFERENCES domains(id) ON DELETE CASCADE,
|
|
FOREIGN KEY (certificate_id) REFERENCES certificates(id) ON DELETE SET NULL
|
|
)
|
|
`);
|
|
|
|
interface OldSslCert {
|
|
id?: number;
|
|
domain?: string;
|
|
cert_path?: string;
|
|
key_path?: string;
|
|
full_chain_path?: string;
|
|
expiry_date?: number;
|
|
issuer?: string;
|
|
created_at?: number;
|
|
updated_at?: number;
|
|
[key: number]: unknown;
|
|
}
|
|
const existingCerts = this.query<OldSslCert>('SELECT * FROM ssl_certificates');
|
|
|
|
const now = Date.now();
|
|
const domainMap = new Map<string, number>();
|
|
|
|
for (const cert of existingCerts) {
|
|
const domain = String(cert.domain ?? (cert as Record<number, unknown>)[1]);
|
|
if (!domainMap.has(domain)) {
|
|
this.query(
|
|
'INSERT INTO domains (domain, dns_provider, is_obsolete, default_wildcard, created_at, updated_at) VALUES (?, ?, ?, ?, ?, ?)',
|
|
[domain, null, 0, 1, now, now]
|
|
);
|
|
const result = this.query<{ id?: number; [key: number]: unknown }>('SELECT last_insert_rowid() as id');
|
|
const domainId = result[0].id ?? (result[0] as Record<number, unknown>)[0];
|
|
domainMap.set(domain, Number(domainId));
|
|
}
|
|
}
|
|
|
|
for (const cert of existingCerts) {
|
|
const domain = String(cert.domain ?? (cert as Record<number, unknown>)[1]);
|
|
const domainId = domainMap.get(domain);
|
|
|
|
this.query(
|
|
`INSERT INTO certificates (
|
|
domain_id, cert_domain, is_wildcard, cert_path, key_path, full_chain_path,
|
|
expiry_date, issuer, is_valid, created_at, updated_at
|
|
) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
|
|
[
|
|
domainId,
|
|
domain,
|
|
0,
|
|
String(cert.cert_path ?? (cert as Record<number, unknown>)[2]),
|
|
String(cert.key_path ?? (cert as Record<number, unknown>)[3]),
|
|
String(cert.full_chain_path ?? (cert as Record<number, unknown>)[4]),
|
|
Number(cert.expiry_date ?? (cert as Record<number, unknown>)[5]),
|
|
String(cert.issuer ?? (cert as Record<number, unknown>)[6]),
|
|
1,
|
|
Number(cert.created_at ?? (cert as Record<number, unknown>)[7]),
|
|
Number(cert.updated_at ?? (cert as Record<number, unknown>)[8])
|
|
]
|
|
);
|
|
}
|
|
|
|
this.query('DROP TABLE ssl_certificates');
|
|
this.query('CREATE INDEX IF NOT EXISTS idx_domains_cloudflare_zone ON domains(cloudflare_zone_id)');
|
|
this.query('CREATE INDEX IF NOT EXISTS idx_certificates_domain ON certificates(domain_id)');
|
|
this.query('CREATE INDEX IF NOT EXISTS idx_certificates_expiry ON certificates(expiry_date)');
|
|
this.query('CREATE INDEX IF NOT EXISTS idx_cert_requirements_service ON cert_requirements(service_id)');
|
|
this.query('CREATE INDEX IF NOT EXISTS idx_cert_requirements_domain ON cert_requirements(domain_id)');
|
|
|
|
this.setMigrationVersion(3);
|
|
logger.success('Migration 3 completed: Domain management tables created');
|
|
}
|
|
|
|
// Migration 4: Add Onebox Registry support columns
|
|
const version4 = this.getMigrationVersion();
|
|
if (version4 < 4) {
|
|
logger.info('Running migration 4: Adding Onebox Registry columns to services table...');
|
|
|
|
this.query(`ALTER TABLE services ADD COLUMN use_onebox_registry INTEGER DEFAULT 0`);
|
|
this.query(`ALTER TABLE services ADD COLUMN registry_repository TEXT`);
|
|
this.query(`ALTER TABLE services ADD COLUMN registry_token TEXT`);
|
|
this.query(`ALTER TABLE services ADD COLUMN registry_image_tag TEXT DEFAULT 'latest'`);
|
|
this.query(`ALTER TABLE services ADD COLUMN auto_update_on_push INTEGER DEFAULT 0`);
|
|
this.query(`ALTER TABLE services ADD COLUMN image_digest TEXT`);
|
|
|
|
this.setMigrationVersion(4);
|
|
logger.success('Migration 4 completed: Onebox Registry columns added to services table');
|
|
}
|
|
|
|
// Migration 5: Registry tokens table
|
|
const version5 = this.getMigrationVersion();
|
|
if (version5 < 5) {
|
|
logger.info('Running migration 5: Creating registry_tokens table...');
|
|
|
|
this.query(`
|
|
CREATE TABLE registry_tokens (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
name TEXT NOT NULL,
|
|
token_hash TEXT NOT NULL UNIQUE,
|
|
token_type TEXT NOT NULL,
|
|
scope TEXT NOT NULL,
|
|
expires_at REAL,
|
|
created_at REAL NOT NULL,
|
|
last_used_at REAL,
|
|
created_by TEXT NOT NULL
|
|
)
|
|
`);
|
|
|
|
this.query('CREATE INDEX IF NOT EXISTS idx_registry_tokens_type ON registry_tokens(token_type)');
|
|
this.query('CREATE INDEX IF NOT EXISTS idx_registry_tokens_hash ON registry_tokens(token_hash)');
|
|
|
|
this.setMigrationVersion(5);
|
|
logger.success('Migration 5 completed: Registry tokens table created');
|
|
}
|
|
|
|
// Migration 6: Drop registry_token column from services table
|
|
const version6 = this.getMigrationVersion();
|
|
if (version6 < 6) {
|
|
logger.info('Running migration 6: Dropping registry_token column from services table...');
|
|
|
|
this.query(`
|
|
CREATE TABLE services_new (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
name TEXT NOT NULL UNIQUE,
|
|
image TEXT NOT NULL,
|
|
registry TEXT,
|
|
env_vars TEXT,
|
|
port INTEGER NOT NULL,
|
|
domain TEXT,
|
|
container_id TEXT,
|
|
status TEXT NOT NULL,
|
|
created_at REAL NOT NULL,
|
|
updated_at REAL NOT NULL,
|
|
use_onebox_registry INTEGER DEFAULT 0,
|
|
registry_repository TEXT,
|
|
registry_image_tag TEXT DEFAULT 'latest',
|
|
auto_update_on_push INTEGER DEFAULT 0,
|
|
image_digest TEXT
|
|
)
|
|
`);
|
|
|
|
this.query(`
|
|
INSERT INTO services_new (
|
|
id, name, image, registry, env_vars, port, domain, container_id, status,
|
|
created_at, updated_at, use_onebox_registry, registry_repository,
|
|
registry_image_tag, auto_update_on_push, image_digest
|
|
)
|
|
SELECT
|
|
id, name, image, registry, env_vars, port, domain, container_id, status,
|
|
created_at, updated_at, use_onebox_registry, registry_repository,
|
|
registry_image_tag, auto_update_on_push, image_digest
|
|
FROM services
|
|
`);
|
|
|
|
this.query('DROP TABLE services');
|
|
this.query('ALTER TABLE services_new RENAME TO services');
|
|
this.query('CREATE INDEX IF NOT EXISTS idx_services_name ON services(name)');
|
|
this.query('CREATE INDEX IF NOT EXISTS idx_services_status ON services(status)');
|
|
|
|
this.setMigrationVersion(6);
|
|
logger.success('Migration 6 completed: registry_token column dropped from services table');
|
|
}
|
|
|
|
// Migration 7: Platform services tables
|
|
const version7 = this.getMigrationVersion();
|
|
if (version7 < 7) {
|
|
logger.info('Running migration 7: Creating platform services tables...');
|
|
|
|
this.query(`
|
|
CREATE TABLE platform_services (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
name TEXT NOT NULL UNIQUE,
|
|
type TEXT NOT NULL,
|
|
status TEXT NOT NULL DEFAULT 'stopped',
|
|
container_id TEXT,
|
|
config TEXT NOT NULL DEFAULT '{}',
|
|
admin_credentials_encrypted TEXT,
|
|
created_at REAL NOT NULL,
|
|
updated_at REAL NOT NULL
|
|
)
|
|
`);
|
|
|
|
this.query(`
|
|
CREATE TABLE platform_resources (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
platform_service_id INTEGER NOT NULL,
|
|
service_id INTEGER NOT NULL,
|
|
resource_type TEXT NOT NULL,
|
|
resource_name TEXT NOT NULL,
|
|
credentials_encrypted TEXT NOT NULL,
|
|
created_at REAL NOT NULL,
|
|
FOREIGN KEY (platform_service_id) REFERENCES platform_services(id) ON DELETE CASCADE,
|
|
FOREIGN KEY (service_id) REFERENCES services(id) ON DELETE CASCADE
|
|
)
|
|
`);
|
|
|
|
this.query(`ALTER TABLE services ADD COLUMN platform_requirements TEXT DEFAULT '{}'`);
|
|
|
|
this.query('CREATE INDEX IF NOT EXISTS idx_platform_services_type ON platform_services(type)');
|
|
this.query('CREATE INDEX IF NOT EXISTS idx_platform_resources_service ON platform_resources(service_id)');
|
|
this.query('CREATE INDEX IF NOT EXISTS idx_platform_resources_platform ON platform_resources(platform_service_id)');
|
|
|
|
this.setMigrationVersion(7);
|
|
logger.success('Migration 7 completed: Platform services tables created');
|
|
}
|
|
|
|
// Migration 8: Convert certificates table to store PEM content
|
|
const version8 = this.getMigrationVersion();
|
|
if (version8 < 8) {
|
|
logger.info('Running migration 8: Converting certificates table to store PEM content...');
|
|
|
|
this.query(`
|
|
CREATE TABLE certificates_new (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
domain_id INTEGER NOT NULL,
|
|
cert_domain TEXT NOT NULL,
|
|
is_wildcard INTEGER NOT NULL DEFAULT 0,
|
|
cert_pem TEXT NOT NULL DEFAULT '',
|
|
key_pem TEXT NOT NULL DEFAULT '',
|
|
fullchain_pem TEXT NOT NULL DEFAULT '',
|
|
expiry_date REAL NOT NULL,
|
|
issuer TEXT NOT NULL,
|
|
is_valid INTEGER NOT NULL DEFAULT 1,
|
|
created_at REAL NOT NULL,
|
|
updated_at REAL NOT NULL,
|
|
FOREIGN KEY (domain_id) REFERENCES domains(id) ON DELETE CASCADE
|
|
)
|
|
`);
|
|
|
|
this.query(`
|
|
INSERT INTO certificates_new (id, domain_id, cert_domain, is_wildcard, cert_pem, key_pem, fullchain_pem, expiry_date, issuer, is_valid, created_at, updated_at)
|
|
SELECT id, domain_id, cert_domain, is_wildcard, '', '', '', expiry_date, issuer, 0, created_at, updated_at FROM certificates
|
|
`);
|
|
|
|
this.query('DROP TABLE certificates');
|
|
this.query('ALTER TABLE certificates_new RENAME TO certificates');
|
|
this.query('CREATE INDEX IF NOT EXISTS idx_certificates_domain ON certificates(domain_id)');
|
|
this.query('CREATE INDEX IF NOT EXISTS idx_certificates_expiry ON certificates(expiry_date)');
|
|
|
|
this.setMigrationVersion(8);
|
|
logger.success('Migration 8 completed: Certificates table now stores PEM content');
|
|
}
|
|
} catch (error) {
|
|
logger.error(`Migration failed: ${getErrorMessage(error)}`);
|
|
if (error instanceof Error && error.stack) {
|
|
logger.error(`Stack: ${error.stack}`);
|
|
}
|
|
throw error;
|
|
}
|
|
}
|
|
|
|
/**
|
|
* Get current migration version
|
|
*/
|
|
private getMigrationVersion(): number {
|
|
if (!this.db) throw new Error('Database not initialized');
|
|
|
|
try {
|
|
const result = this.query<{ version?: number | null; [key: number]: unknown }>('SELECT MAX(version) as version FROM migrations');
|
|
if (result.length === 0) return 0;
|
|
|
|
const versionValue = result[0].version ?? (result[0] as Record<number, unknown>)[0];
|
|
return versionValue !== null && versionValue !== undefined ? Number(versionValue) : 0;
|
|
} catch (error) {
|
|
logger.warn(`Error getting migration version: ${getErrorMessage(error)}, defaulting to 0`);
|
|
return 0;
|
|
}
|
|
}
|
|
|
|
/**
|
|
* Set migration version
|
|
*/
|
|
private setMigrationVersion(version: number): void {
|
|
if (!this.db) throw new Error('Database not initialized');
|
|
|
|
this.query('INSERT INTO migrations (version, applied_at) VALUES (?, ?)', [
|
|
version,
|
|
Date.now(),
|
|
]);
|
|
logger.debug(`Migration version set to ${version}`);
|
|
}
|
|
|
|
/**
|
|
* Close database connection
|
|
*/
|
|
close(): void {
|
|
if (this.db) {
|
|
this.db.close();
|
|
this.db = null;
|
|
logger.debug('Database connection closed');
|
|
}
|
|
}
|
|
|
|
/**
|
|
* Execute a raw query
|
|
*/
|
|
query<T = Record<string, unknown>>(sql: string, params: TBindValue[] = []): T[] {
|
|
if (!this.db) {
|
|
const error = new Error('Database not initialized');
|
|
console.error('Database access before initialization!');
|
|
console.error('Stack trace:', error.stack);
|
|
throw error;
|
|
}
|
|
|
|
if (params.length === 0 && !sql.trim().toUpperCase().startsWith('SELECT')) {
|
|
this.db.exec(sql);
|
|
return [] as T[];
|
|
}
|
|
|
|
const stmt = this.db.prepare(sql);
|
|
try {
|
|
const result = stmt.all(...params);
|
|
return result as T[];
|
|
} finally {
|
|
stmt.finalize();
|
|
}
|
|
}
|
|
|
|
// ============ Services CRUD (delegated to repository) ============
|
|
|
|
async createService(service: Omit<IService, 'id'>): Promise<IService> {
|
|
return this.serviceRepo.create(service);
|
|
}
|
|
|
|
getServiceByName(name: string): IService | null {
|
|
return this.serviceRepo.getByName(name);
|
|
}
|
|
|
|
getServiceByID(id: number): IService | null {
|
|
return this.serviceRepo.getById(id);
|
|
}
|
|
|
|
getAllServices(): IService[] {
|
|
return this.serviceRepo.getAll();
|
|
}
|
|
|
|
updateService(id: number, updates: Partial<IService>): void {
|
|
this.serviceRepo.update(id, updates);
|
|
}
|
|
|
|
deleteService(id: number): void {
|
|
this.serviceRepo.delete(id);
|
|
}
|
|
|
|
// ============ Registries CRUD (delegated to repository) ============
|
|
|
|
async createRegistry(registry: Omit<IRegistry, 'id'>): Promise<IRegistry> {
|
|
return this.registryRepo.createRegistry(registry);
|
|
}
|
|
|
|
getRegistryByURL(url: string): IRegistry | null {
|
|
return this.registryRepo.getRegistryByURL(url);
|
|
}
|
|
|
|
getAllRegistries(): IRegistry[] {
|
|
return this.registryRepo.getAllRegistries();
|
|
}
|
|
|
|
deleteRegistry(url: string): void {
|
|
this.registryRepo.deleteRegistry(url);
|
|
}
|
|
|
|
// ============ Settings CRUD (delegated to repository) ============
|
|
|
|
getSetting(key: string): string | null {
|
|
return this.authRepo.getSetting(key);
|
|
}
|
|
|
|
setSetting(key: string, value: string): void {
|
|
this.authRepo.setSetting(key, value);
|
|
}
|
|
|
|
getAllSettings(): Record<string, string> {
|
|
return this.authRepo.getAllSettings();
|
|
}
|
|
|
|
// ============ Users CRUD (delegated to repository) ============
|
|
|
|
async createUser(user: Omit<IUser, 'id'>): Promise<IUser> {
|
|
return this.authRepo.createUser(user);
|
|
}
|
|
|
|
getUserByUsername(username: string): IUser | null {
|
|
return this.authRepo.getUserByUsername(username);
|
|
}
|
|
|
|
getAllUsers(): IUser[] {
|
|
return this.authRepo.getAllUsers();
|
|
}
|
|
|
|
updateUserPassword(username: string, passwordHash: string): void {
|
|
this.authRepo.updateUserPassword(username, passwordHash);
|
|
}
|
|
|
|
deleteUser(username: string): void {
|
|
this.authRepo.deleteUser(username);
|
|
}
|
|
|
|
// ============ Metrics (delegated to repository) ============
|
|
|
|
addMetric(metric: Omit<IMetric, 'id'>): void {
|
|
this.metricsRepo.addMetric(metric);
|
|
}
|
|
|
|
getMetrics(serviceId: number, limit = 100): IMetric[] {
|
|
return this.metricsRepo.getMetrics(serviceId, limit);
|
|
}
|
|
|
|
// ============ Logs (delegated to repository) ============
|
|
|
|
addLog(log: Omit<ILogEntry, 'id'>): void {
|
|
this.metricsRepo.addLog(log);
|
|
}
|
|
|
|
getLogs(serviceId: number, limit = 1000): ILogEntry[] {
|
|
return this.metricsRepo.getLogs(serviceId, limit);
|
|
}
|
|
|
|
// ============ SSL Certificates Legacy API (delegated to repository) ============
|
|
|
|
async createSSLCertificate(cert: Omit<ISslCertificate, 'id'>): Promise<ISslCertificate> {
|
|
return this.certificateRepo.createSSLCertificate(cert);
|
|
}
|
|
|
|
getSSLCertificate(domain: string): ISslCertificate | null {
|
|
return this.certificateRepo.getSSLCertificate(domain);
|
|
}
|
|
|
|
getAllSSLCertificates(): ISslCertificate[] {
|
|
return this.certificateRepo.getAllSSLCertificates();
|
|
}
|
|
|
|
updateSSLCertificate(domain: string, updates: Partial<ISslCertificate>): void {
|
|
this.certificateRepo.updateSSLCertificate(domain, updates);
|
|
}
|
|
|
|
deleteSSLCertificate(domain: string): void {
|
|
this.certificateRepo.deleteSSLCertificate(domain);
|
|
}
|
|
|
|
// ============ Domains (delegated to repository) ============
|
|
|
|
createDomain(domain: Omit<IDomain, 'id'>): IDomain {
|
|
return this.certificateRepo.createDomain(domain);
|
|
}
|
|
|
|
getDomainByName(domain: string): IDomain | null {
|
|
return this.certificateRepo.getDomainByName(domain);
|
|
}
|
|
|
|
getDomainById(id: number): IDomain | null {
|
|
return this.certificateRepo.getDomainById(id);
|
|
}
|
|
|
|
getAllDomains(): IDomain[] {
|
|
return this.certificateRepo.getAllDomains();
|
|
}
|
|
|
|
getDomainsByProvider(provider: 'cloudflare' | 'manual'): IDomain[] {
|
|
return this.certificateRepo.getDomainsByProvider(provider);
|
|
}
|
|
|
|
updateDomain(id: number, updates: Partial<IDomain>): void {
|
|
this.certificateRepo.updateDomain(id, updates);
|
|
}
|
|
|
|
deleteDomain(id: number): void {
|
|
this.certificateRepo.deleteDomain(id);
|
|
}
|
|
|
|
// ============ Certificates (delegated to repository) ============
|
|
|
|
createCertificate(cert: Omit<ICertificate, 'id'>): ICertificate {
|
|
return this.certificateRepo.createCertificate(cert);
|
|
}
|
|
|
|
getCertificateById(id: number): ICertificate | null {
|
|
return this.certificateRepo.getCertificateById(id);
|
|
}
|
|
|
|
getCertificatesByDomain(domainId: number): ICertificate[] {
|
|
return this.certificateRepo.getCertificatesByDomain(domainId);
|
|
}
|
|
|
|
getAllCertificates(): ICertificate[] {
|
|
return this.certificateRepo.getAllCertificates();
|
|
}
|
|
|
|
updateCertificate(id: number, updates: Partial<ICertificate>): void {
|
|
this.certificateRepo.updateCertificate(id, updates);
|
|
}
|
|
|
|
deleteCertificate(id: number): void {
|
|
this.certificateRepo.deleteCertificate(id);
|
|
}
|
|
|
|
// ============ Certificate Requirements (delegated to repository) ============
|
|
|
|
createCertRequirement(req: Omit<ICertRequirement, 'id'>): ICertRequirement {
|
|
return this.certificateRepo.createCertRequirement(req);
|
|
}
|
|
|
|
getCertRequirementById(id: number): ICertRequirement | null {
|
|
return this.certificateRepo.getCertRequirementById(id);
|
|
}
|
|
|
|
getCertRequirementsByService(serviceId: number): ICertRequirement[] {
|
|
return this.certificateRepo.getCertRequirementsByService(serviceId);
|
|
}
|
|
|
|
getCertRequirementsByDomain(domainId: number): ICertRequirement[] {
|
|
return this.certificateRepo.getCertRequirementsByDomain(domainId);
|
|
}
|
|
|
|
getAllCertRequirements(): ICertRequirement[] {
|
|
return this.certificateRepo.getAllCertRequirements();
|
|
}
|
|
|
|
updateCertRequirement(id: number, updates: Partial<ICertRequirement>): void {
|
|
this.certificateRepo.updateCertRequirement(id, updates);
|
|
}
|
|
|
|
deleteCertRequirement(id: number): void {
|
|
this.certificateRepo.deleteCertRequirement(id);
|
|
}
|
|
|
|
// ============ Registry Tokens (delegated to repository) ============
|
|
|
|
createRegistryToken(token: Omit<IRegistryToken, 'id'>): IRegistryToken {
|
|
return this.registryRepo.createToken(token);
|
|
}
|
|
|
|
getRegistryTokenById(id: number): IRegistryToken | null {
|
|
return this.registryRepo.getTokenById(id);
|
|
}
|
|
|
|
getRegistryTokenByHash(tokenHash: string): IRegistryToken | null {
|
|
return this.registryRepo.getTokenByHash(tokenHash);
|
|
}
|
|
|
|
getAllRegistryTokens(): IRegistryToken[] {
|
|
return this.registryRepo.getAllTokens();
|
|
}
|
|
|
|
getRegistryTokensByType(type: 'global' | 'ci'): IRegistryToken[] {
|
|
return this.registryRepo.getTokensByType(type);
|
|
}
|
|
|
|
updateRegistryTokenLastUsed(id: number): void {
|
|
this.registryRepo.updateTokenLastUsed(id);
|
|
}
|
|
|
|
deleteRegistryToken(id: number): void {
|
|
this.registryRepo.deleteToken(id);
|
|
}
|
|
|
|
// ============ Platform Services (delegated to repository) ============
|
|
|
|
createPlatformService(service: Omit<IPlatformService, 'id'>): IPlatformService {
|
|
return this.platformRepo.createPlatformService(service);
|
|
}
|
|
|
|
getPlatformServiceByName(name: string): IPlatformService | null {
|
|
return this.platformRepo.getPlatformServiceByName(name);
|
|
}
|
|
|
|
getPlatformServiceById(id: number): IPlatformService | null {
|
|
return this.platformRepo.getPlatformServiceById(id);
|
|
}
|
|
|
|
getPlatformServiceByType(type: TPlatformServiceType): IPlatformService | null {
|
|
return this.platformRepo.getPlatformServiceByType(type);
|
|
}
|
|
|
|
getAllPlatformServices(): IPlatformService[] {
|
|
return this.platformRepo.getAllPlatformServices();
|
|
}
|
|
|
|
updatePlatformService(id: number, updates: Partial<IPlatformService>): void {
|
|
this.platformRepo.updatePlatformService(id, updates);
|
|
}
|
|
|
|
deletePlatformService(id: number): void {
|
|
this.platformRepo.deletePlatformService(id);
|
|
}
|
|
|
|
// ============ Platform Resources (delegated to repository) ============
|
|
|
|
createPlatformResource(resource: Omit<IPlatformResource, 'id'>): IPlatformResource {
|
|
return this.platformRepo.createPlatformResource(resource);
|
|
}
|
|
|
|
getPlatformResourceById(id: number): IPlatformResource | null {
|
|
return this.platformRepo.getPlatformResourceById(id);
|
|
}
|
|
|
|
getPlatformResourcesByService(serviceId: number): IPlatformResource[] {
|
|
return this.platformRepo.getPlatformResourcesByService(serviceId);
|
|
}
|
|
|
|
getPlatformResourcesByPlatformService(platformServiceId: number): IPlatformResource[] {
|
|
return this.platformRepo.getPlatformResourcesByPlatformService(platformServiceId);
|
|
}
|
|
|
|
getAllPlatformResources(): IPlatformResource[] {
|
|
return this.platformRepo.getAllPlatformResources();
|
|
}
|
|
|
|
deletePlatformResource(id: number): void {
|
|
this.platformRepo.deletePlatformResource(id);
|
|
}
|
|
|
|
deletePlatformResourcesByService(serviceId: number): void {
|
|
this.platformRepo.deletePlatformResourcesByService(serviceId);
|
|
}
|
|
}
|