7af9c323f6
Features: - Vue 3 frontend with Three.js/Online3DViewer - Node.js API with PostgreSQL and Redis - Python worker for model conversion - Docker Compose for deployment - ViewCube navigation with drag rotation and 90° snap - Cross-section, exploded view, and render settings - Parts tree with visibility controls 🤖 Generated with [Claude Code](https://claude.com/claude-code) Co-Authored-By: Claude Opus 4.5 <noreply@anthropic.com>
95 lines
3.1 KiB
PL/PgSQL
95 lines
3.1 KiB
PL/PgSQL
-- 3D Model Viewer Database Schema
|
|
-- PostgreSQL 16
|
|
|
|
-- Enable UUID extension
|
|
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
|
|
|
|
-- Models table: stores metadata about uploaded 3D models
|
|
CREATE TABLE models (
|
|
id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
|
|
name VARCHAR(255) NOT NULL,
|
|
original_filename VARCHAR(255) NOT NULL,
|
|
original_format VARCHAR(10) NOT NULL,
|
|
file_size BIGINT NOT NULL,
|
|
raw_storage_key TEXT,
|
|
converted_storage_key TEXT,
|
|
thumbnail_storage_key TEXT,
|
|
model_url TEXT,
|
|
thumbnail_url TEXT,
|
|
conversion_status VARCHAR(20) DEFAULT 'pending'
|
|
CHECK (conversion_status IN ('pending', 'processing', 'completed', 'failed')),
|
|
conversion_error TEXT,
|
|
metadata JSONB DEFAULT '{}',
|
|
created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
|
|
-- Model parts table: stores information about individual parts of a model
|
|
-- Used for exploded view feature
|
|
CREATE TABLE model_parts (
|
|
id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
|
|
model_id UUID NOT NULL REFERENCES models(id) ON DELETE CASCADE,
|
|
name VARCHAR(255),
|
|
mesh_index INTEGER,
|
|
bounding_box JSONB NOT NULL, -- {min: {x,y,z}, max: {x,y,z}}
|
|
center_point JSONB NOT NULL, -- {x, y, z}
|
|
parent_part_id UUID REFERENCES model_parts(id) ON DELETE SET NULL,
|
|
created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
|
|
-- Indexes for common queries
|
|
CREATE INDEX idx_models_status ON models(conversion_status);
|
|
CREATE INDEX idx_models_created ON models(created_at DESC);
|
|
CREATE INDEX idx_models_name ON models(name);
|
|
CREATE INDEX idx_models_format ON models(original_format);
|
|
CREATE INDEX idx_model_parts_model ON model_parts(model_id);
|
|
CREATE INDEX idx_model_parts_parent ON model_parts(parent_part_id);
|
|
|
|
-- Full-text search index on model name
|
|
CREATE INDEX idx_models_name_search ON models USING gin(to_tsvector('english', name));
|
|
|
|
-- Updated_at trigger function
|
|
CREATE OR REPLACE FUNCTION update_updated_at_column()
|
|
RETURNS TRIGGER AS $$
|
|
BEGIN
|
|
NEW.updated_at = CURRENT_TIMESTAMP;
|
|
RETURN NEW;
|
|
END;
|
|
$$ LANGUAGE plpgsql;
|
|
|
|
-- Apply updated_at trigger to models table
|
|
CREATE TRIGGER models_updated_at
|
|
BEFORE UPDATE ON models
|
|
FOR EACH ROW
|
|
EXECUTE FUNCTION update_updated_at_column();
|
|
|
|
-- Helper function to search models by name
|
|
CREATE OR REPLACE FUNCTION search_models(search_query TEXT)
|
|
RETURNS SETOF models AS $$
|
|
BEGIN
|
|
RETURN QUERY
|
|
SELECT *
|
|
FROM models
|
|
WHERE to_tsvector('english', name) @@ plainto_tsquery('english', search_query)
|
|
OR name ILIKE '%' || search_query || '%'
|
|
ORDER BY created_at DESC;
|
|
END;
|
|
$$ LANGUAGE plpgsql;
|
|
|
|
-- View for models with part counts
|
|
CREATE VIEW models_with_parts AS
|
|
SELECT
|
|
m.*,
|
|
COALESCE(p.part_count, 0) AS part_count
|
|
FROM models m
|
|
LEFT JOIN (
|
|
SELECT model_id, COUNT(*) AS part_count
|
|
FROM model_parts
|
|
GROUP BY model_id
|
|
) p ON m.id = p.model_id;
|
|
|
|
-- Grant permissions (for production, you might want more restricted permissions)
|
|
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO viewer;
|
|
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO viewer;
|
|
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA public TO viewer;
|