--
-- PostgreSQL database dump
--

\restrict PxOn4uJ4CI6P8l8AmSZ09OeBX6Argss8bqC5xu77ZH2gTwQrWoQ1le37waT1Tfc

-- Dumped from database version 16.11 (Debian 16.11-1.pgdg12+1)
-- Dumped by pg_dump version 16.11 (Debian 16.11-1.pgdg12+1)

SET statement_timeout = 0;
SET lock_timeout = 0;
SET idle_in_transaction_session_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SELECT pg_catalog.set_config('search_path', '', false);
SET check_function_bodies = false;
SET xmloption = content;
SET client_min_messages = warning;
SET row_security = off;

--
-- Name: pg_trgm; Type: EXTENSION; Schema: -; Owner: -
--

CREATE EXTENSION IF NOT EXISTS pg_trgm WITH SCHEMA public;


--
-- Name: EXTENSION pg_trgm; Type: COMMENT; Schema: -; Owner: 
--

COMMENT ON EXTENSION pg_trgm IS 'text similarity measurement and index searching based on trigrams';


--
-- Name: uuid-ossp; Type: EXTENSION; Schema: -; Owner: -
--

CREATE EXTENSION IF NOT EXISTS "uuid-ossp" WITH SCHEMA public;


--
-- Name: EXTENSION "uuid-ossp"; Type: COMMENT; Schema: -; Owner: 
--

COMMENT ON EXTENSION "uuid-ossp" IS 'generate universally unique identifiers (UUIDs)';


--
-- Name: get_conversation_message_stats(uuid, character varying); Type: FUNCTION; Schema: public; Owner: archie_dev
--

CREATE FUNCTION public.get_conversation_message_stats(p_conversation_id uuid, p_firm_id character varying) RETURNS TABLE(total_messages bigint, user_messages bigint, assistant_messages bigint, system_messages bigint, total_artifacts bigint, total_citations bigint, avg_confidence numeric, first_message_at timestamp with time zone, last_message_at timestamp with time zone)
    LANGUAGE plpgsql STABLE
    AS $$
BEGIN
    RETURN QUERY
    SELECT
        COUNT(m.message_id)::BIGINT as total_messages,
        COUNT(m.message_id) FILTER (WHERE m.role = 'user')::BIGINT as user_messages,
        COUNT(m.message_id) FILTER (WHERE m.role = 'assistant')::BIGINT as assistant_messages,
        COUNT(m.message_id) FILTER (WHERE m.role = 'system')::BIGINT as system_messages,
        COUNT(DISTINCT a.artifact_id)::BIGINT as total_artifacts,
        COUNT(DISTINCT c.citation_id)::BIGINT as total_citations,
        AVG(m.confidence_score) FILTER (WHERE m.confidence_score IS NOT NULL)::DECIMAL(3,2) as avg_confidence,
        MIN(m.created_at) as first_message_at,
        MAX(m.created_at) as last_message_at
    FROM messages m
    LEFT JOIN artifacts a ON m.message_id = a.message_id
    LEFT JOIN citations c ON m.message_id = c.message_id
    WHERE
        m.conversation_id = p_conversation_id
        AND m.firm_id = p_firm_id;
END;
$$;


ALTER FUNCTION public.get_conversation_message_stats(p_conversation_id uuid, p_firm_id character varying) OWNER TO archie_dev;

--
-- Name: FUNCTION get_conversation_message_stats(p_conversation_id uuid, p_firm_id character varying); Type: COMMENT; Schema: public; Owner: archie_dev
--

COMMENT ON FUNCTION public.get_conversation_message_stats(p_conversation_id uuid, p_firm_id character varying) IS 'Get comprehensive statistics for a conversation';


--
-- Name: get_user_conversation_permissions(uuid, character varying, character varying); Type: FUNCTION; Schema: public; Owner: archie_dev
--

CREATE FUNCTION public.get_user_conversation_permissions(p_conversation_id uuid, p_user_id character varying, p_firm_id character varying) RETURNS integer
    LANGUAGE plpgsql STABLE
    AS $$
DECLARE
    user_permissions INTEGER;
    is_owner BOOLEAN;
BEGIN
    -- Check if user is owner
    SELECT owner_id = p_user_id INTO is_owner
    FROM conversations
    WHERE id = p_conversation_id AND firm_id = p_firm_id;

    IF is_owner THEN
        RETURN 15; -- All permissions
    END IF;

    -- Get participant permissions
    SELECT permissions INTO user_permissions
    FROM conversation_participants
    WHERE conversation_id = p_conversation_id
        AND user_id = p_user_id
        AND firm_id = p_firm_id
        AND status = 'active';

    RETURN COALESCE(user_permissions, 0);
END;
$$;


ALTER FUNCTION public.get_user_conversation_permissions(p_conversation_id uuid, p_user_id character varying, p_firm_id character varying) OWNER TO archie_dev;

--
-- Name: FUNCTION get_user_conversation_permissions(p_conversation_id uuid, p_user_id character varying, p_firm_id character varying); Type: COMMENT; Schema: public; Owner: archie_dev
--

COMMENT ON FUNCTION public.get_user_conversation_permissions(p_conversation_id uuid, p_user_id character varying, p_firm_id character varying) IS 'Get user permission level for conversation (bit flags)';


--
-- Name: has_conversation_permission(uuid, character varying, character varying, integer); Type: FUNCTION; Schema: public; Owner: archie_dev
--

CREATE FUNCTION public.has_conversation_permission(p_conversation_id uuid, p_user_id character varying, p_firm_id character varying, p_required_permission integer) RETURNS boolean
    LANGUAGE plpgsql STABLE
    AS $$
DECLARE
    user_permissions INTEGER;
    is_owner BOOLEAN;
BEGIN
    -- Check if user is owner
    SELECT owner_id = p_user_id INTO is_owner
    FROM conversations
    WHERE id = p_conversation_id AND firm_id = p_firm_id;

    IF is_owner THEN
        RETURN TRUE; -- Owners have all permissions
    END IF;

    -- Check participant permissions
    SELECT permissions INTO user_permissions
    FROM conversation_participants
    WHERE conversation_id = p_conversation_id
        AND user_id = p_user_id
        AND firm_id = p_firm_id
        AND status = 'active';

    IF user_permissions IS NULL THEN
        RETURN FALSE; -- Not a participant
    END IF;

    -- Check if user has required permission (bitwise AND)
    RETURN (user_permissions & p_required_permission) = p_required_permission;
END;
$$;


ALTER FUNCTION public.has_conversation_permission(p_conversation_id uuid, p_user_id character varying, p_firm_id character varying, p_required_permission integer) OWNER TO archie_dev;

--
-- Name: FUNCTION has_conversation_permission(p_conversation_id uuid, p_user_id character varying, p_firm_id character varying, p_required_permission integer); Type: COMMENT; Schema: public; Owner: archie_dev
--

COMMENT ON FUNCTION public.has_conversation_permission(p_conversation_id uuid, p_user_id character varying, p_firm_id character varying, p_required_permission integer) IS 'Check if user has specific permission for conversation (1=READ, 2=WRITE, 4=DELETE, 8=COMMENT)';


--
-- Name: search_messages(uuid, character varying, text, integer); Type: FUNCTION; Schema: public; Owner: archie_dev
--

CREATE FUNCTION public.search_messages(p_conversation_id uuid, p_firm_id character varying, p_search_query text, p_limit integer DEFAULT 50) RETURNS TABLE(message_id uuid, conversation_id uuid, content text, role character varying, created_at timestamp with time zone, rank real)
    LANGUAGE plpgsql STABLE
    AS $$
BEGIN
    RETURN QUERY
    SELECT
        m.message_id,
        m.conversation_id,
        m.content,
        m.role,
        m.created_at,
        ts_rank(to_tsvector('english', m.content), plainto_tsquery('english', p_search_query)) as rank
    FROM messages m
    WHERE
        m.conversation_id = p_conversation_id
        AND m.firm_id = p_firm_id
        AND to_tsvector('english', m.content) @@ plainto_tsquery('english', p_search_query)
    ORDER BY rank DESC, m.created_at DESC
    LIMIT p_limit;
END;
$$;


ALTER FUNCTION public.search_messages(p_conversation_id uuid, p_firm_id character varying, p_search_query text, p_limit integer) OWNER TO archie_dev;

--
-- Name: FUNCTION search_messages(p_conversation_id uuid, p_firm_id character varying, p_search_query text, p_limit integer); Type: COMMENT; Schema: public; Owner: archie_dev
--

COMMENT ON FUNCTION public.search_messages(p_conversation_id uuid, p_firm_id character varying, p_search_query text, p_limit integer) IS 'Full-text search messages with relevance ranking';


--
-- Name: update_updated_at_column(); Type: FUNCTION; Schema: public; Owner: archie_dev
--

CREATE FUNCTION public.update_updated_at_column() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
        BEGIN
            NEW.updated_at = NOW();
            RETURN NEW;
        END;
        $$;


ALTER FUNCTION public.update_updated_at_column() OWNER TO archie_dev;

--
-- Name: update_users_updated_at(); Type: FUNCTION; Schema: public; Owner: archie_dev
--

CREATE FUNCTION public.update_users_updated_at() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
BEGIN
    NEW.updated_at = NOW();
    RETURN NEW;
END;
$$;


ALTER FUNCTION public.update_users_updated_at() OWNER TO archie_dev;

SET default_tablespace = '';

SET default_table_access_method = heap;

--
-- Name: directory_mappings; Type: TABLE; Schema: public; Owner: archie_dev
--

CREATE TABLE public.directory_mappings (
    id uuid DEFAULT public.uuid_generate_v4() NOT NULL,
    directory_id character varying(255) NOT NULL,
    firm_id character varying(255) NOT NULL,
    organization_id character varying(255),
    directory_type character varying(50),
    is_active boolean DEFAULT true,
    metadata jsonb DEFAULT '{}'::jsonb,
    created_at timestamp with time zone DEFAULT now() NOT NULL,
    updated_at timestamp with time zone DEFAULT now() NOT NULL
);


ALTER TABLE public.directory_mappings OWNER TO archie_dev;

--
-- Name: TABLE directory_mappings; Type: COMMENT; Schema: public; Owner: archie_dev
--

COMMENT ON TABLE public.directory_mappings IS 'Mapping between WorkOS directories and internal firms';


--
-- Name: COLUMN directory_mappings.directory_id; Type: COMMENT; Schema: public; Owner: archie_dev
--

COMMENT ON COLUMN public.directory_mappings.directory_id IS 'WorkOS directory identifier';


--
-- Name: COLUMN directory_mappings.firm_id; Type: COMMENT; Schema: public; Owner: archie_dev
--

COMMENT ON COLUMN public.directory_mappings.firm_id IS 'Internal firm identifier';


--
-- Name: COLUMN directory_mappings.directory_type; Type: COMMENT; Schema: public; Owner: archie_dev
--

COMMENT ON COLUMN public.directory_mappings.directory_type IS 'Identity provider type (azure_ad, okta, google, etc.)';


--
-- Name: users; Type: TABLE; Schema: public; Owner: archie_dev
--

CREATE TABLE public.users (
    user_id character varying(255) NOT NULL,
    email character varying(255) NOT NULL,
    firm_id character varying(255),
    role character varying(50) DEFAULT 'user'::character varying NOT NULL,
    subscription_tier character varying(50) DEFAULT 'starter'::character varying NOT NULL,
    created_at timestamp with time zone DEFAULT now() NOT NULL,
    last_login_at timestamp with time zone,
    metadata jsonb DEFAULT '{}'::jsonb NOT NULL,
    updated_at timestamp with time zone DEFAULT now() NOT NULL,
    deleted_at timestamp with time zone,
    CONSTRAINT users_role_check CHECK (((role)::text = ANY ((ARRAY['user'::character varying, 'firm_admin'::character varying, 'admin'::character varying])::text[]))),
    CONSTRAINT users_tier_check CHECK (((subscription_tier)::text = ANY ((ARRAY['starter'::character varying, 'professional'::character varying, 'enterprise'::character varying])::text[])))
);


ALTER TABLE public.users OWNER TO archie_dev;

--
-- Name: TABLE users; Type: COMMENT; Schema: public; Owner: archie_dev
--

COMMENT ON TABLE public.users IS 'User accounts with authentication and subscription information';


--
-- Name: active_directories; Type: VIEW; Schema: public; Owner: archie_dev
--

CREATE VIEW public.active_directories AS
 SELECT d.id,
    d.directory_id,
    d.firm_id,
    d.organization_id,
    d.directory_type,
    count(DISTINCT u.user_id) AS user_count,
    d.created_at,
    d.updated_at
   FROM (public.directory_mappings d
     LEFT JOIN public.users u ON ((((u.firm_id)::text = (d.firm_id)::text) AND ((u.metadata ->> 'workos_directory_id'::text) = (d.directory_id)::text))))
  WHERE (d.is_active = true)
  GROUP BY d.id, d.directory_id, d.firm_id, d.organization_id, d.directory_type, d.created_at, d.updated_at;


ALTER VIEW public.active_directories OWNER TO archie_dev;

--
-- Name: alembic_version; Type: TABLE; Schema: public; Owner: archie_dev
--

CREATE TABLE public.alembic_version (
    version_num character varying(32) NOT NULL
);


ALTER TABLE public.alembic_version OWNER TO archie_dev;

--
-- Name: artifacts; Type: TABLE; Schema: public; Owner: archie_dev
--

CREATE TABLE public.artifacts (
    artifact_id uuid DEFAULT public.uuid_generate_v4() NOT NULL,
    message_id uuid NOT NULL,
    artifact_type character varying(50) NOT NULL,
    file_url text NOT NULL,
    file_name character varying(255) NOT NULL,
    file_size bigint,
    mime_type character varying(100),
    metadata jsonb DEFAULT '{}'::jsonb,
    created_at timestamp with time zone DEFAULT now() NOT NULL,
    CONSTRAINT chk_artifact_file_size CHECK (((file_size IS NULL) OR (file_size > 0))),
    CONSTRAINT chk_artifact_file_url CHECK ((char_length(file_url) > 0)),
    CONSTRAINT chk_artifact_type CHECK (((artifact_type)::text = ANY ((ARRAY['file'::character varying, 'spreadsheet'::character varying, 'chart'::character varying, 'document'::character varying, 'code'::character varying])::text[])))
);


ALTER TABLE public.artifacts OWNER TO archie_dev;

--
-- Name: TABLE artifacts; Type: COMMENT; Schema: public; Owner: archie_dev
--

COMMENT ON TABLE public.artifacts IS 'Files and documents attached to messages (charts, spreadsheets, documents)';


--
-- Name: COLUMN artifacts.artifact_type; Type: COMMENT; Schema: public; Owner: archie_dev
--

COMMENT ON COLUMN public.artifacts.artifact_type IS 'Type of artifact: file, spreadsheet, chart, document, code';


--
-- Name: COLUMN artifacts.file_url; Type: COMMENT; Schema: public; Owner: archie_dev
--

COMMENT ON COLUMN public.artifacts.file_url IS 'Storage URL for the artifact (S3, etc.)';


--
-- Name: COLUMN artifacts.file_size; Type: COMMENT; Schema: public; Owner: archie_dev
--

COMMENT ON COLUMN public.artifacts.file_size IS 'File size in bytes';


--
-- Name: COLUMN artifacts.mime_type; Type: COMMENT; Schema: public; Owner: archie_dev
--

COMMENT ON COLUMN public.artifacts.mime_type IS 'MIME type of the file (application/pdf, image/png, etc.)';


--
-- Name: citations; Type: TABLE; Schema: public; Owner: archie_dev
--

CREATE TABLE public.citations (
    citation_id uuid DEFAULT public.uuid_generate_v4() NOT NULL,
    message_id uuid NOT NULL,
    source_url text NOT NULL,
    title character varying(500),
    snippet text,
    relevance_score numeric(3,2),
    metadata jsonb DEFAULT '{}'::jsonb,
    created_at timestamp with time zone DEFAULT now() NOT NULL,
    CONSTRAINT chk_citation_relevance CHECK (((relevance_score IS NULL) OR ((relevance_score >= (0)::numeric) AND (relevance_score <= (1)::numeric)))),
    CONSTRAINT chk_citation_snippet_length CHECK (((snippet IS NULL) OR (char_length(snippet) <= 1000))),
    CONSTRAINT chk_citation_source_url CHECK ((char_length(source_url) > 0))
);


ALTER TABLE public.citations OWNER TO archie_dev;

--
-- Name: TABLE citations; Type: COMMENT; Schema: public; Owner: archie_dev
--

COMMENT ON TABLE public.citations IS 'Source references and citations for assistant messages';


--
-- Name: COLUMN citations.source_url; Type: COMMENT; Schema: public; Owner: archie_dev
--

COMMENT ON COLUMN public.citations.source_url IS 'URL of the cited source';


--
-- Name: COLUMN citations.title; Type: COMMENT; Schema: public; Owner: archie_dev
--

