Blueprint AI Estimation System

Database Architecture & Schema Specification (v1.1 – Object Storage Based)

---

1. Overview

This document defines the relational database architecture for the Blueprint AI Estimation system.

Database Type: PostgreSQL Image Storage Strategy: Object Storage (Supabase / S3 compatible) Primary Keys: UUID v4 Timestamps: ISO 8601 (UTC)

⚠ IMPORTANT: Images are NOT stored as Base64 or BLOB in the database. The database stores only storage paths (metadata).

Actual image files are stored in object storage.

---

2. Storage Architecture Decision

❌ Not Used:

• Base64 image storage
• BYTEA / BLOB storage inside PostgreSQL

✅ Used:

• Supabase Storage (or S3 compatible object storage)
• Database stores only file paths

Example Storage Structure:

blueprint-storage/ original/{job_id}.png annotated/{job_id}.png thumbnails/{job_id}.png

Database stores:

• original_image_path
• annotated_image_path

Backend generates signed URLs dynamically.

---

3. High-Level Architecture

Users (optional) ↓ Jobs (stores image paths) ↓ Detections ↓ Estimations

RabbitMQ Result Tracking (idempotency)

---

4. Tables Overview

1. users
2. jobs
3. detections
4. estimations
5. estimation_items
6. message_logs

---

5. Table Schemas

---

5.1 users (Optional)

CREATE TABLE users ( id UUID PRIMARY KEY, email VARCHAR(255) UNIQUE NOT NULL, password_hash TEXT NOT NULL, role VARCHAR(50) DEFAULT 'user', created_at TIMESTAMP NOT NULL DEFAULT NOW(), updated_at TIMESTAMP NOT NULL DEFAULT NOW() );

CREATE INDEX idx_users_email ON users(email);

---

5.2 jobs

CREATE TYPE job_status AS ENUM ( 'PENDING', 'QUEUED', 'PROCESSING', 'COMPLETED', 'FAILED' );

CREATE TABLE jobs ( id UUID PRIMARY KEY, user_id UUID REFERENCES users(id) ON DELETE CASCADE,

project_name VARCHAR(255),

-- Object Storage Paths (NOT blobs)
original_image_path TEXT NOT NULL,
annotated_image_path TEXT,
thumbnail_image_path TEXT,

status job_status NOT NULL,

image_width INTEGER,
image_height INTEGER,

total_detections INTEGER DEFAULT 0,

model_version VARCHAR(100),
processing_time_ms INTEGER,

error_code VARCHAR(100),
error_message TEXT,

created_at TIMESTAMP NOT NULL DEFAULT NOW(),
updated_at TIMESTAMP NOT NULL DEFAULT NOW(),
completed_at TIMESTAMP

);

Indexes: CREATE INDEX idx_jobs_user_id ON jobs(user_id); CREATE INDEX idx_jobs_status ON jobs(status); CREATE INDEX idx_jobs_created_at ON jobs(created_at);

---

5.3 detections

CREATE TABLE detections ( id UUID PRIMARY KEY, job_id UUID NOT NULL REFERENCES jobs(id) ON DELETE CASCADE,

label VARCHAR(100) NOT NULL,
confidence FLOAT NOT NULL CHECK (confidence >= 0 AND confidence <= 1),

bbox_x1 INTEGER NOT NULL,
bbox_y1 INTEGER NOT NULL,
bbox_x2 INTEGER NOT NULL,
bbox_y2 INTEGER NOT NULL,

created_at TIMESTAMP NOT NULL DEFAULT NOW(),

CHECK (bbox_x2 > bbox_x1),
CHECK (bbox_y2 > bbox_y1)

);

CREATE INDEX idx_detections_job_id ON detections(job_id); CREATE INDEX idx_detections_label ON detections(label);

---

5.4 estimations

CREATE TABLE estimations ( id UUID PRIMARY KEY, job_id UUID UNIQUE NOT NULL REFERENCES jobs(id) ON DELETE CASCADE,

total_symbols INTEGER NOT NULL,
grand_total_cost BIGINT NOT NULL,

created_at TIMESTAMP NOT NULL DEFAULT NOW()

);

CREATE UNIQUE INDEX idx_estimations_job_id ON estimations(job_id);

---

5.5 estimation_items

CREATE TABLE estimation_items ( id UUID PRIMARY KEY, estimation_id UUID NOT NULL REFERENCES estimations(id) ON DELETE CASCADE,

symbol VARCHAR(100) NOT NULL,
count INTEGER NOT NULL,
unit_cost BIGINT NOT NULL,
total_cost BIGINT NOT NULL

);

CREATE INDEX idx_estimation_items_estimation_id ON estimation_items(estimation_id);

---

5.6 message_logs (RabbitMQ Idempotency)

CREATE TABLE message_logs ( id UUID PRIMARY KEY, job_id UUID NOT NULL, event_type VARCHAR(100) NOT NULL, message_hash TEXT NOT NULL, processed_at TIMESTAMP NOT NULL DEFAULT NOW() );

CREATE INDEX idx_message_logs_job_id ON message_logs(job_id);

---

6. Relationship Summary

users (1) → (N) jobs jobs (1) → (N) detections jobs (1) → (1) estimations estimations (1) → (N) estimation_items jobs (1) → (N) message_logs

---

7. Signed URL Strategy (Important)

Database stores only paths:

Example: original/3f8a9f90.png annotated/3f8a9f90.png

Backend generates signed URL at request time:

• Expiry: 5–15 minutes
• Never expose permanent public links

---

8. Performance & Scaling Notes

• Images do NOT impact DB size
• DB remains lightweight
• Storage scales independently
• Suitable for high-resolution blueprint processing

For large scale:

• Partition detections table
• Add read replica
• Cache symbol_counts in jobs table

---

9. Key Architectural Rule

The database stores metadata only. All heavy binary data lives in object storage.

This ensures:

• Clean separation of concerns
• Better scalability
• Smaller backups
• Faster queries

---

End of Database Architecture & Schema Specifica