Database Architecture

PIP AI uses Supabase PostgreSQL with the pgvector extension for vector similarity search. All tables use the pip_ai_ prefix.

Entity Relationship Diagram

erDiagram
    pip_ai_projects ||--o{ pip_ai_documents : contains
    pip_ai_projects ||--o{ pip_ai_pip_items : contains
    pip_ai_projects ||--o{ pip_ai_floor_plans : contains
    pip_ai_projects ||--o{ pip_ai_building_stories : has
    pip_ai_projects ||--o{ pip_ai_project_members : has
    pip_ai_projects ||--o{ pip_ai_image_jobs : has

    pip_ai_documents ||--o| pip_ai_spec_uploads : "spec metadata"
    pip_ai_spec_uploads ||--o{ pip_ai_spec_sections : contains

    pip_ai_pip_items ||--o{ pip_ai_matches : "matched to"
    pip_ai_spec_sections ||--o{ pip_ai_matches : "matched from"

    pip_ai_pip_items ||--o{ pip_ai_pip_floor_plan_links : "linked to"
    pip_ai_floor_plans ||--o{ pip_ai_pip_floor_plan_links : "linked from"

    pip_ai_floor_plans ||--o{ pip_ai_floor_plan_elements : contains
    pip_ai_building_stories ||--o{ pip_ai_floor_plans : groups

    pip_ai_image_jobs ||--o{ pip_ai_image_variants : produces
    pip_ai_image_jobs ||--o{ pip_ai_image_messages : "chat history"

    pip_ai_user_profiles ||--o{ pip_ai_projects : creates

Core Tables

Table	Purpose	Key Columns
pip_ai_projects	Hotel property/project	name, brand_name, created_by
pip_ai_documents	Uploaded files metadata	kind, project_id, storage_path, processing_status
pip_ai_pip_items	Parsed PIP line items	code, description, areas[], categories[], priority
pip_ai_spec_sections	Vectorized spec items	spec_number, title, embedding vector(1536), brand_name, area
pip_ai_matches	PIP ↔ Spec junction	pip_item_id, spec_section_id, score, status, evidence
pip_ai_floor_plans	Floor plan metadata	name, original_file_path, rendered_image_path, rooms
pip_ai_floor_plan_elements	Canvas elements	kind, position, style, spec_section_id
pip_ai_user_profiles	Extended user data	role, email, full_name, is_active

Vector Search

The pip_ai_spec_sections table stores OpenAI text-embedding-3-small embeddings (1536 dimensions) for semantic search.

The pip_ai_search_specs() function performs hybrid search:

-- Combines vector similarity with keyword matching
-- Filters by brand_name (mandatory) and areas (optional)
SELECT id, spec_number, title, similarity
FROM pip_ai_search_specs(
  query_embedding,    -- vector(1536)
  query_text,         -- keyword fallback
  filter_brand,       -- brand isolation
  filter_areas,       -- area scoping
  match_threshold,    -- minimum similarity (default 0.70)
  match_count         -- max results (default 10)
);

Idempotency Keys

All upsert operations use unique constraints for safe reprocessing:

Table	Unique Key
pip_ai_spec_sections	(spec_upload_id, spec_number)
pip_ai_pip_items	(project_id, code)
pip_ai_matches	(pip_item_id, spec_section_id)
pip_ai_pip_floor_plan_links	(pip_item_id, floor_plan_id)
pip_ai_building_stories	(project_id, level)

Storage Buckets

Bucket	Path Pattern	Content
pip-ai-docs	specs/{brand}/{upload_id}/{file}	Spec PDFs
pip-ai-docs	pips/{project_id}/{doc_id}/{file}	PIP documents
pip-ai-docs	floor-plans/{project_id}/{plan_id}/{file}	Floor plan files
pip-ai-docs	document-builder/{project_id}/{file}	Builder images
pip-ai-docs	ai-renders/{project_id}/{job_id}/{file}	AI-generated images

For detailed schema documentation, see Database Schema.