Database Schema

All tables use the pip_ai_ prefix for namespace isolation.

Entity Relationship Diagram

erDiagram
    pip_ai_user_profiles {
        uuid id PK
        text email
        text full_name
        text role
        boolean is_active
    }

    pip_ai_projects {
        uuid id PK
        text name
        text brand_name
        uuid created_by FK
    }

    pip_ai_project_members {
        uuid id PK
        uuid project_id FK
        uuid user_id FK
        enum role
    }

    pip_ai_documents {
        uuid id PK
        enum kind
        uuid project_id FK
        text storage_path
        enum processing_status
    }

    pip_ai_spec_uploads {
        uuid id PK
        uuid document_id FK
        text brand_name
        text area
        int sections_count
    }

    pip_ai_spec_sections {
        uuid id PK
        uuid spec_upload_id FK
        text spec_number
        text title
        text brand_name
        text area
        vector embedding
    }

    pip_ai_pip_items {
        uuid id PK
        uuid project_id FK
        text code
        text description
        text_arr areas
        text priority
    }

    pip_ai_matches {
        uuid id PK
        uuid pip_item_id FK
        uuid spec_section_id FK
        enum match_source
        float score
        enum status
        jsonb evidence
    }

    pip_ai_floor_plans {
        uuid id PK
        uuid project_id FK
        uuid story_id FK
        text name
        text original_file_path
    }

    pip_ai_building_stories {
        uuid id PK
        uuid project_id FK
        text name
        int level
    }

    pip_ai_floor_plan_elements {
        uuid id PK
        uuid floor_plan_id FK
        text kind
        jsonb position
        jsonb style
    }

    pip_ai_pip_floor_plan_links {
        uuid id PK
        uuid pip_item_id FK
        uuid floor_plan_id FK
    }

    pip_ai_image_jobs {
        uuid id PK
        uuid project_id FK
        text status
        text prompt
    }

    pip_ai_image_variants {
        uuid id PK
        uuid job_id FK
        text output_path
    }

    pip_ai_image_messages {
        uuid id PK
        uuid job_id FK
        text sender
        text text
    }

    pip_ai_upload_jobs {
        uuid id PK
        enum kind
        uuid target_id
        enum status
        int retry_count
    }

    pip_ai_projects ||--o{ pip_ai_project_members : has
    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_image_jobs : has
    pip_ai_documents ||--o| pip_ai_spec_uploads : 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
    pip_ai_floor_plans ||--o{ pip_ai_pip_floor_plan_links : linked
    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 : has

Enum Types

pip_ai_doc_kind: 'spec' | 'pip' | 'floor_plan'
pip_ai_processing_status: 'pending' | 'processing' | 'completed' | 'failed'
pip_ai_job_status: 'queued' | 'processing' | 'done' | 'failed'
pip_ai_match_source: 'auto' | 'manual'
pip_ai_match_status: 'pending' | 'approved' | 'rejected' | 'review'
pip_ai_project_role: 'owner' | 'editor' | 'viewer'

Core Tables

pip_ai_projects

Column	Type	Nullable	Description
id	uuid	No	Primary key
name	text	No	Project name
brand_name	text	No	Hotel brand
property_description	text	Yes	Optional description
created_by	uuid	Yes	FK → auth.users
created_at	timestamptz	No	Creation timestamp
updated_at	timestamptz	No	Last update

pip_ai_spec_sections

Column	Type	Nullable	Description
id	uuid	No	Primary key
spec_upload_id	uuid	No	FK → spec_uploads
spec_number	text	No	Spec code (e.g., 'EXG2-601.2a')
brand_name	text	No	Denormalized brand
area	text	No	Denormalized area
title	text	No	Concise description
vendor	text	Yes	Vendor name
category	text	Yes	Category
keywords	text[]	No	Searchable keywords
past_pip_requests	text[]	No	Learning: matched PIP descriptions
content	text	Yes	Full extracted text
embedding	vector(1536)	Yes	OpenAI embedding
metadata	jsonb	No	Additional metadata

Unique: (spec_upload_id, spec_number)

pip_ai_pip_items

Column	Type	Nullable	Description
id	uuid	No	Primary key
project_id	uuid	No	FK → projects
document_id	uuid	Yes	FK → documents
code	text	No	Item code (e.g., '2.1')
title	text	Yes	Short title
description	text	No	Full description
areas	text[]	No	Applicable areas
categories	text[]	No	Categories
priority	text	No	low/medium/high

Unique: (project_id, code)

pip_ai_matches

Column	Type	Nullable	Description
id	uuid	No	Primary key
pip_item_id	uuid	No	FK → pip_items
spec_section_id	uuid	No	FK → spec_sections
match_source	enum	No	'auto' or 'manual'
score	float	Yes	Similarity score (0-1)
status	enum	No	pending/approved/rejected/review
evidence	jsonb	No	Match evidence
reasoning	text	Yes	AI reasoning
reviewed_by	uuid	Yes	FK → auth.users

Unique: (pip_item_id, spec_section_id)

For complete table definitions, see the migration files.