232 lines
9.5 KiB
PL/PgSQL
232 lines
9.5 KiB
PL/PgSQL
-- Migration: 000001_initial_schema
|
|
-- Description: Create initial Extrudex schema — lookup tables, core entities, and settings
|
|
-- Author: Hex
|
|
-- Date: 2026-05-06
|
|
--
|
|
-- Design decisions:
|
|
-- - Lookup tables for material_base, material_finish, material_modifier (no free-text enums)
|
|
-- - Lookup tables for printer_type and job_status (extensible, no hard-coded enum values)
|
|
-- - FK ON DELETE: RESTRICT on critical parents (material_base, material_finish, printer),
|
|
-- SET NULL on optional parents (modifier, spool on print_jobs),
|
|
-- CASCADE for usage_logs when parent job is deleted
|
|
-- - Soft-delete (deleted_at) on spools and print_jobs for safety
|
|
-- - JSONB config column on settings for flexible app-wide configuration
|
|
-- - All identifiers snake_case per project convention
|
|
|
|
BEGIN;
|
|
|
|
-- ============================================================================
|
|
-- Lookup Tables
|
|
-- ============================================================================
|
|
|
|
-- Printer types (fdm, resin, etc.) — extensible, not a raw enum
|
|
CREATE TABLE printer_types (
|
|
id SERIAL PRIMARY KEY,
|
|
name VARCHAR(50) NOT NULL UNIQUE,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
|
);
|
|
|
|
-- Job statuses (pending, printing, paused, completed, failed, cancelled)
|
|
CREATE TABLE job_statuses (
|
|
id SERIAL PRIMARY KEY,
|
|
name VARCHAR(50) NOT NULL UNIQUE,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
|
);
|
|
|
|
-- Material base types (PLA, PETG, ABS, TPU, ASA, Nylon, PC)
|
|
CREATE TABLE material_bases (
|
|
id SERIAL PRIMARY KEY,
|
|
name VARCHAR(100) NOT NULL UNIQUE,
|
|
density_g_cm3 DECIMAL(5,3) NOT NULL,
|
|
extrusion_temp_min INT,
|
|
extrusion_temp_max INT,
|
|
bed_temp_min INT,
|
|
bed_temp_max INT,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
|
);
|
|
|
|
-- Material finishes (Basic, Silk, Matte, Glossy, Satin)
|
|
CREATE TABLE material_finishes (
|
|
id SERIAL PRIMARY KEY,
|
|
name VARCHAR(100) NOT NULL UNIQUE,
|
|
description TEXT,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
|
);
|
|
|
|
-- Material modifiers (Wood-Filled, Carbon Fiber, Glow-in-Dark, Marble)
|
|
CREATE TABLE material_modifiers (
|
|
id SERIAL PRIMARY KEY,
|
|
name VARCHAR(100) NOT NULL UNIQUE,
|
|
description TEXT,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
|
);
|
|
|
|
-- ============================================================================
|
|
-- Core Entity Tables
|
|
-- ============================================================================
|
|
|
|
-- 3D printers in the fleet
|
|
CREATE TABLE printers (
|
|
id SERIAL PRIMARY KEY,
|
|
name VARCHAR(255) NOT NULL,
|
|
printer_type_id INT NOT NULL,
|
|
manufacturer VARCHAR(255),
|
|
model VARCHAR(255),
|
|
moonraker_url VARCHAR(512),
|
|
moonraker_api_key VARCHAR(512),
|
|
mqtt_broker_host VARCHAR(255),
|
|
mqtt_topic_prefix VARCHAR(255),
|
|
mqtt_tls_enabled BOOLEAN NOT NULL DEFAULT FALSE,
|
|
is_active BOOLEAN NOT NULL DEFAULT TRUE,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
|
|
CONSTRAINT fk_printers_printer_type
|
|
FOREIGN KEY (printer_type_id) REFERENCES printer_types(id)
|
|
ON DELETE RESTRICT
|
|
);
|
|
|
|
-- Filament spools — the core inventory item
|
|
CREATE TABLE filament_spools (
|
|
id SERIAL PRIMARY KEY,
|
|
name VARCHAR(255) NOT NULL,
|
|
material_base_id INT NOT NULL,
|
|
material_finish_id INT NOT NULL DEFAULT 1, -- "Basic" (seed data populates this first)
|
|
material_modifier_id INT,
|
|
color_hex VARCHAR(7) NOT NULL CHECK (color_hex ~ '^#[0-9A-Fa-f]{6}$'),
|
|
brand VARCHAR(255),
|
|
diameter_mm DECIMAL(4,2) NOT NULL DEFAULT 1.75,
|
|
initial_grams INT NOT NULL CHECK (initial_grams > 0),
|
|
remaining_grams INT NOT NULL CHECK (remaining_grams >= 0),
|
|
spool_weight_grams INT, -- measured empty-spool weight (tare), nullable
|
|
cost_usd DECIMAL(10,2),
|
|
low_stock_threshold_grams INT NOT NULL DEFAULT 50,
|
|
notes TEXT,
|
|
barcode VARCHAR(255) UNIQUE,
|
|
deleted_at TIMESTAMPTZ,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
|
|
CONSTRAINT fk_spools_material_base
|
|
FOREIGN KEY (material_base_id) REFERENCES material_bases(id)
|
|
ON DELETE RESTRICT,
|
|
|
|
CONSTRAINT fk_spools_material_finish
|
|
FOREIGN KEY (material_finish_id) REFERENCES material_finishes(id)
|
|
ON DELETE RESTRICT,
|
|
|
|
CONSTRAINT fk_spools_material_modifier
|
|
FOREIGN KEY (material_modifier_id) REFERENCES material_modifiers(id)
|
|
ON DELETE SET NULL
|
|
);
|
|
|
|
-- Print jobs — each job is one print on one printer
|
|
CREATE TABLE print_jobs (
|
|
id SERIAL PRIMARY KEY,
|
|
printer_id INT NOT NULL,
|
|
filament_spool_id INT, -- nullable: a job may use multiple spools (captured in usage_logs)
|
|
job_name VARCHAR(255) NOT NULL,
|
|
file_name VARCHAR(512),
|
|
job_status_id INT NOT NULL DEFAULT 1, -- "pending"
|
|
started_at TIMESTAMPTZ,
|
|
completed_at TIMESTAMPTZ,
|
|
duration_seconds INT,
|
|
estimated_duration_seconds INT,
|
|
total_mm_extruded DECIMAL(12,2),
|
|
total_grams_used DECIMAL(10,2),
|
|
total_cost_usd DECIMAL(10,4),
|
|
notes TEXT,
|
|
deleted_at TIMESTAMPTZ,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
|
|
CONSTRAINT fk_print_jobs_printer
|
|
FOREIGN KEY (printer_id) REFERENCES printers(id)
|
|
ON DELETE RESTRICT,
|
|
|
|
CONSTRAINT fk_print_jobs_spool
|
|
FOREIGN KEY (filament_spool_id) REFERENCES filament_spools(id)
|
|
ON DELETE SET NULL,
|
|
|
|
CONSTRAINT fk_print_jobs_status
|
|
FOREIGN KEY (job_status_id) REFERENCES job_statuses(id)
|
|
ON DELETE RESTRICT
|
|
);
|
|
|
|
-- Usage logs — granular tracking of filament consumed per job, per spool
|
|
CREATE TABLE usage_logs (
|
|
id SERIAL PRIMARY KEY,
|
|
print_job_id INT NOT NULL,
|
|
filament_spool_id INT NOT NULL,
|
|
mm_extruded DECIMAL(12,2) NOT NULL CHECK (mm_extruded > 0),
|
|
grams_used DECIMAL(10,2) NOT NULL CHECK (grams_used > 0),
|
|
cost_usd DECIMAL(10,4),
|
|
logged_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
|
|
CONSTRAINT fk_usage_logs_print_job
|
|
FOREIGN KEY (print_job_id) REFERENCES print_jobs(id)
|
|
ON DELETE CASCADE,
|
|
|
|
CONSTRAINT fk_usage_logs_spool
|
|
FOREIGN KEY (filament_spool_id) REFERENCES filament_spools(id)
|
|
ON DELETE RESTRICT
|
|
);
|
|
|
|
-- ============================================================================
|
|
-- Application Settings
|
|
-- ============================================================================
|
|
|
|
CREATE TABLE settings (
|
|
id SERIAL PRIMARY KEY,
|
|
key VARCHAR(255) NOT NULL UNIQUE,
|
|
value JSONB NOT NULL,
|
|
description TEXT,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
|
);
|
|
|
|
-- ============================================================================
|
|
-- Indexes
|
|
-- ============================================================================
|
|
|
|
-- Filament spools — query patterns: lookup by material, low-stock scans, barcode scans
|
|
CREATE INDEX ix_spools_material_base_id ON filament_spools(material_base_id);
|
|
CREATE INDEX ix_spools_material_finish_id ON filament_spools(material_finish_id);
|
|
CREATE INDEX ix_spools_material_modifier_id ON filament_spools(material_modifier_id);
|
|
CREATE INDEX ix_spools_remaining_grams ON filament_spools(remaining_grams)
|
|
WHERE deleted_at IS NULL; -- partial index: only active spools for low-stock queries
|
|
CREATE INDEX ix_spools_barcode ON filament_spools(barcode)
|
|
WHERE barcode IS NOT NULL AND deleted_at IS NULL;
|
|
CREATE INDEX ix_spools_deleted_at ON filament_spools(deleted_at)
|
|
WHERE deleted_at IS NOT NULL; -- small index for soft-delete filtering
|
|
|
|
-- Printers
|
|
CREATE INDEX ix_printers_printer_type_id ON printers(printer_type_id);
|
|
CREATE INDEX ix_printers_is_active ON printers(is_active)
|
|
WHERE is_active = TRUE; -- partial index for fleet dashboard queries
|
|
|
|
-- Print jobs — query by printer, status, date range, and soft-delete filter
|
|
CREATE INDEX ix_print_jobs_printer_id ON print_jobs(printer_id);
|
|
CREATE INDEX ix_print_jobs_spool_id ON print_jobs(filament_spool_id)
|
|
WHERE filament_spool_id IS NOT NULL;
|
|
CREATE INDEX ix_print_jobs_status_id ON print_jobs(job_status_id);
|
|
CREATE INDEX ix_print_jobs_created_at ON print_jobs(created_at DESC);
|
|
CREATE INDEX ix_print_jobs_deleted_at ON print_jobs(deleted_at)
|
|
WHERE deleted_at IS NOT NULL;
|
|
|
|
-- Usage logs — always queried by job or spool
|
|
CREATE INDEX ix_usage_logs_print_job_id ON usage_logs(print_job_id);
|
|
CREATE INDEX ix_usage_logs_spool_id ON usage_logs(filament_spool_id);
|
|
CREATE INDEX ix_usage_logs_logged_at ON usage_logs(logged_at DESC);
|
|
|
|
-- Settings — key lookups
|
|
CREATE INDEX ix_settings_key ON settings(key);
|
|
|
|
COMMIT;
|