Database Schema Documentation
===========================

1. SESSIONS
----------
CREATE TABLE sessions (
    id UUID PRIMARY KEY,
    created_at TIMESTAMP NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMP NOT NULL DEFAULT NOW(),
    thread_id TEXT,
    last_run_done BOOLEAN DEFAULT TRUE,
    user_id UUID,
    company_id UUID
);

2. USERS
--------
CREATE TABLE users (
    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
----------
CREATE TABLE messages (
    id SERIAL PRIMARY KEY,
    session_id UUID NOT NULL REFERENCES sessions(id) ON DELETE CASCADE,
    role VARCHAR(50) NOT NULL,
    content TEXT NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMP NOT NULL DEFAULT NOW(),
    CONSTRAINT valid_role CHECK (role IN ('user', 'assistant'))
);

4. LEADS
--------
CREATE TABLE leads (
    session_id UUID PRIMARY KEY REFERENCES sessions(id) ON DELETE CASCADE,
    payload JSONB NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMP NOT NULL DEFAULT NOW()
);

5. MEMBERS
---------
CREATE TABLE members (
    id SERIAL PRIMARY KEY,
    user_id UUID REFERENCES users(id) ON DELETE CASCADE,
    group_id INTEGER,
    created_at TIMESTAMP NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMP NOT NULL DEFAULT NOW()
);

6. COMPANY_MEMBERS
----------------
CREATE TABLE company_members (
    id SERIAL PRIMARY KEY,
    company_id INTEGER,
    user_id UUID REFERENCES users(id) ON DELETE CASCADE,
    created_at TIMESTAMP NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMP NOT NULL DEFAULT NOW()
);

7. COMPANIES
-----------
CREATE TABLE companies (
    id UUID PRIMARY KEY,
    user_id UUID REFERENCES users(id) ON DELETE CASCADE,
    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()
);

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)