COMMENT ON COLUMN public.citations.title IS 'Title or description of the source';


--
-- Name: COLUMN citations.snippet; Type: COMMENT; Schema: public; Owner: archie_dev
--

COMMENT ON COLUMN public.citations.snippet IS 'Relevant excerpt from the source (max 1000 chars)';


--
-- Name: COLUMN citations.relevance_score; Type: COMMENT; Schema: public; Owner: archie_dev
--

COMMENT ON COLUMN public.citations.relevance_score IS 'Relevance/confidence score (0.00 to 1.00)';


--
-- Name: conversation_archive_log; Type: TABLE; Schema: public; Owner: archie_dev
--

CREATE TABLE public.conversation_archive_log (
    id uuid DEFAULT public.uuid_generate_v4() NOT NULL,
    conversation_id uuid NOT NULL,
    user_id character varying(255) NOT NULL,
    action character varying(50) NOT NULL,
    reason text,
    previous_status character varying(50),
    new_status character varying(50),
    created_at timestamp with time zone DEFAULT now() NOT NULL,
    metadata jsonb DEFAULT '{}'::jsonb,
    CONSTRAINT chk_archive_action CHECK (((action)::text = ANY ((ARRAY['archive'::character varying, 'unarchive'::character varying])::text[])))
);


ALTER TABLE public.conversation_archive_log OWNER TO archie_dev;

--
-- Name: TABLE conversation_archive_log; Type: COMMENT; Schema: public; Owner: archie_dev
--

COMMENT ON TABLE public.conversation_archive_log IS 'Audit trail for conversation archive/unarchive operations';


--
-- Name: conversations; Type: TABLE; Schema: public; Owner: archie_dev
--

CREATE TABLE public.conversations (
    id uuid DEFAULT public.uuid_generate_v4() NOT NULL,
    user_id character varying(255) NOT NULL,
    firm_id character varying(255),
    title text DEFAULT 'New Conversation'::text NOT NULL,
    created_at timestamp with time zone DEFAULT now() NOT NULL,
    updated_at timestamp with time zone DEFAULT now() NOT NULL,
    metadata jsonb DEFAULT '{}'::jsonb NOT NULL,
    status character varying(50) DEFAULT 'active'::character varying NOT NULL,
    owner_id character varying(255),
    archived_at timestamp with time zone,
    archived_by character varying(255),
    CONSTRAINT chk_conversation_status CHECK (((status)::text = ANY ((ARRAY['active'::character varying, 'archived'::character varying, 'deleted'::character varying])::text[])))
);


ALTER TABLE public.conversations OWNER TO archie_dev;

--
-- Name: TABLE conversations; Type: COMMENT; Schema: public; Owner: archie_dev
--

COMMENT ON TABLE public.conversations IS 'Conversation threads grouping multiple queries';


--
-- Name: COLUMN conversations.status; Type: COMMENT; Schema: public; Owner: archie_dev
--

COMMENT ON COLUMN public.conversations.status IS 'active: normal operation, archived: read-only, deleted: soft delete';


--
-- Name: COLUMN conversations.owner_id; Type: COMMENT; Schema: public; Owner: archie_dev
--

COMMENT ON COLUMN public.conversations.owner_id IS 'Primary owner with full control (initially the creator)';


--
-- Name: messages; Type: TABLE; Schema: public; Owner: archie_dev
--

CREATE TABLE public.messages (
    message_id uuid DEFAULT public.uuid_generate_v4() NOT NULL,
    conversation_id uuid NOT NULL,
    user_id character varying(255) NOT NULL,
    firm_id character varying(255) NOT NULL,
    role character varying(20) NOT NULL,
    content text NOT NULL,
    confidence_score numeric(3,2),
    model_used character varying(100),
    metadata jsonb DEFAULT '{}'::jsonb,
    created_at timestamp with time zone DEFAULT now() NOT NULL,
    updated_at timestamp with time zone DEFAULT now() NOT NULL,
    CONSTRAINT chk_message_confidence CHECK (((confidence_score IS NULL) OR ((confidence_score >= (0)::numeric) AND (confidence_score <= (1)::numeric)))),
    CONSTRAINT chk_message_content_length CHECK ((char_length(content) <= 50000)),
    CONSTRAINT chk_message_firm_isolation CHECK ((firm_id IS NOT NULL)),
    CONSTRAINT chk_message_role CHECK (((role)::text = ANY ((ARRAY['user'::character varying, 'assistant'::character varying, 'system'::character varying])::text[])))
);


ALTER TABLE public.messages OWNER TO archie_dev;

--
-- Name: TABLE messages; Type: COMMENT; Schema: public; Owner: archie_dev
--

COMMENT ON TABLE public.messages IS 'Stores all conversation messages with full-text search and metadata';


--
-- Name: COLUMN messages.role; Type: COMMENT; Schema: public; Owner: archie_dev
--

COMMENT ON COLUMN public.messages.role IS 'Message sender role: user (human), assistant (AI), system (automated)';


--
-- Name: COLUMN messages.content; Type: COMMENT; Schema: public; Owner: archie_dev
--

COMMENT ON COLUMN public.messages.content IS 'Message text content, max 50000 characters';


--
-- Name: COLUMN messages.confidence_score; Type: COMMENT; Schema: public; Owner: archie_dev
--

COMMENT ON COLUMN public.messages.confidence_score IS 'AI confidence level (0.00 to 1.00), NULL for user/system messages';


--
-- Name: COLUMN messages.model_used; Type: COMMENT; Schema: public; Owner: archie_dev
--

COMMENT ON COLUMN public.messages.model_used IS 'AI model identifier used to generate assistant messages';


--
-- Name: COLUMN messages.metadata; Type: COMMENT; Schema: public; Owner: archie_dev
--

COMMENT ON COLUMN public.messages.metadata IS 'Extensible metadata for custom attributes (tokens, processing time, etc.)';


--
-- Name: conversation_message_summary; Type: VIEW; Schema: public; Owner: archie_dev
--

CREATE VIEW public.conversation_message_summary AS
 SELECT c.id AS conversation_id,
    c.firm_id,
    c.title,
    c.status,
    count(m.message_id) AS total_messages,
    count(m.message_id) FILTER (WHERE ((m.role)::text = 'user'::text)) AS user_messages,
    count(m.message_id) FILTER (WHERE ((m.role)::text = 'assistant'::text)) AS assistant_messages,
    count(DISTINCT a.artifact_id) AS total_artifacts,
    count(DISTINCT ci.citation_id) AS total_citations,
    avg(m.confidence_score) FILTER (WHERE (m.confidence_score IS NOT NULL)) AS avg_confidence,
    max(m.created_at) AS last_message_at
   FROM (((public.conversations c
     LEFT JOIN public.messages m ON ((c.id = m.conversation_id)))
     LEFT JOIN public.artifacts a ON ((m.message_id = a.message_id)))
     LEFT JOIN public.citations ci ON ((m.message_id = ci.message_id)))
  GROUP BY c.id, c.firm_id, c.title, c.status;


ALTER VIEW public.conversation_message_summary OWNER TO archie_dev;

--
-- Name: VIEW conversation_message_summary; Type: COMMENT; Schema: public; Owner: archie_dev
--

COMMENT ON VIEW public.conversation_message_summary IS 'Summary statistics for all conversations';


--
-- Name: conversation_participants; Type: TABLE; Schema: public; Owner: archie_dev
--

CREATE TABLE public.conversation_participants (
    id uuid DEFAULT public.uuid_generate_v4() NOT NULL,
    conversation_id uuid NOT NULL,
    user_id character varying(255) NOT NULL,
    firm_id character varying(255) NOT NULL,
    permissions integer DEFAULT 1 NOT NULL,
    role character varying(50) DEFAULT 'participant'::character varying NOT NULL,
    added_by character varying(255) NOT NULL,
    added_at timestamp with time zone DEFAULT now() NOT NULL,
    last_accessed_at timestamp with time zone,
    status character varying(50) DEFAULT 'active'::character varying NOT NULL,
    metadata jsonb DEFAULT '{}'::jsonb,
    created_at timestamp with time zone DEFAULT now() NOT NULL,
    updated_at timestamp with time zone DEFAULT now() NOT NULL,
    CONSTRAINT chk_participant_firm_isolation CHECK ((firm_id IS NOT NULL)),
    CONSTRAINT chk_participant_permissions CHECK (((permissions >= 0) AND (permissions <= 15))),
    CONSTRAINT chk_participant_role CHECK (((role)::text = ANY ((ARRAY['owner'::character varying, 'admin'::character varying, 'participant'::character varying, 'viewer'::character varying])::text[]))),
    CONSTRAINT chk_participant_status CHECK (((status)::text = ANY ((ARRAY['active'::character varying, 'inactive'::character varying, 'removed'::character varying])::text[])))
);


ALTER TABLE public.conversation_participants OWNER TO archie_dev;

--
-- Name: TABLE conversation_participants; Type: COMMENT; Schema: public; Owner: archie_dev
--

COMMENT ON TABLE public.conversation_participants IS 'Tracks users participating in conversations with granular permissions';


--
-- Name: COLUMN conversation_participants.permissions; Type: COMMENT; Schema: public; Owner: archie_dev
--

COMMENT ON COLUMN public.conversation_participants.permissions IS 'Bit flags: 1=READ, 2=WRITE, 4=DELETE, 8=COMMENT';


--
-- Name: COLUMN conversation_participants.role; Type: COMMENT; Schema: public; Owner: archie_dev
--

COMMENT ON COLUMN public.conversation_participants.role IS 'owner: full control, admin: manage participants, participant: standard access, viewer: read-only';


--
-- Name: conversation_shares; Type: TABLE; Schema: public; Owner: archie_dev
--

CREATE TABLE public.conversation_shares (
    id uuid DEFAULT public.uuid_generate_v4() NOT NULL,
    conversation_id uuid NOT NULL,
    invited_by character varying(255) NOT NULL,
    invited_user_id character varying(255),
    invited_team_id uuid,
    granted_permissions integer DEFAULT 1 NOT NULL,
    granted_role character varying(50) DEFAULT 'participant'::character varying NOT NULL,
    status character varying(50) DEFAULT 'pending'::character varying NOT NULL,
    invitation_message text,
    invitation_token character varying(255),
    expires_at timestamp with time zone NOT NULL,
    responded_at timestamp with time zone,
    response_message text,
    created_at timestamp with time zone DEFAULT now() NOT NULL,
    updated_at timestamp with time zone DEFAULT now() NOT NULL,
    metadata jsonb DEFAULT '{}'::jsonb,
    CONSTRAINT chk_share_expiration CHECK ((expires_at > created_at)),
    CONSTRAINT chk_share_permissions CHECK (((granted_permissions >= 0) AND (granted_permissions <= 15))),
    CONSTRAINT chk_share_role CHECK (((granted_role)::text = ANY ((ARRAY['admin'::character varying, 'participant'::character varying, 'viewer'::character varying])::text[]))),
    CONSTRAINT chk_share_status CHECK (((status)::text = ANY ((ARRAY['pending'::character varying, 'accepted'::character varying, 'rejected'::character varying, 'expired'::character varying, 'cancelled'::character varying])::text[]))),
    CONSTRAINT chk_share_target CHECK ((((invited_user_id IS NOT NULL) AND (invited_team_id IS NULL)) OR ((invited_user_id IS NULL) AND (invited_team_id IS NOT NULL))))
);


ALTER TABLE public.conversation_shares OWNER TO archie_dev;

--
-- Name: TABLE conversation_shares; Type: COMMENT; Schema: public; Owner: archie_dev
--

COMMENT ON TABLE public.conversation_shares IS 'Manages invitation-based conversation sharing with users and teams';


--
-- Name: COLUMN conversation_shares.granted_permissions; Type: COMMENT; Schema: public; Owner: archie_dev
--

COMMENT ON COLUMN public.conversation_shares.granted_permissions IS 'Bit flags: 1=READ, 2=WRITE, 4=DELETE, 8=COMMENT';


--
-- Name: COLUMN conversation_shares.invitation_token; Type: COMMENT; Schema: public; Owner: archie_dev
--

COMMENT ON COLUMN public.conversation_shares.invitation_token IS 'Secure token for email invitation links';


--
-- Name: conversations_with_participants; Type: VIEW; Schema: public; Owner: archie_dev
--

CREATE VIEW public.conversations_with_participants AS
SELECT
    NULL::uuid AS id,
    NULL::character varying(255) AS user_id,
    NULL::character varying(255) AS firm_id,
    NULL::text AS title,
    NULL::timestamp with time zone AS created_at,
    NULL::timestamp with time zone AS updated_at,
    NULL::jsonb AS metadata,
    NULL::character varying(50) AS status,
    NULL::character varying(255) AS owner_id,
    NULL::timestamp with time zone AS archived_at,
    NULL::character varying(255) AS archived_by,
    NULL::bigint AS participant_count,
    NULL::character varying[] AS participant_user_ids;


ALTER VIEW public.conversations_with_participants OWNER TO archie_dev;

--
-- Name: VIEW conversations_with_participants; Type: COMMENT; Schema: public; Owner: archie_dev
--

COMMENT ON VIEW public.conversations_with_participants IS 'Conversations with participant counts and user IDs';


--
-- Name: feedback; Type: TABLE; Schema: public; Owner: archie_dev
--

CREATE TABLE public.feedback (
    id uuid DEFAULT public.uuid_generate_v4() NOT NULL,
    query_id uuid NOT NULL,
    user_id character varying(255) NOT NULL,
    rating integer NOT NULL,
    feedback_text text,
    created_at timestamp with time zone DEFAULT now() NOT NULL,
    metadata jsonb DEFAULT '{}'::jsonb NOT NULL,
    CONSTRAINT feedback_rating_check CHECK (((rating >= 1) AND (rating <= 5)))
);


ALTER TABLE public.feedback OWNER TO archie_dev;

--
-- Name: TABLE feedback; Type: COMMENT; Schema: public; Owner: archie_dev
--

COMMENT ON TABLE public.feedback IS 'User feedback on query responses for continuous improvement';


--
-- Name: identity_sync_audit; Type: TABLE; Schema: public; Owner: archie_dev
--

CREATE TABLE public.identity_sync_audit (
    id uuid DEFAULT public.uuid_generate_v4() NOT NULL,
    user_id character varying(255) NOT NULL,
    event_type character varying(100) NOT NULL,
    status character varying(50) NOT NULL,
    details jsonb DEFAULT '{}'::jsonb,
    created_at timestamp with time zone DEFAULT now() NOT NULL,
    CONSTRAINT sync_audit_status_check CHECK (((status)::text = ANY ((ARRAY['success'::character varying, 'failed'::character varying, 'skipped'::character varying])::text[])))
);


ALTER TABLE public.identity_sync_audit OWNER TO archie_dev;

--
-- Name: TABLE identity_sync_audit; Type: COMMENT; Schema: public; Owner: archie_dev
--

COMMENT ON TABLE public.identity_sync_audit IS 'Audit trail for WorkOS Directory Sync events';


--
-- Name: COLUMN identity_sync_audit.user_id; Type: COMMENT; Schema: public; Owner: archie_dev
--

COMMENT ON COLUMN public.identity_sync_audit.user_id IS 'WorkOS user ID or directory ID for bulk operations';


--
-- Name: COLUMN identity_sync_audit.event_type; Type: COMMENT; Schema: public; Owner: archie_dev
--

COMMENT ON COLUMN public.identity_sync_audit.event_type IS 'Type of sync event (user_created, user_updated, etc.)';


--
-- Name: COLUMN identity_sync_audit.status; Type: COMMENT; Schema: public; Owner: archie_dev
--

COMMENT ON COLUMN public.identity_sync_audit.status IS 'Sync operation status (success, failed, skipped)';


--
-- Name: COLUMN identity_sync_audit.details; Type: COMMENT; Schema: public; Owner: archie_dev
--

COMMENT ON COLUMN public.identity_sync_audit.details IS 'Additional event details in JSON format';


--
-- Name: low_confidence_messages; Type: VIEW; Schema: public; Owner: archie_dev
--

CREATE VIEW public.low_confidence_messages AS
 SELECT m.message_id,
    m.conversation_id,
    m.user_id,
    m.firm_id,
    m.content,
    m.confidence_score,
    m.model_used,
    m.created_at,
    c.title AS conversation_title
   FROM (public.messages m
     JOIN public.conversations c ON ((m.conversation_id = c.id)))
  WHERE (((m.role)::text = 'assistant'::text) AND (m.confidence_score IS NOT NULL) AND (m.confidence_score < 0.70))
  ORDER BY m.confidence_score, m.created_at DESC;


ALTER VIEW public.low_confidence_messages OWNER TO archie_dev;

--
-- Name: VIEW low_confidence_messages; Type: COMMENT; Schema: public; Owner: archie_dev
--

COMMENT ON VIEW public.low_confidence_messages IS 'Assistant messages with confidence score below 0.70 for quality review';


--
-- Name: messages_enriched; Type: VIEW; Schema: public; Owner: archie_dev
--

