2768a71c63
- license_rules table (name -> commercial); licenses auto-register on import with a best-effort seed, overridable in Settings (applies the choice to every model with that license) - resolveCommercial() replaces ad-hoc inferCommercial across import/refresh - GET/PUT /api/licenses; "License types" section in Settings - Fix CC short codes: BY / BY-SA / BY-ND now classed sellable (MakerWorld stores e.g. "BY-SA"); NC variants stay non-commercial Co-Authored-By: Claude Opus 4.8 <noreply@anthropic.com>
109 lines
4.1 KiB
SQL
109 lines
4.1 KiB
SQL
-- STL Library Manager schema
|
|
-- A "model" is one logical download (a Thingiverse thing, a Printables model, a
|
|
-- single uploaded STL). A model owns one or more files (the actual .stl/.3mf/...).
|
|
|
|
-- Per-license commercial-use policy. Auto-registered as licenses are seen;
|
|
-- editable in Settings. commercial: true=sellable, false=non-commercial, null=unknown.
|
|
CREATE TABLE IF NOT EXISTS license_rules (
|
|
name TEXT PRIMARY KEY,
|
|
commercial BOOLEAN
|
|
);
|
|
|
|
-- App users (email/password auth).
|
|
CREATE TABLE IF NOT EXISTS users (
|
|
id SERIAL PRIMARY KEY,
|
|
email TEXT NOT NULL UNIQUE,
|
|
password_hash TEXT NOT NULL,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now()
|
|
);
|
|
|
|
-- One-time password-reset tokens (token stored hashed).
|
|
CREATE TABLE IF NOT EXISTS password_resets (
|
|
id SERIAL PRIMARY KEY,
|
|
user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
|
|
token_hash TEXT NOT NULL,
|
|
expires_at TIMESTAMPTZ NOT NULL,
|
|
used BOOLEAN NOT NULL DEFAULT false
|
|
);
|
|
CREATE INDEX IF NOT EXISTS idx_password_resets_token ON password_resets(token_hash);
|
|
|
|
-- Simple key/value app settings (API tokens, site auth). Editable from the UI.
|
|
CREATE TABLE IF NOT EXISTS settings (
|
|
key TEXT PRIMARY KEY,
|
|
value TEXT,
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS designers (
|
|
id SERIAL PRIMARY KEY,
|
|
name TEXT NOT NULL,
|
|
source_site TEXT, -- thingiverse | printables | makerworld | ...
|
|
profile_url TEXT,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
UNIQUE (name, source_site)
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS projects (
|
|
id SERIAL PRIMARY KEY,
|
|
name TEXT NOT NULL,
|
|
description TEXT NOT NULL DEFAULT '',
|
|
date_created TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
date_modified TIMESTAMPTZ
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS tags (
|
|
id SERIAL PRIMARY KEY,
|
|
name TEXT NOT NULL UNIQUE
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS models (
|
|
id SERIAL PRIMARY KEY,
|
|
name TEXT NOT NULL,
|
|
description TEXT NOT NULL DEFAULT '',
|
|
source_site TEXT, -- where it came from, null for manual upload
|
|
source_url TEXT, -- original page URL (for attribution)
|
|
source_id TEXT, -- the site's id for the thing/model
|
|
license TEXT,
|
|
designer_id INTEGER REFERENCES designers(id) ON DELETE SET NULL,
|
|
thumbnail_path TEXT,
|
|
print_settings JSONB NOT NULL DEFAULT '{}'::jsonb,
|
|
date_added TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
date_modified TIMESTAMPTZ
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS files (
|
|
id SERIAL PRIMARY KEY,
|
|
model_id INTEGER NOT NULL REFERENCES models(id) ON DELETE CASCADE,
|
|
file_name TEXT NOT NULL, -- on-disk name (unique suffix)
|
|
original_name TEXT NOT NULL, -- name as downloaded/uploaded
|
|
file_path TEXT NOT NULL, -- path on disk
|
|
format TEXT NOT NULL, -- stl | 3mf | obj | step | gcode | zip | ...
|
|
size BIGINT NOT NULL DEFAULT 0,
|
|
volume DOUBLE PRECISION, -- cm^3 (from node-stl), when computable
|
|
bbox JSONB, -- {x,y,z} bounding box
|
|
facets INTEGER,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now()
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS model_projects (
|
|
model_id INTEGER NOT NULL REFERENCES models(id) ON DELETE CASCADE,
|
|
project_id INTEGER NOT NULL REFERENCES projects(id) ON DELETE CASCADE,
|
|
PRIMARY KEY (model_id, project_id)
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS model_tags (
|
|
model_id INTEGER NOT NULL REFERENCES models(id) ON DELETE CASCADE,
|
|
tag_id INTEGER NOT NULL REFERENCES tags(id) ON DELETE CASCADE,
|
|
PRIMARY KEY (model_id, tag_id)
|
|
);
|
|
|
|
-- Cached GLB conversion of a 3MF, for in-browser 3D preview.
|
|
ALTER TABLE files ADD COLUMN IF NOT EXISTS glb_path TEXT;
|
|
|
|
-- Whether the model's license permits commercial use (selling). true/false/null.
|
|
ALTER TABLE models ADD COLUMN IF NOT EXISTS commercial_use BOOLEAN;
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_files_model ON files(model_id);
|
|
CREATE INDEX IF NOT EXISTS idx_model_projects_p ON model_projects(project_id);
|
|
CREATE INDEX IF NOT EXISTS idx_model_tags_t ON model_tags(tag_id);
|
|
CREATE INDEX IF NOT EXISTS idx_models_source ON models(source_site, source_id);
|