Database Structure Documentation
===============================

1. SESSIONS TABLE
----------------
Stores chat session information
- id: UUID (Primary Key)
- created_at: TIMESTAMP NOT NULL DEFAULT NOW()
- updated_at: TIMESTAMP NOT NULL DEFAULT NOW()
- thread_id: TEXT (OpenAI thread identifier)
- last_run_done: BOOLEAN DEFAULT TRUE
- user_id: UUID (Reference to users table)
- company_id: UUID

2. USERS TABLE
-------------
Stores user information
- id: UUID (Primary Key)
- name: TEXT
- email: TEXT UNIQUE NOT NULL
- phone: TEXT
- created_at: TIMESTAMP NOT NULL DEFAULT NOW()
- updated_at: TIMESTAMP NOT NULL DEFAULT NOW()

3. MESSAGES TABLE
----------------
Stores chat messages
- id: SERIAL (Primary Key)
- session_id: UUID NOT NULL (Foreign Key -> sessions.id)
- role: VARCHAR(50) NOT NULL ('user' or 'assistant')
- content: TEXT NOT NULL
- created_at: TIMESTAMP NOT NULL DEFAULT NOW()
- updated_at: TIMESTAMP NOT NULL DEFAULT NOW()
Constraints:
- CONSTRAINT valid_role CHECK (role IN ('user', 'assistant'))
- Foreign Key (session_id) REFERENCES sessions(id) ON DELETE CASCADE

4. LEADS TABLE
-------------
Stores incorporation lead data
- session_id: UUID (Primary Key, Foreign Key -> sessions.id)
- payload: JSONB NOT NULL
- created_at: TIMESTAMP NOT NULL DEFAULT NOW()
- updated_at: TIMESTAMP NOT NULL DEFAULT NOW()
Constraints:
- Foreign Key (session_id) REFERENCES sessions(id) ON DELETE CASCADE

5. MEMBERS TABLE
---------------
Stores member information
- id: SERIAL (Primary Key)
- user_id: UUID (Foreign Key -> users.id)
- group_id: INTEGER
- created_at: TIMESTAMP NOT NULL DEFAULT NOW()
- updated_at: TIMESTAMP NOT NULL DEFAULT NOW()
Constraints:
- Foreign Key (user_id) REFERENCES users(id) ON DELETE CASCADE

6. COMPANY_MEMBERS TABLE
-----------------------
Links companies with members
- id: SERIAL (Primary Key)
- company_id: INTEGER
- user_id: UUID (Foreign Key -> users.id)
- created_at: TIMESTAMP NOT NULL DEFAULT NOW()
- updated_at: TIMESTAMP NOT NULL DEFAULT NOW()
Constraints:
- Foreign Key (user_id) REFERENCES users(id) ON DELETE CASCADE

7. COMPANIES TABLE
-----------------
Stores company information
- id: UUID (Primary Key)
- user_id: UUID (Foreign Key -> users.id)
- name: TEXT NOT NULL
- state: TEXT
- entity_type: TEXT
- purpose: TEXT
- naics_code: TEXT
- session_id: UUID
- designator: TEXT
- created_at: TIMESTAMP DEFAULT NOW()
- updated_at: TIMESTAMP DEFAULT NOW()

Important Indexes
----------------
- idx_messages_session_id ON messages(session_id)
- idx_leads_updated_at ON leads(updated_at)
- idx_users_email ON users(email)
- companies_session_name_idx UNIQUE ON companies(session_id, name)