CREATE VIEW public.messages_enriched AS
 SELECT m.message_id,
    m.conversation_id,
    m.user_id,
    m.firm_id,
    m.role,
    m.content,
    m.confidence_score,
    m.model_used,
    m.metadata AS message_metadata,
    m.created_at,
    m.updated_at,
    COALESCE(json_agg(json_build_object('artifact_id', a.artifact_id, 'artifact_type', a.artifact_type, 'file_url', a.file_url, 'file_name', a.file_name, 'file_size', a.file_size, 'mime_type', a.mime_type, 'metadata', a.metadata, 'created_at', a.created_at)) FILTER (WHERE (a.artifact_id IS NOT NULL)), '[]'::json) AS artifacts,
    COALESCE(json_agg(json_build_object('citation_id', c.citation_id, 'source_url', c.source_url, 'title', c.title, 'snippet', c.snippet, 'relevance_score', c.relevance_score, 'metadata', c.metadata, 'created_at', c.created_at) ORDER BY c.relevance_score DESC NULLS LAST) FILTER (WHERE (c.citation_id IS NOT NULL)), '[]'::json) AS citations,
    count(DISTINCT a.artifact_id) AS artifact_count,
    count(DISTINCT c.citation_id) AS citation_count
   FROM ((public.messages m
     LEFT JOIN public.artifacts a ON ((m.message_id = a.message_id)))
     LEFT JOIN public.citations c ON ((m.message_id = c.message_id)))
  GROUP BY m.message_id, m.conversation_id, m.user_id, m.firm_id, m.role, m.content, m.confidence_score, m.model_used, m.metadata, m.created_at, m.updated_at;


ALTER VIEW public.messages_enriched OWNER TO archie_dev;

--
-- Name: VIEW messages_enriched; Type: COMMENT; Schema: public; Owner: archie_dev
--

COMMENT ON VIEW public.messages_enriched IS 'Messages with all artifacts and citations joined for efficient retrieval';


--
-- Name: pending_invitations_summary; Type: VIEW; Schema: public; Owner: archie_dev
--

CREATE VIEW public.pending_invitations_summary AS
 SELECT invited_user_id,
    count(*) AS pending_invitation_count,
    min(expires_at) AS earliest_expiration
   FROM public.conversation_shares cs
  WHERE (((status)::text = 'pending'::text) AND (expires_at > now()) AND (invited_user_id IS NOT NULL))
  GROUP BY invited_user_id;


ALTER VIEW public.pending_invitations_summary OWNER TO archie_dev;

--
-- Name: VIEW pending_invitations_summary; Type: COMMENT; Schema: public; Owner: archie_dev
--

COMMENT ON VIEW public.pending_invitations_summary IS 'Summary of pending invitations per user';


--
-- Name: queries; Type: TABLE; Schema: public; Owner: archie_dev
--

CREATE TABLE public.queries (
    id uuid DEFAULT public.uuid_generate_v4() NOT NULL,
    conversation_id uuid NOT NULL,
    user_id character varying(255) NOT NULL,
    query_text text NOT NULL,
    response_text text,
    status character varying(50) DEFAULT 'pending'::character varying NOT NULL,
    intent character varying(100),
    model_used character varying(100),
    processing_time_ms integer,
    tokens_used integer,
    created_at timestamp with time zone DEFAULT now() NOT NULL,
    completed_at timestamp with time zone,
    updated_at timestamp with time zone DEFAULT now() NOT NULL,
    metadata jsonb DEFAULT '{}'::jsonb NOT NULL,
    CONSTRAINT queries_processing_time_check CHECK (((processing_time_ms IS NULL) OR (processing_time_ms >= 0))),
    CONSTRAINT queries_status_check CHECK (((status)::text = ANY ((ARRAY['pending'::character varying, 'processing'::character varying, 'completed'::character varying, 'error'::character varying, 'interrupted'::character varying])::text[]))),
    CONSTRAINT queries_tokens_check CHECK (((tokens_used IS NULL) OR (tokens_used >= 0)))
);


ALTER TABLE public.queries OWNER TO archie_dev;

--
-- Name: TABLE queries; Type: COMMENT; Schema: public; Owner: archie_dev
--

COMMENT ON TABLE public.queries IS 'Individual queries with processing status and results';


--
-- Name: recent_queries_with_context; Type: VIEW; Schema: public; Owner: archie_dev
--

CREATE VIEW public.recent_queries_with_context AS
 SELECT q.id,
    q.conversation_id,
    q.user_id,
    q.query_text,
    q.response_text,
    q.status,
    q.intent,
    q.model_used,
    q.processing_time_ms,
    q.tokens_used,
    q.created_at,
    q.completed_at,
    c.title AS conversation_title,
    c.firm_id,
    u.email AS user_email,
    u.subscription_tier
   FROM ((public.queries q
     JOIN public.conversations c ON ((q.conversation_id = c.id)))
     JOIN public.users u ON (((q.user_id)::text = (u.user_id)::text)))
  ORDER BY q.created_at DESC;


ALTER VIEW public.recent_queries_with_context OWNER TO archie_dev;

--
-- Name: recent_sync_events; Type: VIEW; Schema: public; Owner: archie_dev
--

CREATE VIEW public.recent_sync_events AS
 SELECT id,
    user_id,
    event_type,
    status,
    details,
    created_at
   FROM public.identity_sync_audit
  ORDER BY created_at DESC
 LIMIT 1000;


ALTER VIEW public.recent_sync_events OWNER TO archie_dev;

--
-- Name: sync_statistics; Type: VIEW; Schema: public; Owner: archie_dev
--

CREATE VIEW public.sync_statistics AS
 SELECT event_type,
    status,
    count(*) AS event_count,
    max(created_at) AS last_occurred_at
   FROM public.identity_sync_audit
  GROUP BY event_type, status
  ORDER BY (max(created_at)) DESC;


ALTER VIEW public.sync_statistics OWNER TO archie_dev;

--
-- Name: team_members; Type: TABLE; Schema: public; Owner: archie_dev
--

CREATE TABLE public.team_members (
    id uuid DEFAULT public.uuid_generate_v4() NOT NULL,
    team_id uuid NOT NULL,
    user_id character varying(255) NOT NULL,
    team_role character varying(50) DEFAULT 'member'::character varying NOT NULL,
    added_by character varying(255) NOT NULL,
    added_at timestamp with time zone DEFAULT now() NOT NULL,
    status character varying(50) DEFAULT 'active'::character varying NOT NULL,
    metadata jsonb DEFAULT '{}'::jsonb,
    created_at timestamp with time zone DEFAULT now() NOT NULL,
    updated_at timestamp with time zone DEFAULT now() NOT NULL,
    CONSTRAINT chk_team_member_status CHECK (((status)::text = ANY ((ARRAY['active'::character varying, 'inactive'::character varying, 'removed'::character varying])::text[]))),
    CONSTRAINT chk_team_role CHECK (((team_role)::text = ANY ((ARRAY['admin'::character varying, 'member'::character varying])::text[])))
);


ALTER TABLE public.team_members OWNER TO archie_dev;

--
-- Name: TABLE team_members; Type: COMMENT; Schema: public; Owner: archie_dev
--

COMMENT ON TABLE public.team_members IS 'Associates users with teams for group-based conversation sharing';


--
-- Name: teams; Type: TABLE; Schema: public; Owner: archie_dev
--

CREATE TABLE public.teams (
    id uuid DEFAULT public.uuid_generate_v4() NOT NULL,
    firm_id character varying(255) NOT NULL,
    name character varying(255) NOT NULL,
    description text,
    created_by character varying(255) NOT NULL,
    status character varying(50) DEFAULT 'active'::character varying NOT NULL,
    metadata jsonb DEFAULT '{}'::jsonb,
    created_at timestamp with time zone DEFAULT now() NOT NULL,
    updated_at timestamp with time zone DEFAULT now() NOT NULL,
    CONSTRAINT chk_team_status CHECK (((status)::text = ANY ((ARRAY['active'::character varying, 'archived'::character varying, 'deleted'::character varying])::text[])))
);


ALTER TABLE public.teams OWNER TO archie_dev;

--
-- Name: TABLE teams; Type: COMMENT; Schema: public; Owner: archie_dev
--

COMMENT ON TABLE public.teams IS 'Pre-defined groups of users for bulk conversation sharing';


--
-- Name: test_rls_docs; Type: TABLE; Schema: public; Owner: archie_dev
--

CREATE TABLE public.test_rls_docs (
    id uuid NOT NULL,
    firm_id uuid NOT NULL,
    title text NOT NULL
);


ALTER TABLE public.test_rls_docs OWNER TO archie_dev;

--
-- Name: user_query_statistics; Type: VIEW; Schema: public; Owner: archie_dev
--

CREATE VIEW public.user_query_statistics AS
 SELECT u.user_id,
    u.email,
    u.subscription_tier,
    count(DISTINCT c.id) AS total_conversations,
    count(q.id) AS total_queries,
    count(
        CASE
            WHEN ((q.status)::text = 'completed'::text) THEN 1
            ELSE NULL::integer
        END) AS completed_queries,
    count(
        CASE
            WHEN ((q.status)::text = 'error'::text) THEN 1
            ELSE NULL::integer
        END) AS failed_queries,
    avg(
        CASE
            WHEN (q.processing_time_ms IS NOT NULL) THEN q.processing_time_ms
            ELSE NULL::integer
        END) AS avg_processing_time_ms,
    sum(COALESCE(q.tokens_used, 0)) AS total_tokens_used,
    avg(f.rating) AS avg_feedback_rating,
    count(f.id) AS total_feedback
   FROM (((public.users u
     LEFT JOIN public.conversations c ON (((u.user_id)::text = (c.user_id)::text)))
     LEFT JOIN public.queries q ON ((c.id = q.conversation_id)))
     LEFT JOIN public.feedback f ON ((q.id = f.query_id)))
  GROUP BY u.user_id, u.email, u.subscription_tier;


ALTER VIEW public.user_query_statistics OWNER TO archie_dev;

--
-- Data for Name: alembic_version; Type: TABLE DATA; Schema: public; Owner: archie_dev
--

COPY public.alembic_version (version_num) FROM stdin;
001_initial_schema
\.


--
-- Data for Name: artifacts; Type: TABLE DATA; Schema: public; Owner: archie_dev
--

COPY public.artifacts (artifact_id, message_id, artifact_type, file_url, file_name, file_size, mime_type, metadata, created_at) FROM stdin;
\.


--
-- Data for Name: citations; Type: TABLE DATA; Schema: public; Owner: archie_dev
--

COPY public.citations (citation_id, message_id, source_url, title, snippet, relevance_score, metadata, created_at) FROM stdin;
\.


--
-- Data for Name: conversation_archive_log; Type: TABLE DATA; Schema: public; Owner: archie_dev
--

COPY public.conversation_archive_log (id, conversation_id, user_id, action, reason, previous_status, new_status, created_at, metadata) FROM stdin;
\.


--
-- Data for Name: conversation_participants; Type: TABLE DATA; Schema: public; Owner: archie_dev
--

COPY public.conversation_participants (id, conversation_id, user_id, firm_id, permissions, role, added_by, added_at, last_accessed_at, status, metadata, created_at, updated_at) FROM stdin;
3233b595-a6c1-43ef-a385-7f4e6e62dbe1	6697d60d-8110-4cba-a777-5db05819963b	user_12345	test_firm	15	owner	user_12345	2025-11-26 11:47:57.279993+00	\N	active	{}	2025-11-26 11:47:57.279993+00	2025-11-26 11:47:57.279993+00
8d31aa0f-c8de-4927-88c3-3bba36c5c90f	a1d8ff75-b2da-4737-9cd8-5953c14f741b	user_12345	test_firm	15	owner	user_12345	2025-11-26 11:47:57.279993+00	\N	active	{}	2025-11-26 11:47:57.279993+00	2025-11-26 11:47:57.279993+00
ae8350fa-7d33-45bc-b543-da95b054ad59	41ce00e6-f0ce-4b44-a17d-dfb7ba35982d	user_12345	test_firm	15	owner	user_12345	2025-11-26 11:47:57.279993+00	\N	active	{}	2025-11-26 11:47:57.279993+00	2025-11-26 11:47:57.279993+00
891fee45-737c-4dda-a77a-8d21c22ea8ac	7ed8d817-e841-4681-903a-132cd82ed9fe	user_12345	test_firm	15	owner	user_12345	2025-11-26 11:47:57.279993+00	\N	active	{}	2025-11-26 11:47:57.279993+00	2025-11-26 11:47:57.279993+00
ffad380f-4a9e-4c7e-b46a-41d04debf9cc	550e8400-e29b-41d4-a716-446655440000	user_12345	test_firm	15	owner	user_12345	2025-11-26 11:47:57.279993+00	\N	active	{}	2025-11-26 11:47:57.279993+00	2025-11-26 11:47:57.279993+00
f2d7feda-5cff-47ff-bb9f-2f8c8e9136e6	4df9557a-d089-4fa2-81ef-c1f639daf402	user_01JDZ8W2YAG96EGGQ872CF1ZWF	org_01JEPW1M88DENBJJMA8P4P013P	15	owner	user_01JDZ8W2YAG96EGGQ872CF1ZWF	2025-11-26 11:47:57.279993+00	\N	active	{}	2025-11-26 11:47:57.279993+00	2025-11-26 11:47:57.279993+00
3db8d04a-0908-4fe0-a72d-3260332fc62d	d023a0ed-d80e-409f-83a4-1347f1f9cad1	user_01JDZ8W2YAG96EGGQ872CF1ZWF	org_01JEPW1M88DENBJJMA8P4P013P	15	owner	user_01JDZ8W2YAG96EGGQ872CF1ZWF	2025-11-26 11:47:57.279993+00	\N	active	{}	2025-11-26 11:47:57.279993+00	2025-11-26 11:47:57.279993+00
0d02fb96-3c60-45ad-9627-f76993026519	b6cb93da-7d31-49af-8335-3cea87e62572	user_01JDZ8W2YAG96EGGQ872CF1ZWF	org_01JEPW1M88DENBJJMA8P4P013P	15	owner	user_01JDZ8W2YAG96EGGQ872CF1ZWF	2025-11-26 11:47:57.279993+00	\N	active	{}	2025-11-26 11:47:57.279993+00	2025-11-26 11:47:57.279993+00
c2e2cf47-5053-4573-87ef-cd9fc52cf192	61a6b4cb-bf34-4c45-acc2-39cef19b9c74	user_01JDZ8W2YAG96EGGQ872CF1ZWF	org_01JEPW1M88DENBJJMA8P4P013P	15	owner	user_01JDZ8W2YAG96EGGQ872CF1ZWF	2025-11-26 11:47:57.279993+00	\N	active	{}	2025-11-26 11:47:57.279993+00	2025-11-26 11:47:57.279993+00
48d650e0-4293-49bb-bddf-5d6fde0164db	8a7fd25e-6929-4ec0-b193-d299c4ff17c8	user_01JDZ8W2YAG96EGGQ872CF1ZWF	org_01JEPW1M88DENBJJMA8P4P013P	15	owner	user_01JDZ8W2YAG96EGGQ872CF1ZWF	2025-11-26 11:47:57.279993+00	\N	active	{}	2025-11-26 11:47:57.279993+00	2025-11-26 11:47:57.279993+00
102a5130-e88c-4d9c-8740-f566dd447857	046ec146-fc0a-4ded-be21-fd36e82223ce	user_01JDZ8W2YAG96EGGQ872CF1ZWF	org_01JEPW1M88DENBJJMA8P4P013P	15	owner	user_01JDZ8W2YAG96EGGQ872CF1ZWF	2025-11-26 11:47:57.279993+00	\N	active	{}	2025-11-26 11:47:57.279993+00	2025-11-26 11:47:57.279993+00
9fef4433-e615-4e9f-93d9-20c89ae011c3	e6955cab-8102-4554-abf5-6056dfa88fdc	user_01JDZ8W2YAG96EGGQ872CF1ZWF	org_01JEPW1M88DENBJJMA8P4P013P	15	owner	user_01JDZ8W2YAG96EGGQ872CF1ZWF	2025-11-26 11:47:57.279993+00	\N	active	{}	2025-11-26 11:47:57.279993+00	2025-11-26 11:47:57.279993+00
f6165077-941c-4a50-981a-3bec28b685d9	4d786842-28b9-488b-a939-7cdbafa6aa1b	user_01JDZ8W2YAG96EGGQ872CF1ZWF	org_01JEPW1M88DENBJJMA8P4P013P	15	owner	user_01JDZ8W2YAG96EGGQ872CF1ZWF	2025-11-26 11:47:57.279993+00	\N	active	{}	2025-11-26 11:47:57.279993+00	2025-11-26 11:47:57.279993+00
dea05984-59f0-44ff-8950-a97794db08fe	e85ef543-f962-4226-a359-304f47e606f2	user_01JDZ8W2YAG96EGGQ872CF1ZWF	org_01JEPW1M88DENBJJMA8P4P013P	15	owner	user_01JDZ8W2YAG96EGGQ872CF1ZWF	2025-11-26 11:47:57.279993+00	\N	active	{}	2025-11-26 11:47:57.279993+00	2025-11-26 11:47:57.279993+00
2319343b-cd22-48ca-92c2-3970d39695ff	61b2ff35-6574-4c17-b4b8-50db0f5106b6	user_01JDZ8W2YAG96EGGQ872CF1ZWF	org_01JEPW1M88DENBJJMA8P4P013P	15	owner	user_01JDZ8W2YAG96EGGQ872CF1ZWF	2025-11-26 11:47:57.279993+00	\N	active	{}	2025-11-26 11:47:57.279993+00	2025-11-26 11:47:57.279993+00
9ab94629-e72c-4b42-b855-95ce7f7935b0	b823d573-dd6e-4e11-bdbd-cdb7789f2240	user_01JDZ8W2YAG96EGGQ872CF1ZWF	org_01JEPW1M88DENBJJMA8P4P013P	15	owner	user_01JDZ8W2YAG96EGGQ872CF1ZWF	2025-11-26 11:47:57.279993+00	\N	active	{}	2025-11-26 11:47:57.279993+00	2025-11-26 11:47:57.279993+00
fc3e416b-8788-4e8d-b856-b11f3a14b5bb	1f891257-68de-4077-9325-16a3cddcd154	user_01JDZ8W2YAG96EGGQ872CF1ZWF	org_01JEPW1M88DENBJJMA8P4P013P	15	owner	user_01JDZ8W2YAG96EGGQ872CF1ZWF	2025-11-26 11:47:57.279993+00	\N	active	{}	2025-11-26 11:47:57.279993+00	2025-11-26 11:47:57.279993+00
60272b76-106d-47ae-a304-7d07056856ca	3cb88d41-7f87-48f7-83c1-f4168ab808d8	user_01JDZ8W2YAG96EGGQ872CF1ZWF	org_01JEPW1M88DENBJJMA8P4P013P	15	owner	user_01JDZ8W2YAG96EGGQ872CF1ZWF	2025-11-26 11:47:57.279993+00	\N	active	{}	2025-11-26 11:47:57.279993+00	2025-11-26 11:47:57.279993+00
5a22f305-eb26-4e0f-bfa3-75f6bdbff711	5250dc82-1d7f-48e7-a9cd-023514008707	user_01JDZ8W2YAG96EGGQ872CF1ZWF	org_01JEPW1M88DENBJJMA8P4P013P	15	owner	user_01JDZ8W2YAG96EGGQ872CF1ZWF	2025-11-26 11:47:57.279993+00	\N	active	{}	2025-11-26 11:47:57.279993+00	2025-11-26 11:47:57.279993+00
833a964a-24a3-4c42-affe-47d1ef8028ff	342fc661-342a-4746-af2a-c8e1268a9f58	user_01JDZ8W2YAG96EGGQ872CF1ZWF	org_01JEPW1M88DENBJJMA8P4P013P	15	owner	user_01JDZ8W2YAG96EGGQ872CF1ZWF	2025-11-26 11:47:57.279993+00	\N	active	{}	2025-11-26 11:47:57.279993+00	2025-11-26 11:47:57.279993+00
8061d3cc-89af-42e4-8033-101de13f134c	07db14c2-8950-4b35-aab0-f401e6ace9bf	user_01JDZ8W2YAG96EGGQ872CF1ZWF	org_01JEPW1M88DENBJJMA8P4P013P	15	owner	user_01JDZ8W2YAG96EGGQ872CF1ZWF	2025-11-26 11:47:57.279993+00	\N	active	{}	2025-11-26 11:47:57.279993+00	2025-11-26 11:47:57.279993+00
21fb57c8-dbc5-4a23-9e73-395816c31c57	b4955bad-185c-4e0a-85d5-1165d141bf18	user_01JDZ8W2YAG96EGGQ872CF1ZWF	org_01JEPW1M88DENBJJMA8P4P013P	15	owner	user_01JDZ8W2YAG96EGGQ872CF1ZWF	2025-11-26 11:47:57.279993+00	\N	active	{}	2025-11-26 11:47:57.279993+00	2025-11-26 11:47:57.279993+00
8cd70ace-b5f9-48e2-8e15-51af16e10491	0c18867c-ba51-485f-80a0-ce008750e5f4	user_01JDZ8W2YAG96EGGQ872CF1ZWF	org_01JEPW1M88DENBJJMA8P4P013P	15	owner	user_01JDZ8W2YAG96EGGQ872CF1ZWF	2025-11-26 11:47:57.279993+00	\N	active	{}	2025-11-26 11:47:57.279993+00	2025-11-26 11:47:57.279993+00
55bc1a12-0db1-4f67-ab44-4ac714220d61	59bd1e95-5531-4127-859f-ed01d764086d	user_01JDZ8W2YAG96EGGQ872CF1ZWF	org_01JEPW1M88DENBJJMA8P4P013P	15	owner	user_01JDZ8W2YAG96EGGQ872CF1ZWF	2025-11-26 11:47:57.279993+00	\N	active	{}	2025-11-26 11:47:57.279993+00	2025-11-26 11:47:57.279993+00
d4d53eec-d36c-4030-9733-2d6ec13594d3	b79d927d-f04e-4f13-ba56-90d513a46635	user_01JDZ8W2YAG96EGGQ872CF1ZWF	org_01JEPW1M88DENBJJMA8P4P013P	15	owner	user_01JDZ8W2YAG96EGGQ872CF1ZWF	2025-11-26 11:47:57.279993+00	\N	active	{}	2025-11-26 11:47:57.279993+00	2025-11-26 11:47:57.279993+00
85e8bb27-c0fc-46db-9083-d149c93a4f17	69ddbf01-7e6a-48b0-bd45-359d441e34d0	user_01JDZ8W2YAG96EGGQ872CF1ZWF	org_01JEPW1M88DENBJJMA8P4P013P	15	owner	user_01JDZ8W2YAG96EGGQ872CF1ZWF	2025-11-26 11:47:57.279993+00	\N	active	{}	2025-11-26 11:47:57.279993+00	2025-11-26 11:47:57.279993+00
6f4e736d-a39c-44c2-8f8f-6ec6465e06e2	8c8a2b16-8f0d-4560-be01-59d784a51eca	user_01JDZ8W2YAG96EGGQ872CF1ZWF	org_01JEPW1M88DENBJJMA8P4P013P	15	owner	user_01JDZ8W2YAG96EGGQ872CF1ZWF	2025-11-26 11:47:57.279993+00	\N	active	{}	2025-11-26 11:47:57.279993+00	2025-11-26 11:47:57.279993+00
d0c03e6e-5ce1-4883-a88e-4636ecb71228	868cb742-1314-453a-a41c-cd0ca9d51465	user_01JDZ8W2YAG96EGGQ872CF1ZWF	org_01JEPW1M88DENBJJMA8P4P013P	15	owner	user_01JDZ8W2YAG96EGGQ872CF1ZWF	2025-11-26 11:47:57.279993+00	\N	active	{}	2025-11-26 11:47:57.279993+00	2025-11-26 11:47:57.279993+00
0a664c79-bf8a-4d28-9904-9a1543c1fe28	8df2da1b-0b84-4595-a7a8-27c9af6a228b	user_01JDZ8W2YAG96EGGQ872CF1ZWF	org_01JEPW1M88DENBJJMA8P4P013P	15	owner	user_01JDZ8W2YAG96EGGQ872CF1ZWF	2025-11-26 11:47:57.279993+00	\N	active	{}	2025-11-26 11:47:57.279993+00	2025-11-26 11:47:57.279993+00
c5050046-5090-410e-9286-0d0bb18b85ab	ebae1b0a-c00f-424a-aa47-b6f39b890f09	user_01JDZ8W2YAG96EGGQ872CF1ZWF	org_01JEPW1M88DENBJJMA8P4P013P	15	owner	user_01JDZ8W2YAG96EGGQ872CF1ZWF	2025-11-26 11:47:57.279993+00	\N	active	{}	2025-11-26 11:47:57.279993+00	2025-11-26 11:47:57.279993+00
fdc5df99-f66c-4e95-b122-dda320fd5840	36f143f5-1089-4205-b386-def97a476efd	user_01JDZ8W2YAG96EGGQ872CF1ZWF	org_01JEPW1M88DENBJJMA8P4P013P	15	owner	user_01JDZ8W2YAG96EGGQ872CF1ZWF	2025-11-26 11:47:57.279993+00	\N	active	{}	2025-11-26 11:47:57.279993+00	2025-11-26 11:47:57.279993+00
decd6e1f-40eb-4ad4-af11-f9b7ea2b63b1	e1af7205-a99e-4a02-bd11-95dcd5cb069d	user_01JDZ8W2YAG96EGGQ872CF1ZWF	org_01JEPW1M88DENBJJMA8P4P013P	15	owner	user_01JDZ8W2YAG96EGGQ872CF1ZWF	2025-11-26 11:47:57.279993+00	\N	active	{}	2025-11-26 11:47:57.279993+00	2025-11-26 11:47:57.279993+00
f67068bc-78bd-4e71-8ded-245bc7899ed1	aa2d935c-0347-43fa-ae3d-bb2cd562ae56	user_01JDZ8W2YAG96EGGQ872CF1ZWF	org_01JEPW1M88DENBJJMA8P4P013P	15	owner	user_01JDZ8W2YAG96EGGQ872CF1ZWF	2025-11-26 11:47:57.279993+00	\N	active	{}	2025-11-26 11:47:57.279993+00	2025-11-26 11:47:57.279993+00
a2c882cf-1700-4cae-840e-350a277eccd9	67c266b0-8707-4468-81d6-e7bb4f6f5872	user_01JDZ8W2YAG96EGGQ872CF1ZWF	org_01JEPW1M88DENBJJMA8P4P013P	15	owner	user_01JDZ8W2YAG96EGGQ872CF1ZWF	2025-11-26 11:47:57.279993+00	\N	active	{}	2025-11-26 11:47:57.279993+00	2025-11-26 11:47:57.279993+00
1f8725b8-fa4c-4474-b061-ba4bbcf3b8d0	e084595a-9450-4ad9-b59e-e2746de1c7e8	user_01JDZ8W2YAG96EGGQ872CF1ZWF	org_01JEPW1M88DENBJJMA8P4P013P	15	owner	user_01JDZ8W2YAG96EGGQ872CF1ZWF	2025-11-26 11:47:57.279993+00	\N	active	{}	2025-11-26 11:47:57.279993+00	2025-11-26 11:47:57.279993+00
16798b8b-ccc6-4073-b2e6-02bcd30c4fed	3d8cf9c5-f2c7-4c9b-8ec3-681df381e66a	user_01JDZ8W2YAG96EGGQ872CF1ZWF	org_01JEPW1M88DENBJJMA8P4P013P	15	owner	user_01JDZ8W2YAG96EGGQ872CF1ZWF	2025-11-26 11:47:57.279993+00	\N	active	{}	2025-11-26 11:47:57.279993+00	2025-11-26 11:47:57.279993+00
7cafa3fd-b7be-4969-9785-42d5ae621847	d0edb098-8143-4f5f-a68d-68dfb0f7efc3	user_01JDZ8W2YAG96EGGQ872CF1ZWF	org_01JEPW1M88DENBJJMA8P4P013P	15	owner	user_01JDZ8W2YAG96EGGQ872CF1ZWF	2025-11-26 11:47:57.279993+00	\N	active	{}	2025-11-26 11:47:57.279993+00	2025-11-26 11:47:57.279993+00
\.


--
-- Data for Name: conversation_shares; Type: TABLE DATA; Schema: public; Owner: archie_dev
--

COPY public.conversation_shares (id, conversation_id, invited_by, invited_user_id, invited_team_id, granted_permissions, granted_role, status, invitation_message, invitation_token, expires_at, responded_at, response_message, created_at, updated_at, metadata) FROM stdin;
\.


--
-- Data for Name: conversations; Type: TABLE DATA; Schema: public; Owner: archie_dev
--

COPY public.conversations (id, user_id, firm_id, title, created_at, updated_at, metadata, status, owner_id, archived_at, archived_by) FROM stdin;
6697d60d-8110-4cba-a777-5db05819963b	user_12345	test_firm	Chat 2025-11-24 03:20	2025-11-24 03:20:03.817824+00	2025-11-26 11:47:57.279993+00	{}	active	user_12345	\N	\N
a1d8ff75-b2da-4737-9cd8-5953c14f741b	user_12345	test_firm	Chat 2025-11-24 03:24	2025-11-24 03:24:29.998947+00	2025-11-26 11:47:57.279993+00	{}	active	user_12345	\N	\N
41ce00e6-f0ce-4b44-a17d-dfb7ba35982d	user_12345	test_firm	Chat 2025-11-24 03:27	2025-11-24 03:27:08.818815+00	2025-11-26 11:47:57.279993+00	{}	active	user_12345	\N	\N
7ed8d817-e841-4681-903a-132cd82ed9fe	user_12345	test_firm	Chat 2025-11-24 03:28	2025-11-24 03:28:24.834152+00	2025-11-26 11:47:57.279993+00	{}	active	user_12345	\N	\N
550e8400-e29b-41d4-a716-446655440000	user_12345	test_firm	Chat 2025-11-24 03:35	2025-11-24 03:35:08.404915+00	2025-11-26 11:47:57.279993+00	{}	active	user_12345	\N	\N
4df9557a-d089-4fa2-81ef-c1f639daf402	user_01JDZ8W2YAG96EGGQ872CF1ZWF	org_01JEPW1M88DENBJJMA8P4P013P	Chat 2025-11-24 03:36	2025-11-24 03:36:16.027545+00	2025-11-26 11:47:57.279993+00	{}	active	user_01JDZ8W2YAG96EGGQ872CF1ZWF	\N	\N
d023a0ed-d80e-409f-83a4-1347f1f9cad1	user_01JDZ8W2YAG96EGGQ872CF1ZWF	org_01JEPW1M88DENBJJMA8P4P013P	CLI Conversation - 2025-11-24 13:20	2025-11-24 13:20:40.611696+00	2025-11-26 11:47:57.279993+00	{"source": "cli", "auto_created": true}	active	user_01JDZ8W2YAG96EGGQ872CF1ZWF	\N	\N
b6cb93da-7d31-49af-8335-3cea87e62572	user_01JDZ8W2YAG96EGGQ872CF1ZWF	org_01JEPW1M88DENBJJMA8P4P013P	CLI Conversation - 2025-11-24 13:24	2025-11-24 13:24:35.046284+00	2025-11-26 11:47:57.279993+00	{"source": "cli", "auto_created": true}	active	user_01JDZ8W2YAG96EGGQ872CF1ZWF	\N	\N
61a6b4cb-bf34-4c45-acc2-39cef19b9c74	user_01JDZ8W2YAG96EGGQ872CF1ZWF	org_01JEPW1M88DENBJJMA8P4P013P	CLI Conversation - 2025-11-24 13:58	2025-11-24 13:58:22.770736+00	2025-11-26 11:47:57.279993+00	"{\\"source\\": \\"cli\\", \\"auto_created\\": true}"	active	user_01JDZ8W2YAG96EGGQ872CF1ZWF	\N	\N
8a7fd25e-6929-4ec0-b193-d299c4ff17c8	user_01JDZ8W2YAG96EGGQ872CF1ZWF	org_01JEPW1M88DENBJJMA8P4P013P	CLI Conversation - 2025-11-24 13:59	2025-11-24 13:59:36.142452+00	2025-11-26 11:47:57.279993+00	{"source": "cli", "auto_created": true}	active	user_01JDZ8W2YAG96EGGQ872CF1ZWF	\N	\N
046ec146-fc0a-4ded-be21-fd36e82223ce	user_01JDZ8W2YAG96EGGQ872CF1ZWF	org_01JEPW1M88DENBJJMA8P4P013P	CLI Conversation - 2025-11-24 14:25	2025-11-24 14:25:13.128172+00	2025-11-26 11:47:57.279993+00	{"source": "cli", "auto_created": true}	active	user_01JDZ8W2YAG96EGGQ872CF1ZWF	\N	\N
e6955cab-8102-4554-abf5-6056dfa88fdc	user_01JDZ8W2YAG96EGGQ872CF1ZWF	org_01JEPW1M88DENBJJMA8P4P013P	CLI Conversation - 2025-11-24 14:27	2025-11-24 14:27:26.861653+00	2025-11-26 11:47:57.279993+00	{"source": "cli", "auto_created": true}	active	user_01JDZ8W2YAG96EGGQ872CF1ZWF	\N	\N
4d786842-28b9-488b-a939-7cdbafa6aa1b	user_01JDZ8W2YAG96EGGQ872CF1ZWF	org_01JEPW1M88DENBJJMA8P4P013P	CLI Conversation - 2025-11-24 14:29	2025-11-24 14:29:21.976524+00	2025-11-26 11:47:57.279993+00	{"source": "cli", "auto_created": true}	active	user_01JDZ8W2YAG96EGGQ872CF1ZWF	\N	\N
e85ef543-f962-4226-a359-304f47e606f2	user_01JDZ8W2YAG96EGGQ872CF1ZWF	org_01JEPW1M88DENBJJMA8P4P013P	CLI Conversation - 2025-11-24 14:33	2025-11-24 14:33:28.409674+00	2025-11-26 11:47:57.279993+00	{"source": "cli", "auto_created": true}	active	user_01JDZ8W2YAG96EGGQ872CF1ZWF	\N	\N
61b2ff35-6574-4c17-b4b8-50db0f5106b6	user_01JDZ8W2YAG96EGGQ872CF1ZWF	org_01JEPW1M88DENBJJMA8P4P013P	CLI Conversation - 2025-11-24 14:51	2025-11-24 14:51:27.128447+00	2025-11-26 11:47:57.279993+00	{"source": "cli", "auto_created": true}	active	user_01JDZ8W2YAG96EGGQ872CF1ZWF	\N	\N
b823d573-dd6e-4e11-bdbd-cdb7789f2240	user_01JDZ8W2YAG96EGGQ872CF1ZWF	org_01JEPW1M88DENBJJMA8P4P013P	CLI Conversation - 2025-11-24 15:04	2025-11-24 15:04:49.697954+00	2025-11-26 11:47:57.279993+00	{"source": "cli", "auto_created": true}	active	user_01JDZ8W2YAG96EGGQ872CF1ZWF	\N	\N
1f891257-68de-4077-9325-16a3cddcd154	user_01JDZ8W2YAG96EGGQ872CF1ZWF	org_01JEPW1M88DENBJJMA8P4P013P	CLI Conversation - 2025-11-24 16:08	2025-11-24 16:08:44.966017+00	2025-11-26 11:47:57.279993+00	{"source": "cli", "auto_created": true}	active	user_01JDZ8W2YAG96EGGQ872CF1ZWF	\N	\N
3cb88d41-7f87-48f7-83c1-f4168ab808d8	user_01JDZ8W2YAG96EGGQ872CF1ZWF	org_01JEPW1M88DENBJJMA8P4P013P	CLI Conversation - 2025-11-24 16:14	2025-11-24 16:14:04.774348+00	2025-11-26 11:47:57.279993+00	{"source": "cli", "auto_created": true}	active	user_01JDZ8W2YAG96EGGQ872CF1ZWF	\N	\N
5250dc82-1d7f-48e7-a9cd-023514008707	user_01JDZ8W2YAG96EGGQ872CF1ZWF	org_01JEPW1M88DENBJJMA8P4P013P	CLI Conversation - 2025-11-24 16:20	2025-11-24 16:20:34.779294+00	2025-11-26 11:47:57.279993+00	{"source": "cli", "auto_created": true}	active	user_01JDZ8W2YAG96EGGQ872CF1ZWF	\N	\N
342fc661-342a-4746-af2a-c8e1268a9f58	user_01JDZ8W2YAG96EGGQ872CF1ZWF	org_01JEPW1M88DENBJJMA8P4P013P	CLI Conversation - 2025-11-24 16:21	2025-11-24 16:21:01.181214+00	2025-11-26 11:47:57.279993+00	{"source": "cli", "auto_created": true}	active	user_01JDZ8W2YAG96EGGQ872CF1ZWF	\N	\N
07db14c2-8950-4b35-aab0-f401e6ace9bf	user_01JDZ8W2YAG96EGGQ872CF1ZWF	org_01JEPW1M88DENBJJMA8P4P013P	CLI Conversation - 2025-11-24 16:23	2025-11-24 16:23:45.91979+00	2025-11-26 11:47:57.279993+00	{"source": "cli", "auto_created": true}	active	user_01JDZ8W2YAG96EGGQ872CF1ZWF	\N	\N
b4955bad-185c-4e0a-85d5-1165d141bf18	user_01JDZ8W2YAG96EGGQ872CF1ZWF	org_01JEPW1M88DENBJJMA8P4P013P	CLI Conversation - 2025-11-24 16:31	2025-11-24 16:31:21.579446+00	2025-11-26 11:47:57.279993+00	{"source": "cli", "auto_created": true}	active	user_01JDZ8W2YAG96EGGQ872CF1ZWF	\N	\N
0c18867c-ba51-485f-80a0-ce008750e5f4	user_01JDZ8W2YAG96EGGQ872CF1ZWF	org_01JEPW1M88DENBJJMA8P4P013P	CLI Conversation - 2025-11-24 16:33	2025-11-24 16:33:19.394114+00	2025-11-26 11:47:57.279993+00	{"source": "cli", "auto_created": true}	active	user_01JDZ8W2YAG96EGGQ872CF1ZWF	\N	\N
59bd1e95-5531-4127-859f-ed01d764086d	user_01JDZ8W2YAG96EGGQ872CF1ZWF	org_01JEPW1M88DENBJJMA8P4P013P	CLI Conversation - 2025-11-24 16:38	2025-11-24 16:38:57.626403+00	2025-11-26 11:47:57.279993+00	{"source": "cli", "auto_created": true}	active	user_01JDZ8W2YAG96EGGQ872CF1ZWF	\N	\N
b79d927d-f04e-4f13-ba56-90d513a46635	user_01JDZ8W2YAG96EGGQ872CF1ZWF	org_01JEPW1M88DENBJJMA8P4P013P	CLI Conversation - 2025-11-24 16:44	2025-11-24 16:44:24.400066+00	2025-11-26 11:47:57.279993+00	{"source": "cli", "auto_created": true}	active	user_01JDZ8W2YAG96EGGQ872CF1ZWF	\N	\N
69ddbf01-7e6a-48b0-bd45-359d441e34d0	user_01JDZ8W2YAG96EGGQ872CF1ZWF	org_01JEPW1M88DENBJJMA8P4P013P	CLI Conversation - 2025-11-24 16:46	2025-11-24 16:46:00.04208+00	2025-11-26 11:47:57.279993+00	{"source": "cli", "auto_created": true}	active	user_01JDZ8W2YAG96EGGQ872CF1ZWF	\N	\N
8c8a2b16-8f0d-4560-be01-59d784a51eca	user_01JDZ8W2YAG96EGGQ872CF1ZWF	org_01JEPW1M88DENBJJMA8P4P013P	CLI Conversation - 2025-11-24 16:50	2025-11-24 16:50:07.517578+00	2025-11-26 11:47:57.279993+00	{"source": "cli", "auto_created": true}	active	user_01JDZ8W2YAG96EGGQ872CF1ZWF	\N	\N
868cb742-1314-453a-a41c-cd0ca9d51465	user_01JDZ8W2YAG96EGGQ872CF1ZWF	org_01JEPW1M88DENBJJMA8P4P013P	CLI Conversation - 2025-11-24 16:55	2025-11-24 16:55:03.109574+00	2025-11-26 11:47:57.279993+00	{"source": "cli", "auto_created": true}	active	user_01JDZ8W2YAG96EGGQ872CF1ZWF	\N	\N
8df2da1b-0b84-4595-a7a8-27c9af6a228b	user_01JDZ8W2YAG96EGGQ872CF1ZWF	org_01JEPW1M88DENBJJMA8P4P013P	CLI Conversation - 2025-11-24 16:57	2025-11-24 16:57:31.414768+00	2025-11-26 11:47:57.279993+00	{"source": "cli", "auto_created": true}	active	user_01JDZ8W2YAG96EGGQ872CF1ZWF	\N	\N
ebae1b0a-c00f-424a-aa47-b6f39b890f09	user_01JDZ8W2YAG96EGGQ872CF1ZWF	org_01JEPW1M88DENBJJMA8P4P013P	CLI Conversation - 2025-11-24 16:58	2025-11-24 16:58:59.563031+00	2025-11-26 11:47:57.279993+00	{"source": "cli", "auto_created": true}	active	user_01JDZ8W2YAG96EGGQ872CF1ZWF	\N	\N
36f143f5-1089-4205-b386-def97a476efd	user_01JDZ8W2YAG96EGGQ872CF1ZWF	org_01JEPW1M88DENBJJMA8P4P013P	CLI Conversation - 2025-11-24 18:22	2025-11-24 18:22:12.219215+00	2025-11-26 11:47:57.279993+00	{"source": "cli", "auto_created": true}	active	user_01JDZ8W2YAG96EGGQ872CF1ZWF	\N	\N
e1af7205-a99e-4a02-bd11-95dcd5cb069d	user_01JDZ8W2YAG96EGGQ872CF1ZWF	org_01JEPW1M88DENBJJMA8P4P013P	CLI Conversation - 2025-11-24 20:47	2025-11-24 20:47:52.089887+00	2025-11-26 11:47:57.279993+00	{"source": "cli", "auto_created": true}	active	user_01JDZ8W2YAG96EGGQ872CF1ZWF	\N	\N
aa2d935c-0347-43fa-ae3d-bb2cd562ae56	user_01JDZ8W2YAG96EGGQ872CF1ZWF	org_01JEPW1M88DENBJJMA8P4P013P	CLI Conversation - 2025-11-24 20:51	2025-11-24 20:51:59.96024+00	2025-11-26 11:47:57.279993+00	{"source": "cli", "auto_created": true}	active	user_01JDZ8W2YAG96EGGQ872CF1ZWF	\N	\N
67c266b0-8707-4468-81d6-e7bb4f6f5872	user_01JDZ8W2YAG96EGGQ872CF1ZWF	org_01JEPW1M88DENBJJMA8P4P013P	CLI Conversation - 2025-11-24 20:56	2025-11-24 20:56:10.860237+00	2025-11-26 11:47:57.279993+00	{"source": "cli", "auto_created": true}	active	user_01JDZ8W2YAG96EGGQ872CF1ZWF	\N	\N
e084595a-9450-4ad9-b59e-e2746de1c7e8	user_01JDZ8W2YAG96EGGQ872CF1ZWF	org_01JEPW1M88DENBJJMA8P4P013P	CLI Conversation - 2025-11-25 00:36	2025-11-25 00:36:41.198116+00	2025-11-26 11:47:57.279993+00	{"source": "cli", "auto_created": true}	active	user_01JDZ8W2YAG96EGGQ872CF1ZWF	\N	\N
3d8cf9c5-f2c7-4c9b-8ec3-681df381e66a	user_01JDZ8W2YAG96EGGQ872CF1ZWF	org_01JEPW1M88DENBJJMA8P4P013P	CLI Conversation - 2025-11-25 01:15	2025-11-25 01:15:03.545556+00	2025-11-26 11:47:57.279993+00	{"source": "cli", "auto_created": true}	active	user_01JDZ8W2YAG96EGGQ872CF1ZWF	\N	\N
d0edb098-8143-4f5f-a68d-68dfb0f7efc3	user_01JDZ8W2YAG96EGGQ872CF1ZWF	org_01JEPW1M88DENBJJMA8P4P013P	CLI Conversation - 2025-11-25 01:15	2025-11-25 01:15:47.409769+00	2025-11-26 11:47:57.279993+00	{"source": "cli", "auto_created": true}	active	user_01JDZ8W2YAG96EGGQ872CF1ZWF	\N	\N
\.


--
-- Data for Name: directory_mappings; Type: TABLE DATA; Schema: public; Owner: archie_dev
--

COPY public.directory_mappings (id, directory_id, firm_id, organization_id, directory_type, is_active, metadata, created_at, updated_at) FROM stdin;
\.


--
-- Data for Name: feedback; Type: TABLE DATA; Schema: public; Owner: archie_dev
--

COPY public.feedback (id, query_id, user_id, rating, feedback_text, created_at, metadata) FROM stdin;
\.


--
-- Data for Name: identity_sync_audit; Type: TABLE DATA; Schema: public; Owner: archie_dev
--

COPY public.identity_sync_audit (id, user_id, event_type, status, details, created_at) FROM stdin;
\.


--
-- Data for Name: messages; Type: TABLE DATA; Schema: public; Owner: archie_dev
--

COPY public.messages (message_id, conversation_id, user_id, firm_id, role, content, confidence_score, model_used, metadata, created_at, updated_at) FROM stdin;
\.


--
-- Data for Name: queries; Type: TABLE DATA; Schema: public; Owner: archie_dev
--

COPY public.queries (id, conversation_id, user_id, query_text, response_text, status, intent, model_used, processing_time_ms, tokens_used, created_at, completed_at, updated_at, metadata) FROM stdin;
050b4a16-6f68-479d-a559-7e19d9e90bba	7ed8d817-e841-4681-903a-132cd82ed9fe	user_12345	What is 2+2?	\N	processing	\N	\N	\N	\N	2025-11-24 03:28:24.837295+00	\N	2025-11-24 03:28:24.837295+00	{"attachments": []}
0ba54c68-ea7d-4e00-becc-a4f55cbc228d	550e8400-e29b-41d4-a716-446655440000	user_12345	Hello	\N	processing	\N	\N	\N	\N	2025-11-24 03:35:08.408577+00	\N	2025-11-24 03:35:08.408577+00	{"attachments": []}
5e314e92-cbea-4e93-a5ed-d0ef3bbd985e	4df9557a-d089-4fa2-81ef-c1f639daf402	user_01JDZ8W2YAG96EGGQ872CF1ZWF	Hello	\N	processing	\N	\N	\N	\N	2025-11-24 03:36:16.03016+00	\N	2025-11-24 03:36:16.03016+00	{"attachments": []}
faf600b1-5e28-405f-b690-1a7f68ffe000	b6cb93da-7d31-49af-8335-3cea87e62572	user_01JDZ8W2YAG96EGGQ872CF1ZWF	Hello from Archie CLI!	\N	processing	\N	\N	\N	\N	2025-11-24 13:24:35.049589+00	\N	2025-11-24 13:24:35.049589+00	{"attachments": []}
57dfde7a-418d-4688-b4d5-6d176a6f877f	61a6b4cb-bf34-4c45-acc2-39cef19b9c74	user_01JDZ8W2YAG96EGGQ872CF1ZWF	Hello from Archie CLI!	\N	processing	\N	\N	\N	\N	2025-11-24 13:58:22.77421+00	\N	2025-11-24 13:58:22.77421+00	{"attachments": []}
df91693d-267d-4acd-bec8-03ba3865d1c0	8a7fd25e-6929-4ec0-b193-d299c4ff17c8	user_01JDZ8W2YAG96EGGQ872CF1ZWF	Hello from Archie CLI!	\N	processing	\N	\N	\N	\N	2025-11-24 13:59:36.147808+00	\N	2025-11-24 13:59:36.147808+00	{"attachments": []}
1bbe59fa-1a73-42f6-9769-7bf41a537ee3	046ec146-fc0a-4ded-be21-fd36e82223ce	user_01JDZ8W2YAG96EGGQ872CF1ZWF	Hello from Archie CLI!	\N	processing	\N	\N	\N	\N	2025-11-24 14:25:13.133739+00	\N	2025-11-24 14:25:13.133739+00	{"attachments": []}
863633bc-7293-4eaa-9e09-8420344370ed	e6955cab-8102-4554-abf5-6056dfa88fdc	user_01JDZ8W2YAG96EGGQ872CF1ZWF	Hello from Archie CLI!	\N	processing	\N	\N	\N	\N	2025-11-24 14:27:26.871172+00	\N	2025-11-24 14:27:26.871172+00	{"attachments": []}
27debc63-0ce2-4d28-8fb6-8afc40fca4c2	4d786842-28b9-488b-a939-7cdbafa6aa1b	user_01JDZ8W2YAG96EGGQ872CF1ZWF	Hello from Archie CLI!	\N	processing	\N	\N	\N	\N	2025-11-24 14:29:21.980319+00	\N	2025-11-24 14:29:21.980319+00	{"attachments": []}
71e5c4b9-e7f6-49bb-8e85-db50c4b06ea0	e85ef543-f962-4226-a359-304f47e606f2	user_01JDZ8W2YAG96EGGQ872CF1ZWF	Hello from Archie CLI!	\N	processing	\N	\N	\N	\N	2025-11-24 14:33:28.41343+00	\N	2025-11-24 14:33:28.41343+00	{"attachments": []}
dca6b5d1-1e83-4f9d-99ac-2ca0098d36df	61b2ff35-6574-4c17-b4b8-50db0f5106b6	user_01JDZ8W2YAG96EGGQ872CF1ZWF	Hello from Archie CLI!	\N	processing	\N	\N	\N	\N	2025-11-24 14:51:27.131793+00	\N	2025-11-24 14:51:27.131793+00	{"attachments": []}
cc95f0bf-4567-4865-ae87-8594334c245d	b823d573-dd6e-4e11-bdbd-cdb7789f2240	user_01JDZ8W2YAG96EGGQ872CF1ZWF	Hello from Archie CLI!	\N	processing	\N	\N	\N	\N	2025-11-24 15:04:49.70457+00	\N	2025-11-24 15:04:49.70457+00	{"attachments": []}
51c7bda3-9d1e-4e10-bd66-18b4427c6af6	1f891257-68de-4077-9325-16a3cddcd154	user_01JDZ8W2YAG96EGGQ872CF1ZWF	Hello	\N	processing	\N	\N	\N	\N	2025-11-24 16:08:44.971445+00	\N	2025-11-24 16:08:44.971445+00	{"attachments": []}
d5ce6e24-d76a-4a50-a45f-9c4663241b66	3cb88d41-7f87-48f7-83c1-f4168ab808d8	user_01JDZ8W2YAG96EGGQ872CF1ZWF	Hello	\N	processing	\N	\N	\N	\N	2025-11-24 16:14:04.778353+00	\N	2025-11-24 16:14:04.778353+00	{"attachments": []}
b2d92842-a273-40b7-b2a9-82d42d22885a	5250dc82-1d7f-48e7-a9cd-023514008707	user_01JDZ8W2YAG96EGGQ872CF1ZWF	hello	\N	processing	\N	\N	\N	\N	2025-11-24 16:20:34.784179+00	\N	2025-11-24 16:20:34.784179+00	{"attachments": []}
d92be1ce-e804-4c0a-9447-ffc579999dc0	342fc661-342a-4746-af2a-c8e1268a9f58	user_01JDZ8W2YAG96EGGQ872CF1ZWF	hello	\N	processing	\N	\N	\N	\N	2025-11-24 16:21:01.186974+00	\N	2025-11-24 16:21:01.186974+00	{"attachments": []}
a6ae791c-0950-4ffa-8a97-610c442d52f3	07db14c2-8950-4b35-aab0-f401e6ace9bf	user_01JDZ8W2YAG96EGGQ872CF1ZWF	hello	\N	processing	\N	\N	\N	\N	2025-11-24 16:23:45.92333+00	\N	2025-11-24 16:23:45.92333+00	{"attachments": []}
a4cda2ca-52fd-4175-aaf8-0ee421c19b54	b4955bad-185c-4e0a-85d5-1165d141bf18	user_01JDZ8W2YAG96EGGQ872CF1ZWF	[3Hello	\N	processing	\N	\N	\N	\N	2025-11-24 16:31:21.583217+00	\N	2025-11-24 16:31:21.583217+00	{"attachments": []}
4ddbaac5-7ebc-4356-ace9-fe83124afa97	0c18867c-ba51-485f-80a0-ce008750e5f4	user_01JDZ8W2YAG96EGGQ872CF1ZWF	\\hello	\N	processing	\N	\N	\N	\N	2025-11-24 16:33:19.401206+00	\N	2025-11-24 16:33:19.401206+00	{"attachments": []}
4d7ceb50-8058-4cb2-a615-881dea6bfd7a	59bd1e95-5531-4127-859f-ed01d764086d	user_01JDZ8W2YAG96EGGQ872CF1ZWF	hello	\N	processing	\N	\N	\N	\N	2025-11-24 16:38:57.631121+00	\N	2025-11-24 16:38:57.631121+00	{"attachments": []}
a61ca42d-1dc8-41e4-bbd8-cafcce87e32c	b79d927d-f04e-4f13-ba56-90d513a46635	user_01JDZ8W2YAG96EGGQ872CF1ZWF	stuarty	\N	processing	\N	\N	\N	\N	2025-11-24 16:44:24.403497+00	\N	2025-11-24 16:44:24.403497+00	{"attachments": []}
3d578f5c-902a-4a96-bfad-f0b3de954ece	b79d927d-f04e-4f13-ba56-90d513a46635	user_01JDZ8W2YAG96EGGQ872CF1ZWF	exit	\N	processing	\N	\N	\N	\N	2025-11-24 16:45:51.790431+00	\N	2025-11-24 16:45:51.790431+00	{"attachments": []}
18956114-094d-4f2c-804c-03f864d12ea9	69ddbf01-7e6a-48b0-bd45-359d441e34d0	user_01JDZ8W2YAG96EGGQ872CF1ZWF	hello	\N	processing	\N	\N	\N	\N	2025-11-24 16:46:00.046996+00	\N	2025-11-24 16:46:00.046996+00	{"attachments": []}
81fe137f-2aaf-4f90-bd98-5ee41a229a81	8c8a2b16-8f0d-4560-be01-59d784a51eca	user_01JDZ8W2YAG96EGGQ872CF1ZWF	Hi Archie!	\N	processing	\N	\N	\N	\N	2025-11-24 16:50:07.523177+00	\N	2025-11-24 16:50:07.523177+00	{"attachments": []}
e7d9cb48-912e-4438-aa78-045d42f95285	8c8a2b16-8f0d-4560-be01-59d784a51eca	user_01JDZ8W2YAG96EGGQ872CF1ZWF	Based on the minimal context, I'll create a structured, comprehensive depreciation analysis request:\n\n"Explain the depreciation methodology and calculations with the following comprehensive breakdown:\n\n1. Depreciation Fundamentals\n - Definition and accounting purpose\n - Key accounting standards governing depreciation (GAAP/IFRS)\n - Primary depreciation methods (straight-line, declining balance, units of production)\n\n2. Calculation Components\n - Asset cost determination\n - Estimated useful life assessment\n - Salvage value considerations\n - Depreciation rate computation\n\n3. Practical Application Analysis\n - Typical depreciation scenarios across different asset types\n - Impact on financial statements\n - Tax implications of depreciation methods\n - Common calculation errors to avoid\n\n4. Comparative Method Review\n - Pros and cons of different depreciation approaches\n - Industry-specific depreciation practices\n - Recommended method selection criteria\n\n5. Reporting and Documentation\n - Required financial statement disclosures\n - Tracking and maintaining depreciation schedules\n - Audit documentation best practices\n\nDeliverable Format: Comprehensive explanatory report with numerical examples, visual diagrams, and practical insights.\n\nExpected Depth: Technical explanation suitable for accounting professionals, with clear, accessible language and practical context."	\N	processing	\N	\N	\N	\N	2025-11-24 16:51:10.756221+00	\N	2025-11-24 16:51:10.756221+00	{"attachments": []}
75f6839b-1cb1-45fb-89ae-f02d13d2c836	868cb742-1314-453a-a41c-cd0ca9d51465	user_01JDZ8W2YAG96EGGQ872CF1ZWF	Hello!	\N	processing	\N	\N	\N	\N	2025-11-24 16:55:03.114276+00	\N	2025-11-24 16:55:03.114276+00	{"attachments": []}
bdd87120-d92b-4397-8504-0c4961efbc84	868cb742-1314-453a-a41c-cd0ca9d51465	user_01JDZ8W2YAG96EGGQ872CF1ZWF	Ok can i say hello?	\N	processing	\N	\N	\N	\N	2025-11-24 16:56:43.064492+00	\N	2025-11-24 16:56:43.064492+00	{"attachments": []}
53071867-767a-4d9c-89e8-9fded5fb9323	8df2da1b-0b84-4595-a7a8-27c9af6a228b	user_01JDZ8W2YAG96EGGQ872CF1ZWF	Hello!	\N	processing	\N	\N	\N	\N	2025-11-24 16:57:31.417753+00	\N	2025-11-24 16:57:31.417753+00	{"attachments": []}
e939b22f-0f98-4296-8d98-ff4aa32d64ca	ebae1b0a-c00f-424a-aa47-b6f39b890f09	user_01JDZ8W2YAG96EGGQ872CF1ZWF	tell me about depreciation	\N	processing	\N	\N	\N	\N	2025-11-24 16:58:59.566042+00	\N	2025-11-24 16:58:59.566042+00	{"attachments": []}
fea90a64-b5cd-4b5b-9af2-cbac2507ff2c	36f143f5-1089-4205-b386-def97a476efd	user_01JDZ8W2YAG96EGGQ872CF1ZWF	Hello!	Hello! I'm happy to help answer any accounting questions you may have. To provide a comprehensive response, I will need you to ask a specific question related to accounting standards or guidance. Once you provide a question, I will directly address it, cite relevant standards using the [Source Name] format, explain the reasoning step-by-step, provide practical guidance on applying the standards, and note any limitations or assumptions in my response. Please let me know what specific accounting topic or issue you would like me to assist with.\n\n---\n**References:**\n- Source Name\n	completed	voice_query	\N	11874	\N	2025-11-24 18:22:12.222803+00	2025-11-24 18:22:24.106908+00	2025-11-24 18:22:24.106908+00	{"rag_used": false, "citations": [], "confidence": null, "agents_used": [], "attachments": [], "fallback_triggered": false}
4c7f9313-5643-438f-b0cf-81339ad25188	e1af7205-a99e-4a02-bd11-95dcd5cb069d	user_01JDZ8W2YAG96EGGQ872CF1ZWF	Hello!	Hello! I'm an AI accounting assistant. I don't have a query from you yet, but I'm ready to provide a comprehensive answer that directly addresses your accounting question, cites relevant standards using [Source Name] format, explains the reasoning step-by-step, provides practical guidance, and notes any limitations or assumptions. Please go ahead and ask your accounting question, and I'll do my best to assist you while staying within the bounds of the authoritative guidance provided.\n\n---\n**References:**\n- Source Name\n	completed	voice_query	\N	11429	\N	2025-11-24 20:47:52.093528+00	2025-11-24 20:48:03.528966+00	2025-11-24 20:48:03.528966+00	{"rag_used": false, "citations": [], "confidence": null, "agents_used": [], "attachments": [], "fallback_triggered": false}
bb04ab57-9568-4784-98bb-e2ef514f46bd	aa2d935c-0347-43fa-ae3d-bb2cd562ae56	user_01JDZ8W2YAG96EGGQ872CF1ZWF	Hello!	Hello! I'm an AI accounting assistant. I'm ready to help answer your accounting questions, using relevant accounting standards and guidance as the basis for my responses. Please go ahead and ask your question, and I'll do my best to provide a comprehensive answer that directly addresses it, cites relevant sources, explains the reasoning clearly, offers practical guidance, and notes any key limitations or assumptions in my response.	completed	voice_query	\N	12184	\N	2025-11-24 20:51:59.963716+00	2025-11-24 20:52:12.154054+00	2025-11-24 20:52:12.154054+00	{"rag_used": false, "citations": [], "confidence": null, "agents_used": [], "attachments": [], "fallback_triggered": false}
893ffb1e-baf2-4720-9de3-1203d519f98e	67c266b0-8707-4468-81d6-e7bb4f6f5872	user_01JDZ8W2YAG96EGGQ872CF1ZWF	hello	Hello! I am an AI accounting assistant. How may I assist you today? Please provide a specific accounting question or topic you would like me to address. I will aim to provide a comprehensive answer that directly addresses your query, cites relevant accounting standards, explains the reasoning step-by-step, offers practical guidance, and notes any key limitations or assumptions. Let me know what accounting issue you need help with.	completed	voice_query	\N	13299	\N	2025-11-24 20:56:10.86806+00	2025-11-24 20:56:24.171922+00	2025-11-24 20:56:24.171922+00	{"rag_used": false, "citations": [], "confidence": null, "agents_used": [], "attachments": [], "fallback_triggered": false}
62b24838-808f-4abf-9fad-9ac29ef4d999	e084595a-9450-4ad9-b59e-e2746de1c7e8	user_01JDZ8W2YAG96EGGQ872CF1ZWF	Hello	Hello! I'm an AI accounting assistant. I'm happy to help answer your accounting questions by directly citing relevant accounting standards and guidance. Please let me know what specific accounting question or topic you need assistance with. I'll provide a comprehensive answer that explains the reasoning step-by-step, offers practical guidance, and notes any key limitations or assumptions. Let me know how I can help!	completed	voice_query	\N	13348	\N	2025-11-25 00:36:41.202215+00	2025-11-25 00:36:54.562841+00	2025-11-25 00:36:54.562841+00	{"rag_used": false, "citations": [], "confidence": null, "agents_used": [], "attachments": [], "fallback_triggered": false}
55759ce6-59d5-4ede-89fb-d395f611d878	3d8cf9c5-f2c7-4c9b-8ec3-681df381e66a	user_01JDZ8W2YAG96EGGQ872CF1ZWF	\\HEllo, I'm Bruce!	Hello Bruce, it's nice to meet you! I'm afraid I don't have enough context to provide a comprehensive accounting-related answer, as you haven't asked a specific accounting question yet. Please let me know if there is an accounting topic or issue you would like me to assist with. I'm happy to provide guidance that directly addresses your question, cites relevant accounting standards, explains the reasoning step-by-step, offers practical advice, and notes any key limitations or assumptions. Feel free to ask me anything related to accounting and I'll do my best to help!	completed	accounting_reasoning	\N	13637	\N	2025-11-25 01:15:03.54935+00	2025-11-25 01:15:17.192474+00	2025-11-25 01:15:17.192474+00	{"rag_used": false, "citations": [], "confidence": null, "agents_used": [], "attachments": [], "fallback_triggered": false}
46ca3647-11f0-45e8-b415-389750d64bd6	d0edb098-8143-4f5f-a68d-68dfb0f7efc3	user_01JDZ8W2YAG96EGGQ872CF1ZWF	Good evening	Good evening! How may I assist you with accounting and financial reporting questions today? I'm happy to provide guidance based on relevant accounting standards and principles. Please let me know what specific question or topic you need help with and I'll do my best to give a thorough, well-reasoned response citing authoritative sources.	completed	accounting_reasoning	\N	14126	\N	2025-11-25 01:15:47.412994+00	2025-11-25 01:16:01.544419+00	2025-11-25 01:16:01.544419+00	{"rag_used": false, "citations": [], "confidence": null, "agents_used": [], "attachments": [], "fallback_triggered": false}
\.


--
-- Data for Name: team_members; Type: TABLE DATA; Schema: public; Owner: archie_dev
--

COPY public.team_members (id, team_id, user_id, team_role, added_by, added_at, status, metadata, created_at, updated_at) FROM stdin;
\.


--
-- Data for Name: teams; Type: TABLE DATA; Schema: public; Owner: archie_dev
--

COPY public.teams (id, firm_id, name, description, created_by, status, metadata, created_at, updated_at) FROM stdin;
\.


--
-- Data for Name: test_rls_docs; Type: TABLE DATA; Schema: public; Owner: archie_dev
--

COPY public.test_rls_docs (id, firm_id, title) FROM stdin;
849e0438-1c0d-43db-ae4b-80d8bf1f1c70	b4ad9484-7098-417b-91d7-3840118f68a4	Firm A Document
82945397-480d-4447-97ff-cfdc4da15b96	04d83f47-5f48-42e6-b50a-3ae66d46755a	Firm B Document
\.


--
-- Data for Name: users; Type: TABLE DATA; Schema: public; Owner: archie_dev
--

COPY public.users (user_id, email, firm_id, role, subscription_tier, created_at, last_login_at, metadata, updated_at, deleted_at) FROM stdin;
user_01JDZ8W2YAG96EGGQ872CF1ZWF	user_01JDZ8W2YAG96EGGQ872CF1ZWF@workos.user	org_01JEPW1M88DENBJJMA8P4P013P	user	starter	2025-11-24 03:36:16.022334+00	\N	{}	2025-11-24 11:21:01.4387+00	\N
user_12345	test@example.com	test_firm	user	starter	2025-11-24 03:19:15.683381+00	2025-11-24 11:24:18.571072+00	{}	2025-11-24 11:24:18.571072+00	\N
\.


--
-- Name: alembic_version alembic_version_pkc; Type: CONSTRAINT; Schema: public; Owner: archie_dev
--

ALTER TABLE ONLY public.alembic_version
    ADD CONSTRAINT alembic_version_pkc PRIMARY KEY (version_num);


--
-- Name: artifacts artifacts_pkey; Type: CONSTRAINT; Schema: public; Owner: archie_dev
--

ALTER TABLE ONLY public.artifacts
    ADD CONSTRAINT artifacts_pkey PRIMARY KEY (artifact_id);


--
-- Name: citations citations_pkey; Type: CONSTRAINT; Schema: public; Owner: archie_dev
--

ALTER TABLE ONLY public.citations
    ADD CONSTRAINT citations_pkey PRIMARY KEY (citation_id);


--
-- Name: conversation_archive_log conversation_archive_log_pkey; Type: CONSTRAINT; Schema: public; Owner: archie_dev
--

ALTER TABLE ONLY public.conversation_archive_log
    ADD CONSTRAINT conversation_archive_log_pkey PRIMARY KEY (id);


--
-- Name: conversation_participants conversation_participants_pkey; Type: CONSTRAINT; Schema: public; Owner: archie_dev
--

ALTER TABLE ONLY public.conversation_participants
    ADD CONSTRAINT conversation_participants_pkey PRIMARY KEY (id);


--
-- Name: conversation_shares conversation_shares_invitation_token_key; Type: CONSTRAINT; Schema: public; Owner: archie_dev
--

ALTER TABLE ONLY public.conversation_shares
    ADD CONSTRAINT conversation_shares_invitation_token_key UNIQUE (invitation_token);


--
-- Name: conversation_shares conversation_shares_pkey; Type: CONSTRAINT; Schema: public; Owner: archie_dev
--

ALTER TABLE ONLY public.conversation_shares
    ADD CONSTRAINT conversation_shares_pkey PRIMARY KEY (id);


--
-- Name: conversations conversations_pkey; Type: CONSTRAINT; Schema: public; Owner: archie_dev
--

ALTER TABLE ONLY public.conversations
    ADD CONSTRAINT conversations_pkey PRIMARY KEY (id);


--
-- Name: directory_mappings directory_mappings_directory_id_key; Type: CONSTRAINT; Schema: public; Owner: archie_dev
--

ALTER TABLE ONLY public.directory_mappings
    ADD CONSTRAINT directory_mappings_directory_id_key UNIQUE (directory_id);


--
-- Name: directory_mappings directory_mappings_pkey; Type: CONSTRAINT; Schema: public; Owner: archie_dev
--

ALTER TABLE ONLY public.directory_mappings
    ADD CONSTRAINT directory_mappings_pkey PRIMARY KEY (id);


--
-- Name: feedback feedback_pkey; Type: CONSTRAINT; Schema: public; Owner: archie_dev
--

ALTER TABLE ONLY public.feedback
    ADD CONSTRAINT feedback_pkey PRIMARY KEY (id);


--
-- Name: identity_sync_audit identity_sync_audit_pkey; Type: CONSTRAINT; Schema: public; Owner: archie_dev
--

ALTER TABLE ONLY public.identity_sync_audit
    ADD CONSTRAINT identity_sync_audit_pkey PRIMARY KEY (id);


--
-- Name: messages messages_pkey; Type: CONSTRAINT; Schema: public; Owner: archie_dev
--

ALTER TABLE ONLY public.messages
    ADD CONSTRAINT messages_pkey PRIMARY KEY (message_id);


--
-- Name: queries queries_pkey; Type: CONSTRAINT; Schema: public; Owner: archie_dev
--

ALTER TABLE ONLY public.queries
    ADD CONSTRAINT queries_pkey PRIMARY KEY (id);


--
-- Name: team_members team_members_pkey; Type: CONSTRAINT; Schema: public; Owner: archie_dev
--

ALTER TABLE ONLY public.team_members
    ADD CONSTRAINT team_members_pkey PRIMARY KEY (id);


--
-- Name: teams teams_pkey; Type: CONSTRAINT; Schema: public; Owner: archie_dev
--

ALTER TABLE ONLY public.teams
    ADD CONSTRAINT teams_pkey PRIMARY KEY (id);


--
-- Name: test_rls_docs test_rls_docs_pkey; Type: CONSTRAINT; Schema: public; Owner: archie_dev
--

ALTER TABLE ONLY public.test_rls_docs
    ADD CONSTRAINT test_rls_docs_pkey PRIMARY KEY (id);


--
-- Name: conversation_participants uq_participant_conversation_user; Type: CONSTRAINT; Schema: public; Owner: archie_dev
--

ALTER TABLE ONLY public.conversation_participants
    ADD CONSTRAINT uq_participant_conversation_user UNIQUE (conversation_id, user_id);


--
-- Name: team_members uq_team_member; Type: CONSTRAINT; Schema: public; Owner: archie_dev
--

ALTER TABLE ONLY public.team_members
    ADD CONSTRAINT uq_team_member UNIQUE (team_id, user_id);


--
-- Name: teams uq_team_name_firm; Type: CONSTRAINT; Schema: public; Owner: archie_dev
--

ALTER TABLE ONLY public.teams
    ADD CONSTRAINT uq_team_name_firm UNIQUE (firm_id, name);


--
-- Name: users users_email_key; Type: CONSTRAINT; Schema: public; Owner: archie_dev
--

ALTER TABLE ONLY public.users
    ADD CONSTRAINT users_email_key UNIQUE (email);


--
-- Name: users users_pkey; Type: CONSTRAINT; Schema: public; Owner: archie_dev
--

ALTER TABLE ONLY public.users
    ADD CONSTRAINT users_pkey PRIMARY KEY (user_id);


--
-- Name: idx_archive_conversation; Type: INDEX; Schema: public; Owner: archie_dev
--

CREATE INDEX idx_archive_conversation ON public.conversation_archive_log USING btree (conversation_id);


--
-- Name: idx_archive_created_at; Type: INDEX; Schema: public; Owner: archie_dev
--

CREATE INDEX idx_archive_created_at ON public.conversation_archive_log USING btree (created_at DESC);


--
-- Name: idx_archive_user; Type: INDEX; Schema: public; Owner: archie_dev
--

CREATE INDEX idx_archive_user ON public.conversation_archive_log USING btree (user_id);


--
-- Name: idx_artifacts_created_at; Type: INDEX; Schema: public; Owner: archie_dev
--

CREATE INDEX idx_artifacts_created_at ON public.artifacts USING btree (created_at DESC);


--
-- Name: idx_artifacts_message; Type: INDEX; Schema: public; Owner: archie_dev
--

CREATE INDEX idx_artifacts_message ON public.artifacts USING btree (message_id);


--
-- Name: idx_artifacts_metadata; Type: INDEX; Schema: public; Owner: archie_dev
--

CREATE INDEX idx_artifacts_metadata ON public.artifacts USING gin (metadata);


--
-- Name: idx_artifacts_type; Type: INDEX; Schema: public; Owner: archie_dev
--

CREATE INDEX idx_artifacts_type ON public.artifacts USING btree (artifact_type, created_at DESC);


--
-- Name: idx_citations_created_at; Type: INDEX; Schema: public; Owner: archie_dev
--

CREATE INDEX idx_citations_created_at ON public.citations USING btree (created_at DESC);


--
-- Name: idx_citations_message; Type: INDEX; Schema: public; Owner: archie_dev
--

CREATE INDEX idx_citations_message ON public.citations USING btree (message_id);


--
-- Name: idx_citations_metadata; Type: INDEX; Schema: public; Owner: archie_dev
--

CREATE INDEX idx_citations_metadata ON public.citations USING gin (metadata);


--
-- Name: idx_citations_relevance; Type: INDEX; Schema: public; Owner: archie_dev
--

CREATE INDEX idx_citations_relevance ON public.citations USING btree (message_id, relevance_score DESC) WHERE (relevance_score IS NOT NULL);


--
-- Name: idx_conversation_archived_at; Type: INDEX; Schema: public; Owner: archie_dev
--

CREATE INDEX idx_conversation_archived_at ON public.conversations USING btree (archived_at) WHERE (archived_at IS NOT NULL);


--
-- Name: idx_conversation_owner; Type: INDEX; Schema: public; Owner: archie_dev
--

CREATE INDEX idx_conversation_owner ON public.conversations USING btree (owner_id) WHERE (owner_id IS NOT NULL);


--
-- Name: idx_conversation_status; Type: INDEX; Schema: public; Owner: archie_dev
--

CREATE INDEX idx_conversation_status ON public.conversations USING btree (status);


--
-- Name: idx_conversations_active_firm; Type: INDEX; Schema: public; Owner: archie_dev
--

CREATE INDEX idx_conversations_active_firm ON public.conversations USING btree (firm_id, updated_at DESC) WHERE ((status)::text = 'active'::text);


--
-- Name: INDEX idx_conversations_active_firm; Type: COMMENT; Schema: public; Owner: archie_dev
--

COMMENT ON INDEX public.idx_conversations_active_firm IS 'Optimized index for active conversation queries by firm';


--
-- Name: idx_conversations_created_at; Type: INDEX; Schema: public; Owner: archie_dev
--

CREATE INDEX idx_conversations_created_at ON public.conversations USING btree (created_at DESC);


--
-- Name: idx_conversations_firm_id; Type: INDEX; Schema: public; Owner: archie_dev
--

CREATE INDEX idx_conversations_firm_id ON public.conversations USING btree (firm_id) WHERE (firm_id IS NOT NULL);


--
-- Name: idx_conversations_metadata; Type: INDEX; Schema: public; Owner: archie_dev
--

CREATE INDEX idx_conversations_metadata ON public.conversations USING gin (metadata);


--
-- Name: idx_conversations_updated_at; Type: INDEX; Schema: public; Owner: archie_dev
--

CREATE INDEX idx_conversations_updated_at ON public.conversations USING btree (updated_at DESC);


--
-- Name: idx_conversations_user_id; Type: INDEX; Schema: public; Owner: archie_dev
--

CREATE INDEX idx_conversations_user_id ON public.conversations USING btree (user_id);


--
-- Name: idx_directory_mappings_active; Type: INDEX; Schema: public; Owner: archie_dev
--

CREATE INDEX idx_directory_mappings_active ON public.directory_mappings USING btree (is_active) WHERE (is_active = true);


--
-- Name: idx_directory_mappings_firm_id; Type: INDEX; Schema: public; Owner: archie_dev
--

CREATE INDEX idx_directory_mappings_firm_id ON public.directory_mappings USING btree (firm_id);


--
-- Name: idx_directory_mappings_org_id; Type: INDEX; Schema: public; Owner: archie_dev
--

CREATE INDEX idx_directory_mappings_org_id ON public.directory_mappings USING btree (organization_id) WHERE (organization_id IS NOT NULL);


--
-- Name: idx_feedback_created_at; Type: INDEX; Schema: public; Owner: archie_dev
--

CREATE INDEX idx_feedback_created_at ON public.feedback USING btree (created_at DESC);


--
-- Name: idx_feedback_query_id; Type: INDEX; Schema: public; Owner: archie_dev
--

CREATE INDEX idx_feedback_query_id ON public.feedback USING btree (query_id);


--
-- Name: idx_feedback_rating; Type: INDEX; Schema: public; Owner: archie_dev
--

CREATE INDEX idx_feedback_rating ON public.feedback USING btree (rating);


--
-- Name: idx_feedback_user_id; Type: INDEX; Schema: public; Owner: archie_dev
--

CREATE INDEX idx_feedback_user_id ON public.feedback USING btree (user_id);


--
-- Name: idx_messages_confidence; Type: INDEX; Schema: public; Owner: archie_dev
--

CREATE INDEX idx_messages_confidence ON public.messages USING btree (confidence_score DESC NULLS LAST) WHERE ((confidence_score IS NOT NULL) AND ((role)::text = 'assistant'::text));


--
-- Name: idx_messages_conversation; Type: INDEX; Schema: public; Owner: archie_dev
--

CREATE INDEX idx_messages_conversation ON public.messages USING btree (conversation_id, created_at DESC);


--
-- Name: idx_messages_firm; Type: INDEX; Schema: public; Owner: archie_dev
--

CREATE INDEX idx_messages_firm ON public.messages USING btree (firm_id) WHERE ((role)::text = 'user'::text);


--
-- Name: idx_messages_metadata; Type: INDEX; Schema: public; Owner: archie_dev
--

CREATE INDEX idx_messages_metadata ON public.messages USING gin (metadata);


--
-- Name: idx_messages_model; Type: INDEX; Schema: public; Owner: archie_dev
--

CREATE INDEX idx_messages_model ON public.messages USING btree (model_used, created_at DESC) WHERE (model_used IS NOT NULL);


--
-- Name: idx_messages_role; Type: INDEX; Schema: public; Owner: archie_dev
--

CREATE INDEX idx_messages_role ON public.messages USING btree (role, created_at DESC);


--
-- Name: idx_messages_search; Type: INDEX; Schema: public; Owner: archie_dev
--

CREATE INDEX idx_messages_search ON public.messages USING gin (to_tsvector('english'::regconfig, content));


--
-- Name: idx_messages_user; Type: INDEX; Schema: public; Owner: archie_dev
--

CREATE INDEX idx_messages_user ON public.messages USING btree (user_id, created_at DESC);


--
-- Name: idx_participant_conversation; Type: INDEX; Schema: public; Owner: archie_dev
--

CREATE INDEX idx_participant_conversation ON public.conversation_participants USING btree (conversation_id) WHERE ((status)::text = 'active'::text);


--
-- Name: idx_participant_firm; Type: INDEX; Schema: public; Owner: archie_dev
--

CREATE INDEX idx_participant_firm ON public.conversation_participants USING btree (firm_id) WHERE ((status)::text = 'active'::text);


--
-- Name: idx_participant_last_accessed; Type: INDEX; Schema: public; Owner: archie_dev
--

CREATE INDEX idx_participant_last_accessed ON public.conversation_participants USING btree (last_accessed_at DESC);


--
-- Name: idx_participant_lookup; Type: INDEX; Schema: public; Owner: archie_dev
--

CREATE INDEX idx_participant_lookup ON public.conversation_participants USING btree (conversation_id, user_id, status, permissions) WHERE ((status)::text = 'active'::text);


--
-- Name: idx_participant_metadata; Type: INDEX; Schema: public; Owner: archie_dev
--

CREATE INDEX idx_participant_metadata ON public.conversation_participants USING gin (metadata);


--
-- Name: idx_participant_permissions; Type: INDEX; Schema: public; Owner: archie_dev
--

CREATE INDEX idx_participant_permissions ON public.conversation_participants USING btree (permissions);


--
-- Name: idx_participant_role; Type: INDEX; Schema: public; Owner: archie_dev
--

CREATE INDEX idx_participant_role ON public.conversation_participants USING btree (role) WHERE ((status)::text = 'active'::text);


--
-- Name: idx_participant_user; Type: INDEX; Schema: public; Owner: archie_dev
--

CREATE INDEX idx_participant_user ON public.conversation_participants USING btree (user_id) WHERE ((status)::text = 'active'::text);


--
-- Name: idx_queries_completed_at; Type: INDEX; Schema: public; Owner: archie_dev
--

CREATE INDEX idx_queries_completed_at ON public.queries USING btree (completed_at DESC) WHERE (completed_at IS NOT NULL);


--
-- Name: idx_queries_conversation_id; Type: INDEX; Schema: public; Owner: archie_dev
--

CREATE INDEX idx_queries_conversation_id ON public.queries USING btree (conversation_id);


--
-- Name: idx_queries_created_at; Type: INDEX; Schema: public; Owner: archie_dev
--

CREATE INDEX idx_queries_created_at ON public.queries USING btree (created_at DESC);


--
-- Name: idx_queries_intent; Type: INDEX; Schema: public; Owner: archie_dev
--

CREATE INDEX idx_queries_intent ON public.queries USING btree (intent) WHERE (intent IS NOT NULL);


--
-- Name: idx_queries_metadata; Type: INDEX; Schema: public; Owner: archie_dev
--

CREATE INDEX idx_queries_metadata ON public.queries USING gin (metadata);


--
-- Name: idx_queries_model_used; Type: INDEX; Schema: public; Owner: archie_dev
--

CREATE INDEX idx_queries_model_used ON public.queries USING btree (model_used) WHERE (model_used IS NOT NULL);


--
-- Name: idx_queries_query_text_search; Type: INDEX; Schema: public; Owner: archie_dev
--

CREATE INDEX idx_queries_query_text_search ON public.queries USING gin (to_tsvector('english'::regconfig, query_text));


--
-- Name: idx_queries_status; Type: INDEX; Schema: public; Owner: archie_dev
--

CREATE INDEX idx_queries_status ON public.queries USING btree (status);


--
-- Name: idx_queries_user_id; Type: INDEX; Schema: public; Owner: archie_dev
--

CREATE INDEX idx_queries_user_id ON public.queries USING btree (user_id);


--
-- Name: idx_share_conversation; Type: INDEX; Schema: public; Owner: archie_dev
--

CREATE INDEX idx_share_conversation ON public.conversation_shares USING btree (conversation_id);


--
-- Name: idx_share_created_at; Type: INDEX; Schema: public; Owner: archie_dev
--

CREATE INDEX idx_share_created_at ON public.conversation_shares USING btree (created_at DESC);


--
-- Name: idx_share_expires; Type: INDEX; Schema: public; Owner: archie_dev
--

CREATE INDEX idx_share_expires ON public.conversation_shares USING btree (expires_at) WHERE ((status)::text = 'pending'::text);


--
-- Name: idx_share_invited_by; Type: INDEX; Schema: public; Owner: archie_dev
--

CREATE INDEX idx_share_invited_by ON public.conversation_shares USING btree (invited_by);


--
-- Name: idx_share_invited_team; Type: INDEX; Schema: public; Owner: archie_dev
--

CREATE INDEX idx_share_invited_team ON public.conversation_shares USING btree (invited_team_id) WHERE ((invited_team_id IS NOT NULL) AND ((status)::text = 'pending'::text));


--
-- Name: idx_share_invited_user; Type: INDEX; Schema: public; Owner: archie_dev
--

CREATE INDEX idx_share_invited_user ON public.conversation_shares USING btree (invited_user_id) WHERE ((invited_user_id IS NOT NULL) AND ((status)::text = 'pending'::text));


--
-- Name: idx_share_pending_user; Type: INDEX; Schema: public; Owner: archie_dev
--

CREATE INDEX idx_share_pending_user ON public.conversation_shares USING btree (invited_user_id, status, expires_at) WHERE ((invited_user_id IS NOT NULL) AND ((status)::text = 'pending'::text));


--
-- Name: idx_share_status; Type: INDEX; Schema: public; Owner: archie_dev
--

CREATE INDEX idx_share_status ON public.conversation_shares USING btree (status);


--
-- Name: idx_share_token; Type: INDEX; Schema: public; Owner: archie_dev
--

CREATE INDEX idx_share_token ON public.conversation_shares USING btree (invitation_token) WHERE (invitation_token IS NOT NULL);


--
-- Name: idx_sync_audit_created_at; Type: INDEX; Schema: public; Owner: archie_dev
--

CREATE INDEX idx_sync_audit_created_at ON public.identity_sync_audit USING btree (created_at DESC);


--
-- Name: idx_sync_audit_details; Type: INDEX; Schema: public; Owner: archie_dev
--

CREATE INDEX idx_sync_audit_details ON public.identity_sync_audit USING gin (details);


--
-- Name: idx_sync_audit_event_type; Type: INDEX; Schema: public; Owner: archie_dev
--

CREATE INDEX idx_sync_audit_event_type ON public.identity_sync_audit USING btree (event_type);


--
-- Name: idx_sync_audit_status; Type: INDEX; Schema: public; Owner: archie_dev
--

CREATE INDEX idx_sync_audit_status ON public.identity_sync_audit USING btree (status);


--
-- Name: idx_sync_audit_user_id; Type: INDEX; Schema: public; Owner: archie_dev
--

CREATE INDEX idx_sync_audit_user_id ON public.identity_sync_audit USING btree (user_id);


--
-- Name: idx_team_created_by; Type: INDEX; Schema: public; Owner: archie_dev
--

CREATE INDEX idx_team_created_by ON public.teams USING btree (created_by);


--
-- Name: idx_team_firm; Type: INDEX; Schema: public; Owner: archie_dev
--

CREATE INDEX idx_team_firm ON public.teams USING btree (firm_id) WHERE ((status)::text = 'active'::text);


--
-- Name: idx_team_member_role; Type: INDEX; Schema: public; Owner: archie_dev
--

CREATE INDEX idx_team_member_role ON public.team_members USING btree (team_role);


--
-- Name: idx_team_member_team; Type: INDEX; Schema: public; Owner: archie_dev
--

CREATE INDEX idx_team_member_team ON public.team_members USING btree (team_id) WHERE ((status)::text = 'active'::text);


--
-- Name: idx_team_member_user; Type: INDEX; Schema: public; Owner: archie_dev
--

CREATE INDEX idx_team_member_user ON public.team_members USING btree (user_id) WHERE ((status)::text = 'active'::text);


--
-- Name: idx_team_membership_lookup; Type: INDEX; Schema: public; Owner: archie_dev
--

CREATE INDEX idx_team_membership_lookup ON public.team_members USING btree (team_id, user_id, status) WHERE ((status)::text = 'active'::text);


--
-- Name: idx_team_name; Type: INDEX; Schema: public; Owner: archie_dev
--

CREATE INDEX idx_team_name ON public.teams USING btree (name);


--
-- Name: idx_team_status; Type: INDEX; Schema: public; Owner: archie_dev
--

CREATE INDEX idx_team_status ON public.teams USING btree (status);


--
-- Name: idx_users_created_at; Type: INDEX; Schema: public; Owner: archie_dev
--

CREATE INDEX idx_users_created_at ON public.users USING btree (created_at DESC);


--
-- Name: idx_users_deleted_at; Type: INDEX; Schema: public; Owner: archie_dev
--

CREATE INDEX idx_users_deleted_at ON public.users USING btree (deleted_at) WHERE (deleted_at IS NULL);


--
-- Name: idx_users_email; Type: INDEX; Schema: public; Owner: archie_dev
--

CREATE INDEX idx_users_email ON public.users USING btree (email);


--
-- Name: idx_users_firm_id; Type: INDEX; Schema: public; Owner: archie_dev
--

CREATE INDEX idx_users_firm_id ON public.users USING btree (firm_id) WHERE (firm_id IS NOT NULL);


--
-- Name: idx_users_state; Type: INDEX; Schema: public; Owner: archie_dev
--

CREATE INDEX idx_users_state ON public.users USING btree (((metadata ->> 'state'::text))) WHERE ((metadata ->> 'state'::text) IS NOT NULL);


--
-- Name: idx_users_subscription_tier; Type: INDEX; Schema: public; Owner: archie_dev
--

CREATE INDEX idx_users_subscription_tier ON public.users USING btree (subscription_tier);


--
-- Name: idx_users_updated_at; Type: INDEX; Schema: public; Owner: archie_dev
--

CREATE INDEX idx_users_updated_at ON public.users USING btree (updated_at DESC);


--
-- Name: idx_users_workos_user_id; Type: INDEX; Schema: public; Owner: archie_dev
--

CREATE INDEX idx_users_workos_user_id ON public.users USING btree (((metadata ->> 'workos_user_id'::text))) WHERE ((metadata ->> 'workos_user_id'::text) IS NOT NULL);


--
-- Name: conversations_with_participants _RETURN; Type: RULE; Schema: public; Owner: archie_dev
--

CREATE OR REPLACE VIEW public.conversations_with_participants AS
 SELECT c.id,
    c.user_id,
    c.firm_id,
    c.title,
    c.created_at,
    c.updated_at,
    c.metadata,
    c.status,
    c.owner_id,
    c.archived_at,
    c.archived_by,
    count(DISTINCT cp.user_id) FILTER (WHERE ((cp.status)::text = 'active'::text)) AS participant_count,
    array_agg(DISTINCT cp.user_id) FILTER (WHERE ((cp.status)::text = 'active'::text)) AS participant_user_ids
   FROM (public.conversations c
     LEFT JOIN public.conversation_participants cp ON ((c.id = cp.conversation_id)))
  GROUP BY c.id;


--
-- Name: users trigger_update_users_updated_at; Type: TRIGGER; Schema: public; Owner: archie_dev
--

CREATE TRIGGER trigger_update_users_updated_at BEFORE UPDATE ON public.users FOR EACH ROW EXECUTE FUNCTION public.update_users_updated_at();


--
-- Name: conversations update_conversations_updated_at; Type: TRIGGER; Schema: public; Owner: archie_dev
--

CREATE TRIGGER update_conversations_updated_at BEFORE UPDATE ON public.conversations FOR EACH ROW EXECUTE FUNCTION public.update_updated_at_column();


--
-- Name: directory_mappings update_directory_mappings_updated_at; Type: TRIGGER; Schema: public; Owner: archie_dev
--

CREATE TRIGGER update_directory_mappings_updated_at BEFORE UPDATE ON public.directory_mappings FOR EACH ROW EXECUTE FUNCTION public.update_updated_at_column();


--
-- Name: messages update_messages_updated_at; Type: TRIGGER; Schema: public; Owner: archie_dev
--

CREATE TRIGGER update_messages_updated_at BEFORE UPDATE ON public.messages FOR EACH ROW EXECUTE FUNCTION public.update_updated_at_column();


--
-- Name: conversation_participants update_participant_updated_at; Type: TRIGGER; Schema: public; Owner: archie_dev
--

CREATE TRIGGER update_participant_updated_at BEFORE UPDATE ON public.conversation_participants FOR EACH ROW EXECUTE FUNCTION public.update_updated_at_column();


--
-- Name: queries update_queries_updated_at; Type: TRIGGER; Schema: public; Owner: archie_dev
--

CREATE TRIGGER update_queries_updated_at BEFORE UPDATE ON public.queries FOR EACH ROW EXECUTE FUNCTION public.update_updated_at_column();


--
-- Name: conversation_shares update_share_updated_at; Type: TRIGGER; Schema: public; Owner: archie_dev
--

CREATE TRIGGER update_share_updated_at BEFORE UPDATE ON public.conversation_shares FOR EACH ROW EXECUTE FUNCTION public.update_updated_at_column();


--
-- Name: team_members update_team_member_updated_at; Type: TRIGGER; Schema: public; Owner: archie_dev
--

CREATE TRIGGER update_team_member_updated_at BEFORE UPDATE ON public.team_members FOR EACH ROW EXECUTE FUNCTION public.update_updated_at_column();


--
-- Name: teams update_team_updated_at; Type: TRIGGER; Schema: public; Owner: archie_dev
--

CREATE TRIGGER update_team_updated_at BEFORE UPDATE ON public.teams FOR EACH ROW EXECUTE FUNCTION public.update_updated_at_column();


--
-- Name: conversation_archive_log fk_archive_conversation; Type: FK CONSTRAINT; Schema: public; Owner: archie_dev
--

ALTER TABLE ONLY public.conversation_archive_log
    ADD CONSTRAINT fk_archive_conversation FOREIGN KEY (conversation_id) REFERENCES public.conversations(id) ON DELETE CASCADE;


--
-- Name: conversation_archive_log fk_archive_user; Type: FK CONSTRAINT; Schema: public; Owner: archie_dev
--

ALTER TABLE ONLY public.conversation_archive_log
    ADD CONSTRAINT fk_archive_user FOREIGN KEY (user_id) REFERENCES public.users(user_id) ON DELETE SET NULL;


--
-- Name: artifacts fk_artifact_message; Type: FK CONSTRAINT; Schema: public; Owner: archie_dev
--

ALTER TABLE ONLY public.artifacts
    ADD CONSTRAINT fk_artifact_message FOREIGN KEY (message_id) REFERENCES public.messages(message_id) ON DELETE CASCADE;


--
-- Name: citations fk_citation_message; Type: FK CONSTRAINT; Schema: public; Owner: archie_dev
--

ALTER TABLE ONLY public.citations
    ADD CONSTRAINT fk_citation_message FOREIGN KEY (message_id) REFERENCES public.messages(message_id) ON DELETE CASCADE;


--
-- Name: conversations fk_conversation_archived_by; Type: FK CONSTRAINT; Schema: public; Owner: archie_dev
--

ALTER TABLE ONLY public.conversations
    ADD CONSTRAINT fk_conversation_archived_by FOREIGN KEY (archived_by) REFERENCES public.users(user_id) ON DELETE SET NULL;


--
-- Name: conversations fk_conversation_owner; Type: FK CONSTRAINT; Schema: public; Owner: archie_dev
--

ALTER TABLE ONLY public.conversations
    ADD CONSTRAINT fk_conversation_owner FOREIGN KEY (owner_id) REFERENCES public.users(user_id) ON DELETE SET NULL;


--
-- Name: conversations fk_conversations_user; Type: FK CONSTRAINT; Schema: public; Owner: archie_dev
--

ALTER TABLE ONLY public.conversations
    ADD CONSTRAINT fk_conversations_user FOREIGN KEY (user_id) REFERENCES public.users(user_id) ON DELETE CASCADE;


--
-- Name: feedback fk_feedback_query; Type: FK CONSTRAINT; Schema: public; Owner: archie_dev
--

ALTER TABLE ONLY public.feedback
    ADD CONSTRAINT fk_feedback_query FOREIGN KEY (query_id) REFERENCES public.queries(id) ON DELETE CASCADE;


--
-- Name: feedback fk_feedback_user; Type: FK CONSTRAINT; Schema: public; Owner: archie_dev
--

ALTER TABLE ONLY public.feedback
    ADD CONSTRAINT fk_feedback_user FOREIGN KEY (user_id) REFERENCES public.users(user_id) ON DELETE CASCADE;


--
-- Name: messages fk_message_conversation; Type: FK CONSTRAINT; Schema: public; Owner: archie_dev
--

ALTER TABLE ONLY public.messages
    ADD CONSTRAINT fk_message_conversation FOREIGN KEY (conversation_id) REFERENCES public.conversations(id) ON DELETE CASCADE;


--
-- Name: messages fk_message_user; Type: FK CONSTRAINT; Schema: public; Owner: archie_dev
--

ALTER TABLE ONLY public.messages
    ADD CONSTRAINT fk_message_user FOREIGN KEY (user_id) REFERENCES public.users(user_id) ON DELETE CASCADE;


--
-- Name: conversation_participants fk_participant_added_by; Type: FK CONSTRAINT; Schema: public; Owner: archie_dev
--

ALTER TABLE ONLY public.conversation_participants
    ADD CONSTRAINT fk_participant_added_by FOREIGN KEY (added_by) REFERENCES public.users(user_id) ON DELETE SET NULL;


--
-- Name: conversation_participants fk_participant_conversation; Type: FK CONSTRAINT; Schema: public; Owner: archie_dev
--

ALTER TABLE ONLY public.conversation_participants
    ADD CONSTRAINT fk_participant_conversation FOREIGN KEY (conversation_id) REFERENCES public.conversations(id) ON DELETE CASCADE;


--
-- Name: conversation_participants fk_participant_user; Type: FK CONSTRAINT; Schema: public; Owner: archie_dev
--

ALTER TABLE ONLY public.conversation_participants
    ADD CONSTRAINT fk_participant_user FOREIGN KEY (user_id) REFERENCES public.users(user_id) ON DELETE CASCADE;


--
-- Name: queries fk_queries_conversation; Type: FK CONSTRAINT; Schema: public; Owner: archie_dev
--

ALTER TABLE ONLY public.queries
    ADD CONSTRAINT fk_queries_conversation FOREIGN KEY (conversation_id) REFERENCES public.conversations(id) ON DELETE CASCADE;


--
-- Name: queries fk_queries_user; Type: FK CONSTRAINT; Schema: public; Owner: archie_dev
--

ALTER TABLE ONLY public.queries
    ADD CONSTRAINT fk_queries_user FOREIGN KEY (user_id) REFERENCES public.users(user_id) ON DELETE CASCADE;


--
-- Name: conversation_shares fk_share_conversation; Type: FK CONSTRAINT; Schema: public; Owner: archie_dev
--

ALTER TABLE ONLY public.conversation_shares
    ADD CONSTRAINT fk_share_conversation FOREIGN KEY (conversation_id) REFERENCES public.conversations(id) ON DELETE CASCADE;


--
-- Name: conversation_shares fk_share_invited_by; Type: FK CONSTRAINT; Schema: public; Owner: archie_dev
--

ALTER TABLE ONLY public.conversation_shares
    ADD CONSTRAINT fk_share_invited_by FOREIGN KEY (invited_by) REFERENCES public.users(user_id) ON DELETE SET NULL;


--
-- Name: conversation_shares fk_share_invited_team; Type: FK CONSTRAINT; Schema: public; Owner: archie_dev
--

ALTER TABLE ONLY public.conversation_shares
    ADD CONSTRAINT fk_share_invited_team FOREIGN KEY (invited_team_id) REFERENCES public.teams(id) ON DELETE CASCADE;


--
-- Name: conversation_shares fk_share_invited_user; Type: FK CONSTRAINT; Schema: public; Owner: archie_dev
--

ALTER TABLE ONLY public.conversation_shares
    ADD CONSTRAINT fk_share_invited_user FOREIGN KEY (invited_user_id) REFERENCES public.users(user_id) ON DELETE CASCADE;


--
-- Name: teams fk_team_created_by; Type: FK CONSTRAINT; Schema: public; Owner: archie_dev
--

ALTER TABLE ONLY public.teams
    ADD CONSTRAINT fk_team_created_by FOREIGN KEY (created_by) REFERENCES public.users(user_id) ON DELETE SET NULL;


--
-- Name: team_members fk_team_member_added_by; Type: FK CONSTRAINT; Schema: public; Owner: archie_dev
--

ALTER TABLE ONLY public.team_members
    ADD CONSTRAINT fk_team_member_added_by FOREIGN KEY (added_by) REFERENCES public.users(user_id) ON DELETE SET NULL;


--
-- Name: team_members fk_team_member_team; Type: FK CONSTRAINT; Schema: public; Owner: archie_dev
--

ALTER TABLE ONLY public.team_members
    ADD CONSTRAINT fk_team_member_team FOREIGN KEY (team_id) REFERENCES public.teams(id) ON DELETE CASCADE;


--
-- Name: team_members fk_team_member_user; Type: FK CONSTRAINT; Schema: public; Owner: archie_dev
--

ALTER TABLE ONLY public.team_members
    ADD CONSTRAINT fk_team_member_user FOREIGN KEY (user_id) REFERENCES public.users(user_id) ON DELETE CASCADE;


--
-- Name: test_rls_docs firm_isolation; Type: POLICY; Schema: public; Owner: archie_dev
--

CREATE POLICY firm_isolation ON public.test_rls_docs USING ((firm_id = COALESCE((current_setting('app.current_firm_id'::text, true))::uuid, '00000000-0000-0000-0000-000000000000'::uuid)));


--
-- Name: test_rls_docs; Type: ROW SECURITY; Schema: public; Owner: archie_dev
--

ALTER TABLE public.test_rls_docs ENABLE ROW LEVEL SECURITY;

--
-- PostgreSQL database dump complete
--

\unrestrict PxOn4uJ4CI6P8l8AmSZ09OeBX6Argss8bqC5xu77ZH2gTwQrWoQ1le37waT1Tfc

