-- Daisychain Data Sync Schema
-- Generated: 2026-05-25T07:47:07Z

-- Group: ActBlue

-- Donations received through ActBlue integration.
CREATE TABLE actblue_contributions (
    id BIGINT NOT NULL PRIMARY KEY,
    form_id BIGINT -- The ActBlue form used for this contribution.. FK: actblue_forms.id,
    person_id BIGINT -- The matched supporter in your account.. FK: people.id,
    integration_id VARCHAR(36) NOT NULL,
    order_number VARCHAR(256) NOT NULL,
    refcodes VARCHAR(MAX) DEFAULT {},
    custom_fields VARCHAR(MAX) DEFAULT {},
    credit_card_expiration VARCHAR(256),
    recurring_period VARCHAR(50) NOT NULL DEFAULT once -- Donation frequency: once, weekly, or monthly.,
    recurring_duration INTEGER,
    ab_test_name VARCHAR(256),
    weekly_recurring_sunset DATE,
    is_paypal BOOLEAN,
    is_mobile BOOLEAN,
    ab_test_variation VARCHAR(256),
    text_message_option VARCHAR(256) NOT NULL DEFAULT unknown,
    is_express BOOLEAN,
    with_express_lane BOOLEAN,
    express_signup BOOLEAN,
    status VARCHAR(50) NOT NULL DEFAULT pending -- Payment status: approved, pending, or declined.,
    thanks_url VARCHAR(256),
    retry_url VARCHAR(256),
    cancelled_at TIMESTAMP,
    recur_completed INTEGER,
    recur_pledged INTEGER,
    smart_boost_amount INTEGER,
    created_at TIMESTAMP NOT NULL,
    updated_at TIMESTAMP NOT NULL,
    broadcast_id BIGINT -- FK: broadcasts.id,
    message_content_variant_id BIGINT -- FK: message_content_variants.id
);

-- Employer information for ActBlue contributors (for FEC compliance).
CREATE TABLE actblue_employers (
    id BIGINT NOT NULL PRIMARY KEY,
    contribution_id BIGINT NOT NULL -- The contribution this employer info belongs to.. FK: actblue_contributions.id,
    employer VARCHAR(256),
    occupation VARCHAR(256) -- Contributor's occupation.,
    employer_addr_1 VARCHAR(256),
    employer_city VARCHAR(256),
    employer_state VARCHAR(256),
    employer_country VARCHAR(256),
    created_at TIMESTAMP NOT NULL,
    updated_at TIMESTAMP NOT NULL
);

-- Recipient entities/committees receiving ActBlue donations.
CREATE TABLE actblue_entities (
    id BIGINT NOT NULL PRIMARY KEY,
    entity_id BIGINT NOT NULL,
    fec_id VARCHAR(256),
    committee_name VARCHAR(256),
    created_at TIMESTAMP NOT NULL,
    updated_at TIMESTAMP NOT NULL
);

-- ActBlue donation form configurations.
CREATE TABLE actblue_forms (
    id BIGINT NOT NULL PRIMARY KEY,
    integration_id VARCHAR(36) NOT NULL,
    name VARCHAR(256) -- Form name.,
    kind VARCHAR(50) DEFAULT page -- Form type: page, merchandise, event, embed, or autogenerated.,
    managing_entity_name VARCHAR(256),
    managing_entity_committee_name VARCHAR(256),
    owner_email VARCHAR(256),
    created_at TIMESTAMP NOT NULL,
    updated_at TIMESTAMP NOT NULL
);

-- Individual line items within an ActBlue contribution (e.g., split donations to multiple recipients).
CREATE TABLE actblue_line_items (
    id BIGINT NOT NULL PRIMARY KEY,
    contribution_id BIGINT NOT NULL -- The contribution this line item belongs to.. FK: actblue_contributions.id,
    entity_id BIGINT NOT NULL -- The recipient entity/committee.. FK: actblue_entities.id,
    lineitem_id BIGINT NOT NULL,
    sequence INTEGER,
    amount INTEGER NOT NULL -- Amount for this line item in cents.,
    recurring_amount INTEGER,
    paid_at TIMESTAMP,
    refunded_at TIMESTAMP,
    disbursed_at TIMESTAMP,
    recovered_at TIMESTAMP,
    created_at TIMESTAMP NOT NULL,
    updated_at TIMESTAMP NOT NULL
);

-- Group: People

-- Tracked actions and events associated with people, such as donations, form submissions, and custom events.
CREATE TABLE actions (
    id BIGINT NOT NULL PRIMARY KEY,
    account_id BIGINT,
    kind VARCHAR(256) -- The type of action (e.g., donation, signup, custom).,
    data VARCHAR(MAX) -- Additional action data as JSON.,
    created_at TIMESTAMP NOT NULL,
    updated_at TIMESTAMP NOT NULL,
    person_id BIGINT -- The person who performed this action.. FK: people.id,
    actblue_contribution_id BIGINT -- Associated ActBlue contribution, if this action was a donation.. FK: actblue_contributions.id
);

-- Physical/mailing addresses associated with people.
CREATE TABLE addresses (
    id BIGINT NOT NULL PRIMARY KEY,
    person_id BIGINT -- The person this address belongs to.. FK: people.id,
    kind VARCHAR(256) NOT NULL DEFAULT home,
    post_office_box VARCHAR(256),
    street_address VARCHAR(256),
    extended_address VARCHAR(256),
    locality VARCHAR(256),
    region VARCHAR(256),
    postal_code VARCHAR(256),
    country VARCHAR(256),
    latitude DECIMAL,
    longitude DECIMAL,
    created_at TIMESTAMP NOT NULL,
    updated_at TIMESTAMP NOT NULL,
    geo_projection VARCHAR(256),
    primary BOOLEAN -- Whether this is the person's primary address.,
    geocode_precision VARCHAR(50) -- Precision level of the geocoded coordinates.
);

-- Email addresses associated with people. Each person can have multiple email addresses.
CREATE TABLE emails (
    id BIGINT NOT NULL PRIMARY KEY,
    person_id BIGINT -- The person this email belongs to.. FK: people.id,
    value VARCHAR(256),
    primary BOOLEAN -- Whether this is the person's primary email address.,
    created_at TIMESTAMP NOT NULL,
    updated_at TIMESTAMP NOT NULL
);

-- Custom field values captured during flow conversations.
CREATE TABLE flows_custom_field_captures (
    id BIGINT NOT NULL PRIMARY KEY,
    person_id BIGINT NOT NULL -- The person whose custom field was captured.. FK: people.id,
    field_identifier VARCHAR(256) NOT NULL,
    value VARCHAR(256) -- The captured value.,
    created_at TIMESTAMP NOT NULL,
    updated_at TIMESTAMP NOT NULL,
    field_configuration_id BIGINT
);

-- User-created notes attached to people.
CREATE TABLE notes (
    id BIGINT NOT NULL PRIMARY KEY,
    person_id BIGINT -- The person this note is about.. FK: people.id,
    card_id BIGINT,
    user_id BIGINT -- The team member who created the note.,
    content VARCHAR(256) -- The note text content.,
    created_at TIMESTAMP NOT NULL,
    updated_at TIMESTAMP NOT NULL
);

-- Core supporter/contact records belonging to your account.
CREATE TABLE people (
    id BIGINT NOT NULL PRIMARY KEY,
    account_id BIGINT NOT NULL,
    first_name VARCHAR(256),
    last_name VARCHAR(256),
    properties VARCHAR(MAX) DEFAULT {} -- Custom field values as JSON. Keys match fields configured in Settings > People > Fields.,
    created_at TIMESTAMP NOT NULL,
    updated_at TIMESTAMP NOT NULL,
    assigned_id BIGINT -- The team member (user) assigned to this person.,
    last_conversation_id BIGINT -- The most recent conversation with this person.. FK: conversations.id
);

-- Join records linking tags to people. Each record represents one tag applied to one person.
CREATE TABLE person_taggings (
    id BIGINT NOT NULL PRIMARY KEY,
    tag_id BIGINT -- The tag that was applied.. FK: tags.id,
    person_id BIGINT -- The person the tag was applied to.. FK: people.id,
    created_by_user_id BIGINT -- The team member who applied the tag, if done manually.,
    created_at TIMESTAMP NOT NULL,
    updated_at TIMESTAMP NOT NULL
);

-- Phone numbers associated with people. Each person can have multiple phone numbers.
CREATE TABLE phones (
    id BIGINT NOT NULL PRIMARY KEY,
    person_id BIGINT NOT NULL -- The person this phone number belongs to.. FK: people.id,
    value VARCHAR(256),
    primary BOOLEAN -- Whether this is the person's primary phone number.,
    created_at TIMESTAMP NOT NULL,
    updated_at TIMESTAMP NOT NULL,
    phone_data_id BIGINT,
    sms_opt_out_id BIGINT -- FK: sms_opt_outs.id
);

-- Account-level SMS opt-out records. When a phone number opts out, it applies across the account.
CREATE TABLE sms_opt_outs (
    id BIGINT NOT NULL PRIMARY KEY,
    account_id BIGINT,
    phone_number VARCHAR(256) -- The phone number that opted out.,
    created_at TIMESTAMP NOT NULL,
    updated_at TIMESTAMP NOT NULL,
    source_type VARCHAR(256) -- Type of record that triggered the opt-out.,
    source_id BIGINT -- ID of the record that triggered the opt-out.,
    reason VARCHAR(256) -- Reason for the opt-out (e.g., keyword reply, manual, carrier).,
    message_id BIGINT -- The message that triggered the opt-out, if applicable.. FK: messages.id
);

-- Communication channel opt-in/opt-out status for each person (email, SMS, voice, postal).
CREATE TABLE subscriptions (
    id BIGINT NOT NULL PRIMARY KEY,
    person_id BIGINT -- The person this subscription belongs to.. FK: people.id,
    subscribed_at TIMESTAMP,
    opt_out_at TIMESTAMP,
    source_type VARCHAR(256),
    source_id BIGINT,
    source_kind VARCHAR(256),
    opt_out_reason VARCHAR(256) -- Reason for opt-out, if applicable.,
    channel VARCHAR(50) NOT NULL DEFAULT email -- Communication channel: email, sms, voice, or postal.,
    created_at TIMESTAMP NOT NULL,
    updated_at TIMESTAMP NOT NULL,
    opt_out_source_type VARCHAR(256),
    opt_out_source_id BIGINT,
    phone_id BIGINT -- FK: phones.id,
    email_id BIGINT -- FK: emails.id
);

-- Tags/labels that can be applied to people for segmentation and organization.
CREATE TABLE tags (
    id BIGINT NOT NULL PRIMARY KEY,
    account_id BIGINT,
    name VARCHAR(256) -- The display name of the tag.,
    slug VARCHAR(256),
    created_at TIMESTAMP NOT NULL,
    updated_at TIMESTAMP NOT NULL,
    kind VARCHAR(256) NOT NULL DEFAULT Person -- The type of record this tag applies to.
);

-- Group: Automations

-- Individual runs of an automation for a specific action/event. Tracks execution status and progress.
CREATE TABLE automation_executions (
    id BIGINT NOT NULL PRIMARY KEY,
    action_id BIGINT -- The ID of the triggering action.,
    current_step_id BIGINT -- The step currently being executed.. FK: automation_steps.id,
    created_at TIMESTAMP NOT NULL,
    updated_at TIMESTAMP NOT NULL,
    sidekiq_job_id VARCHAR(256),
    status VARCHAR(256) NOT NULL DEFAULT running -- Execution status (running, completed, etc.).,
    action_type VARCHAR(256) NOT NULL DEFAULT Action -- The type of triggering action.,
    automation_version_id BIGINT -- The automation version being executed.. FK: automation_versions.id,
    skipped_reason_execution_id BIGINT -- FK: automation_executions.id,
    person_id BIGINT -- FK: people.id
);

-- Individual steps within an automation version, executed in order.
CREATE TABLE automation_steps (
    id BIGINT NOT NULL PRIMARY KEY,
    step_action_type VARCHAR(256) -- The type of action this step performs.,
    step_action_id BIGINT -- The ID of the action configuration.,
    position BIGINT -- Order position within the automation.,
    created_at TIMESTAMP NOT NULL,
    updated_at TIMESTAMP NOT NULL,
    automation_version_id BIGINT -- The automation version this step belongs to.. FK: automation_versions.id
);

-- Versioned snapshots of automation configurations. Only published versions are active.
CREATE TABLE automation_versions (
    id BIGINT NOT NULL PRIMARY KEY,
    integration_automatable_type VARCHAR(256),
    integration_automatable_id VARCHAR(36),
    created_at TIMESTAMP NOT NULL,
    updated_at TIMESTAMP NOT NULL,
    version INTEGER NOT NULL DEFAULT 1 -- Sequential version number.,
    status VARCHAR(256) NOT NULL DEFAULT draft -- Whether this version is draft or published.,
    published_at TIMESTAMP,
    automation_id BIGINT -- The automation this version belongs to.. FK: automations.id
);

-- Automated workflows triggered by events. Each automation has versions with defined trigger conditions and steps.
CREATE TABLE automations (
    id BIGINT NOT NULL PRIMARY KEY,
    account_id BIGINT NOT NULL,
    name VARCHAR(256) NOT NULL -- Display name of the automation.,
    managed_by_flow BOOLEAN NOT NULL -- Whether this automation is managed by a flow node rather than standalone.,
    current_version_id BIGINT -- The currently active automation version.. FK: automation_versions.id,
    created_at TIMESTAMP NOT NULL,
    updated_at TIMESTAMP NOT NULL
);

-- Group: Messaging

-- Mass message sends to targeted audiences. Tracks delivery status, scheduling, and A/B test configuration.
CREATE TABLE broadcasts (
    id BIGINT NOT NULL PRIMARY KEY,
    account_id BIGINT,
    user_id BIGINT -- The team member who created the broadcast.,
    created_at TIMESTAMP NOT NULL,
    updated_at TIMESTAMP NOT NULL,
    delivered_at TIMESTAMP,
    delivery_status VARCHAR(256) DEFAULT pending -- Current delivery state of the broadcast.,
    audience VARCHAR(MAX) DEFAULT {},
    name VARCHAR(256),
    audience_size INTEGER,
    delivery_scheduled_at TIMESTAMP,
    goal VARCHAR(256) DEFAULT 'simple',
    campaign_goal_configuration_type VARCHAR(256),
    campaign_goal_configuration_id BIGINT,
    batch_count INTEGER,
    expire_reply_batches BOOLEAN NOT NULL,
    conversation_flow_kind VARCHAR(256),
    delivery_validation_errors VARCHAR(MAX) DEFAULT [],
    subscriptions_opt_outs_count INTEGER NOT NULL DEFAULT 0,
    audience_duplicates_size INTEGER,
    percentage_of_audience_for_testing DECIMAL,
    flow_id BIGINT -- The flow used for this broadcast, if applicable.. FK: flows.id,
    remaining_audience_size INTEGER,
    reply_mode VARCHAR(256) NOT NULL DEFAULT 'inbox',
    delivery_phone_number_id BIGINT,
    tsvector VARCHAR(MAX)
);

-- ActBlue contribution goals linked to broadcasts. Configures donation tracking for campaign messages.
CREATE TABLE campaigns_actblue_contribution_goals (
    id BIGINT NOT NULL PRIMARY KEY,
    refcode VARCHAR(256) -- Custom refcode appended to the ActBlue URL for tracking.,
    page_url VARCHAR(256) -- The ActBlue donation page URL.,
    amount INTEGER -- Suggested donation amount in cents, if configured.,
    recurring VARCHAR(256) -- Recurring donation frequency, if configured.,
    express_lane BOOLEAN -- Whether ActBlue Express Lane is enabled for one-click donations.,
    created_at TIMESTAMP NOT NULL,
    updated_at TIMESTAMP NOT NULL
);

-- Mobilize America attendance goals linked to broadcasts. Configures event signup tracking for campaign messages.
CREATE TABLE campaigns_mobilize_attendance_goals (
    id BIGINT NOT NULL PRIMARY KEY,
    mobilize_lookup_config_id BIGINT -- The lookup configuration for lookup-type goals.,
    mobilize_america_event_id BIGINT -- The Mobilize America event for event-type goals.. FK: mobilize_america_events.id,
    kind VARCHAR(256) -- Goal type: event, lookup, or timeslot_conversation.,
    created_at TIMESTAMP NOT NULL,
    updated_at TIMESTAMP NOT NULL,
    mobilize_america_event_timeslot_id BIGINT -- The specific event timeslot for timeslot_conversation-type goals.. FK: mobilize_america_event_timeslots.id
);

-- Individual messaging threads between the account and a person.
CREATE TABLE conversations (
    id BIGINT NOT NULL PRIMARY KEY,
    account_id BIGINT,
    card_id BIGINT,
    person_id BIGINT -- The person in this conversation.. FK: people.id,
    created_at TIMESTAMP NOT NULL,
    updated_at TIMESTAMP NOT NULL,
    broadcast_id BIGINT -- The broadcast that initiated this conversation, if applicable.. FK: broadcasts.id,
    read BOOLEAN,
    automation_execution_id BIGINT -- FK: automation_executions.id,
    sms_opt_outs_count INTEGER NOT NULL DEFAULT 0,
    archived_at TIMESTAMP -- When the conversation was archived, if applicable.,
    archived_by_id BIGINT,
    subscriptions_opt_outs_count INTEGER NOT NULL DEFAULT 0,
    last_incoming_message_at TIMESTAMP,
    message_content_variant_id BIGINT -- FK: message_content_variants.id,
    automation_send_message_id BIGINT,
    sms_keyword_id BIGINT -- The SMS keyword that initiated this conversation, if applicable.. FK: sms_keywords.id
);

-- A/B test content variants for broadcasts. Each variant has its own message body and can track separate delivery statistics.
CREATE TABLE message_content_variants (
    id BIGINT NOT NULL PRIMARY KEY,
    broadcast_id BIGINT NOT NULL -- The broadcast this variant belongs to.. FK: broadcasts.id,
    name VARCHAR(256) -- Display name of the variant.,
    body VARCHAR(256) DEFAULT '' -- The message text content for this variant.,
    created_at TIMESTAMP NOT NULL,
    updated_at TIMESTAMP NOT NULL,
    winner_at TIMESTAMP -- When this variant was marked as the A/B test winner, if applicable.,
    sample_message_id BIGINT -- A representative message sent with this variant content.. FK: messages.id,
    letter VARCHAR(256) -- Single-letter identifier (A-Z) for the variant within its broadcast.,
    initial_audience_size INTEGER NOT NULL DEFAULT 0 -- Number of recipients initially assigned to this variant.,
    subscriptions_opt_outs_count INTEGER NOT NULL DEFAULT 0 -- Number of subscription opt-outs attributed to this variant.
);

-- Individual SMS/MMS messages within conversations. Tracks direction, delivery status, and billing.
CREATE TABLE messages (
    id BIGINT NOT NULL PRIMARY KEY,
    conversation_id BIGINT -- The conversation this message belongs to.. FK: conversations.id,
    subaccount_id BIGINT,
    previous_message_id BIGINT -- FK: messages.id,
    user_id BIGINT -- The team member who sent the message, for manual replies.,
    direction VARCHAR(50) NOT NULL DEFAULT outgoing -- Whether the message was incoming or outgoing.,
    status VARCHAR(50) DEFAULT pending -- Delivery status (pending, sent, delivered, failed, etc.).,
    error_code INTEGER -- Carrier error code if delivery failed.,
    external_id VARCHAR(256) -- Message ID from the SMS provider.,
    from VARCHAR(256),
    to VARCHAR(256),
    body VARCHAR(256) -- The message text content.,
    created_at TIMESTAMP NOT NULL,
    updated_at TIMESTAMP NOT NULL,
    subaccount_type VARCHAR(256),
    segment_count INTEGER -- Number of SMS segments this message was split into.,
    billing_kind VARCHAR(50) NOT NULL -- Billing category (sms, mms, etc.).,
    intent VARCHAR(256),
    classified_intent VARCHAR(256),
    segment_count_recalculated INTEGER,
    opt_out BOOLEAN NOT NULL,
    accepted_at TIMESTAMP,
    embedding VARCHAR(MAX),
    potential_opt_out BOOLEAN,
    flow_node_id BIGINT -- The flow node that generated this message, if applicable.. FK: flow_nodes.id,
    needs_reply BOOLEAN NOT NULL
);

-- Individual terms/words that trigger an SMS keyword.
CREATE TABLE sms_keyword_terms (
    id BIGINT NOT NULL PRIMARY KEY,
    sms_keyword_id BIGINT NOT NULL -- The SMS keyword this term belongs to.. FK: sms_keywords.id,
    term VARCHAR(256) NOT NULL -- The word or phrase that triggers the keyword.,
    account_id BIGINT,
    delivery_phone_number_id BIGINT,
    created_at TIMESTAMP NOT NULL,
    updated_at TIMESTAMP NOT NULL
);

-- SMS keywords that trigger automated responses or flows when texted to your number.
CREATE TABLE sms_keywords (
    id BIGINT NOT NULL PRIMARY KEY,
    account_id BIGINT,
    delivery_phone_number_id BIGINT,
    response_message VARCHAR(MAX) -- The auto-reply message sent when this keyword is received.,
    triggers_opt_in BOOLEAN NOT NULL,
    starts_new_conversation BOOLEAN NOT NULL DEFAULT TRUE,
    created_at TIMESTAMP NOT NULL,
    updated_at TIMESTAMP NOT NULL,
    flow_id BIGINT -- The flow triggered by this keyword, if applicable.. FK: flows.id
);

-- Group: Flows

-- Follow-up actions triggered after a message is sent within a flow.
CREATE TABLE flow_message_followups (
    id BIGINT NOT NULL PRIMARY KEY,
    followup_target_type VARCHAR(256) NOT NULL -- Type of message being followed up (Message or SimulatedMessage).,
    followup_target_id BIGINT NOT NULL -- ID of the message being followed up.,
    status VARCHAR(50) NOT NULL DEFAULT pending,
    followup_attempts INTEGER NOT NULL DEFAULT 0,
    last_processed_at TIMESTAMP,
    next_follow_up_at TIMESTAMP,
    resolved_at TIMESTAMP,
    created_at TIMESTAMP NOT NULL,
    updated_at TIMESTAMP NOT NULL
);

-- Configuration for automation type flow nodes.
CREATE TABLE flow_node_automation_configurations (
    id BIGINT NOT NULL PRIMARY KEY,
    automation_id BIGINT NOT NULL -- FK: automation_versions.id,
    created_at TIMESTAMP NOT NULL,
    updated_at TIMESTAMP NOT NULL
);

-- Links between flow nodes and automation versions that execute when the node is reached.
CREATE TABLE flow_node_automations (
    id BIGINT NOT NULL PRIMARY KEY,
    flow_node_id BIGINT NOT NULL -- The flow node that triggers this automation.. FK: flow_nodes.id,
    flow_version_id BIGINT NOT NULL -- The flow version this link belongs to.. FK: flow_versions.id,
    automation_version_id BIGINT NOT NULL -- The automation version to execute.. FK: automation_versions.id,
    created_at TIMESTAMP NOT NULL,
    updated_at TIMESTAMP NOT NULL
);

-- Configuration for AI intelligence type flow nodes.
CREATE TABLE flow_node_intelligence_configurations (
    id BIGINT NOT NULL PRIMARY KEY,
    should_send_reply BOOLEAN NOT NULL DEFAULT TRUE,
    ai_model VARCHAR(256) NOT NULL DEFAULT '@anthropic/claude-sonnet-4-6',
    created_at TIMESTAMP NOT NULL,
    updated_at TIMESTAMP NOT NULL,
    inactivity_followup_enabled BOOLEAN NOT NULL,
    followup_delay_minutes INTEGER DEFAULT 5,
    max_followups INTEGER DEFAULT 1,
    reasoning_setting VARCHAR(256) NOT NULL DEFAULT automatic,
    legislative_lookup_config_id BIGINT,
    instructions_content VARCHAR(MAX),
    inactivity_followup_instructions VARCHAR(MAX)
);

-- Custom field extraction settings for AI intelligence nodes.
CREATE TABLE flow_node_intelligence_custom_field_configurations (
    id BIGINT NOT NULL PRIMARY KEY,
    intelligence_configuration_id BIGINT NOT NULL -- The intelligence node configuration this belongs to.. FK: flow_node_intelligence_configurations.id,
    field_configuration_id BIGINT NOT NULL,
    overwrite_mode VARCHAR(256) NOT NULL DEFAULT only_if_blank,
    created_at TIMESTAMP NOT NULL,
    updated_at TIMESTAMP NOT NULL
);

-- Configuration for send-message type flow nodes.
CREATE TABLE flow_node_send_message_configurations (
    id BIGINT NOT NULL PRIMARY KEY,
    body VARCHAR(MAX) NOT NULL -- The message template text.,
    created_at TIMESTAMP NOT NULL,
    updated_at TIMESTAMP NOT NULL
);

-- Individual nodes within a flow version. Each node represents a step in the conversation (send-message, intelligence, or automation).
CREATE TABLE flow_nodes (
    id BIGINT NOT NULL PRIMARY KEY,
    name VARCHAR(256),
    node_type VARCHAR(256) NOT NULL DEFAULT 'intelligence',
    is_starting_node BOOLEAN NOT NULL,
    flow_version_id BIGINT NOT NULL -- The flow version this node belongs to.. FK: flow_versions.id,
    node_configuration_type VARCHAR(256) -- The type of node (SendMessage, Intelligence, or Automation).,
    node_configuration_id BIGINT -- The ID of the node's configuration record.,
    created_at TIMESTAMP NOT NULL,
    updated_at TIMESTAMP NOT NULL,
    position_x INTEGER NOT NULL DEFAULT 0,
    position_y INTEGER NOT NULL DEFAULT 0,
    previous_version_node_id BIGINT -- FK: flow_nodes.id,
    status VARCHAR(50) NOT NULL DEFAULT active -- Whether the node is pending or active.
);

-- Tracks a conversation's current position within a flow.
CREATE TABLE flow_states (
    id BIGINT NOT NULL PRIMARY KEY,
    flow_version_id BIGINT NOT NULL -- The flow version being traversed.. FK: flow_versions.id,
    conversation_id BIGINT NOT NULL -- The ID of the conversation traversing the flow.,
    current_node_id BIGINT NOT NULL -- The flow node the conversation is currently at.. FK: flow_nodes.id,
    state_data VARCHAR(MAX) NOT NULL DEFAULT {},
    created_at TIMESTAMP NOT NULL,
    updated_at TIMESTAMP NOT NULL,
    conversation_type VARCHAR(256) NOT NULL -- The type of conversation (Conversation or SimulatedConversation).,
    status VARCHAR(50) NOT NULL DEFAULT active -- Whether the flow state is active or completed.
);

-- Connections between flow nodes defining the conversation path.
CREATE TABLE flow_transitions (
    id BIGINT NOT NULL PRIMARY KEY,
    name VARCHAR(256) NOT NULL,
    ai_prompt VARCHAR(MAX),
    order INTEGER NOT NULL DEFAULT 0,
    from_node_id BIGINT NOT NULL -- The node this transition starts from.. FK: flow_nodes.id,
    to_node_id BIGINT -- The node this transition leads to.. FK: flow_nodes.id,
    created_at TIMESTAMP NOT NULL,
    updated_at TIMESTAMP NOT NULL
);

-- URL guardrail settings that constrain AI intelligence node responses to approved URLs.
CREATE TABLE flow_url_guardrail_configurations (
    id BIGINT NOT NULL PRIMARY KEY,
    intelligence_configuration_id BIGINT NOT NULL -- The intelligence node configuration this guardrail belongs to.. FK: flow_node_intelligence_configurations.id,
    enabled BOOLEAN NOT NULL,
    allowed_url_patterns VARCHAR(256) NOT NULL DEFAULT '{}',
    created_at TIMESTAMP NOT NULL,
    updated_at TIMESTAMP NOT NULL
);

-- Versioned snapshots of flow configurations. Only published versions are active.
CREATE TABLE flow_versions (
    id BIGINT NOT NULL PRIMARY KEY,
    flow_id BIGINT NOT NULL -- The flow this version belongs to.. FK: flows.id,
    version INTEGER NOT NULL DEFAULT 1 -- Sequential version number.,
    status VARCHAR(256) NOT NULL DEFAULT draft -- Whether this version is draft or published.,
    published_at TIMESTAMP,
    published_version INTEGER,
    created_at TIMESTAMP NOT NULL,
    updated_at TIMESTAMP NOT NULL,
    start_position_x INTEGER,
    start_position_y INTEGER,
    end_position_x INTEGER,
    end_position_y INTEGER
);

-- Conversational flows that guide interactive SMS/messaging experiences.
CREATE TABLE flows (
    id BIGINT NOT NULL PRIMARY KEY,
    name VARCHAR(256) NOT NULL -- Display name of the flow.,
    account_id BIGINT NOT NULL,
    created_at TIMESTAMP NOT NULL,
    updated_at TIMESTAMP NOT NULL,
    flow_states_count INTEGER NOT NULL DEFAULT 0
);

-- Customizable content blocks within flows for personalized messaging.
CREATE TABLE flows_customizable_contents (
    id BIGINT NOT NULL PRIMARY KEY,
    account_id BIGINT NOT NULL,
    flow_id BIGINT -- FK: flows.id,
    kind VARCHAR(256) NOT NULL,
    content VARCHAR(MAX) NOT NULL,
    created_at TIMESTAMP NOT NULL,
    updated_at TIMESTAMP NOT NULL
);

-- Scheduled future resumptions of paused flow conversations.
CREATE TABLE flows_scheduled_conversation_resumptions (
    id BIGINT NOT NULL PRIMARY KEY,
    node_id BIGINT NOT NULL -- The flow node where the conversation will resume.. FK: flow_nodes.id,
    conversation_id BIGINT NOT NULL,
    resume_at TIMESTAMP NOT NULL,
    sidekiq_jid VARCHAR(256),
    created_at TIMESTAMP NOT NULL,
    updated_at TIMESTAMP NOT NULL,
    conversation_type VARCHAR(256) NOT NULL,
    incoming_message_id BIGINT,
    canceled_at TIMESTAMP,
    incoming_message_type VARCHAR(256),
    executed_at TIMESTAMP
);

-- Test/preview conversations for flow development and QA.
CREATE TABLE flows_simulated_conversations (
    id BIGINT NOT NULL PRIMARY KEY,
    flow_id BIGINT NOT NULL -- The flow being simulated.. FK: flows.id,
    person_id BIGINT NOT NULL -- FK: people.id,
    account_id BIGINT NOT NULL,
    created_at TIMESTAMP NOT NULL,
    updated_at TIMESTAMP NOT NULL
);

-- Messages within simulated flow conversations.
CREATE TABLE flows_simulated_messages (
    id BIGINT NOT NULL PRIMARY KEY,
    simulated_conversation_id BIGINT NOT NULL -- The simulated conversation this message belongs to.. FK: flows_simulated_conversations.id,
    previous_message_id BIGINT -- FK: flows_simulated_messages.id,
    body VARCHAR(MAX) -- The message text content.,
    direction VARCHAR(50) NOT NULL -- Whether the message was incoming or outgoing.,
    from VARCHAR(256),
    to VARCHAR(256),
    status VARCHAR(256),
    error_code INTEGER,
    flow_node_id BIGINT -- FK: flow_nodes.id,
    created_at TIMESTAMP NOT NULL,
    updated_at TIMESTAMP NOT NULL,
    needs_reply BOOLEAN NOT NULL
);

-- Group: Fundraise Up

-- Fundraise Up campaign configurations.
CREATE TABLE fundraise_up_campaigns (
    id BIGINT NOT NULL PRIMARY KEY,
    integration_id VARCHAR(36) NOT NULL,
    external_id VARCHAR(256) NOT NULL -- Fundraise Up's ID for this campaign.,
    code VARCHAR(256),
    name VARCHAR(256) -- Campaign name.,
    created_at TIMESTAMP NOT NULL,
    updated_at TIMESTAMP NOT NULL
);

-- Donations received through Fundraise Up integration.
CREATE TABLE fundraise_up_donations (
    id BIGINT NOT NULL PRIMARY KEY,
    integration_id VARCHAR(36) NOT NULL,
    person_id BIGINT NOT NULL -- The matched supporter in your account.. FK: people.id,
    campaign_id BIGINT NOT NULL -- The Fundraise Up campaign.. FK: fundraise_up_campaigns.id,
    fundraiser_id BIGINT -- FK: fundraise_up_fundraisers.id,
    recurring_plan_id BIGINT -- The recurring plan, for recurring donations.. FK: fundraise_up_recurring_plans.id,
    amount INTEGER NOT NULL -- Donation amount.,
    currency VARCHAR(256) NOT NULL,
    amount_in_default_currency INTEGER,
    recurring_plan_installment INTEGER,
    status VARCHAR(50) NOT NULL -- Payment status (succeeded, failed, refunded, pending, etc.).,
    source VARCHAR(256),
    url VARCHAR(256),
    utm_source VARCHAR(256),
    utm_medium VARCHAR(256),
    utm_campaign VARCHAR(256),
    utm_term VARCHAR(256),
    utm_content VARCHAR(256),
    external_id VARCHAR(256) NOT NULL,
    external_receipt_id VARCHAR(256),
    external_created_at TIMESTAMP,
    external_refunded_at TIMESTAMP,
    external_failed_at TIMESTAMP,
    external_succeeded_at TIMESTAMP,
    test_mode BOOLEAN,
    created_at TIMESTAMP NOT NULL,
    updated_at TIMESTAMP NOT NULL
);

-- Individual fundraiser pages within Fundraise Up campaigns.
CREATE TABLE fundraise_up_fundraisers (
    id BIGINT NOT NULL PRIMARY KEY,
    integration_id VARCHAR(36) NOT NULL,
    campaign_id BIGINT NOT NULL -- The campaign this fundraiser belongs to.. FK: fundraise_up_campaigns.id,
    external_id VARCHAR(256) NOT NULL -- Fundraise Up's ID for this fundraiser.,
    name VARCHAR(256) -- Fundraiser page name.,
    created_at TIMESTAMP NOT NULL,
    updated_at TIMESTAMP NOT NULL
);

-- Recurring donation plans set up through Fundraise Up.
CREATE TABLE fundraise_up_recurring_plans (
    id BIGINT NOT NULL PRIMARY KEY,
    integration_id VARCHAR(36) NOT NULL,
    person_id BIGINT NOT NULL -- The supporter with this recurring plan.. FK: people.id,
    campaign_id BIGINT NOT NULL -- The associated campaign.. FK: fundraise_up_campaigns.id,
    fundraiser_id BIGINT -- FK: fundraise_up_fundraisers.id,
    amount INTEGER NOT NULL -- Recurring donation amount.,
    currency VARCHAR(256) NOT NULL,
    amount_in_default_currency INTEGER,
    status VARCHAR(50) NOT NULL -- Plan status (active, cancelled, etc.).,
    frequency VARCHAR(50) NOT NULL -- How often the donation recurs.,
    source VARCHAR(256),
    url VARCHAR(256),
    utm_source VARCHAR(256),
    utm_medium VARCHAR(256),
    utm_campaign VARCHAR(256),
    utm_term VARCHAR(256),
    utm_content VARCHAR(256),
    external_id VARCHAR(256) NOT NULL,
    external_created_at TIMESTAMP,
    external_next_installment_at TIMESTAMP,
    external_ended_at TIMESTAMP,
    test_mode BOOLEAN,
    created_at TIMESTAMP NOT NULL,
    updated_at TIMESTAMP NOT NULL
);

-- Group: Legislative

-- Collections of geographic areas used to group legislative districts.
CREATE TABLE legislative_area_collections (
    id BIGINT NOT NULL PRIMARY KEY,
    name VARCHAR(256) NOT NULL,
    created_at TIMESTAMP NOT NULL,
    updated_at TIMESTAMP NOT NULL
);

-- Geographic areas representing legislative districts and regions.
CREATE TABLE legislative_areas (
    id BIGINT NOT NULL PRIMARY KEY,
    area_collection_id BIGINT NOT NULL -- FK: legislative_area_collections.id,
    name VARCHAR(256) NOT NULL,
    abbreviation VARCHAR(256) NOT NULL,
    fips_code VARCHAR(256),
    region_fips_code VARCHAR(256),
    region VARCHAR(256),
    country VARCHAR(256),
    ocd_division_id VARCHAR(256),
    kind VARCHAR(50) NOT NULL,
    created_at TIMESTAMP NOT NULL,
    updated_at TIMESTAMP NOT NULL
);

-- Political parties and factions that legislators belong to.
CREATE TABLE legislative_factions (
    id BIGINT NOT NULL PRIMARY KEY,
    name VARCHAR(256) NOT NULL,
    created_at TIMESTAMP NOT NULL,
    updated_at TIMESTAMP NOT NULL
);

-- Legislative bodies such as Congress, state legislatures, and city councils.
CREATE TABLE legislative_legislatures (
    id BIGINT NOT NULL PRIMARY KEY,
    name VARCHAR(256) NOT NULL,
    country VARCHAR(256) NOT NULL,
    slug VARCHAR(256) NOT NULL,
    area_collection_id BIGINT NOT NULL -- FK: legislative_area_collections.id,
    created_at TIMESTAMP NOT NULL,
    updated_at TIMESTAMP NOT NULL,
    minimum_lookup_precision VARCHAR(50) NOT NULL DEFAULT postcode
);

-- Links between people, positions, and legislative periods representing who holds which office.
CREATE TABLE legislative_memberships (
    id BIGINT NOT NULL PRIMARY KEY,
    person_id BIGINT NOT NULL -- FK: legislative_people.id,
    legislative_period_id BIGINT NOT NULL -- FK: legislative_periods.id,
    faction_id BIGINT NOT NULL -- FK: legislative_factions.id,
    area_id BIGINT NOT NULL -- FK: legislative_areas.id,
    start_date DATE NOT NULL,
    end_date DATE NOT NULL,
    us_senate_class VARCHAR(50),
    us_senate_state_rank VARCHAR(50),
    url VARCHAR(256),
    address VARCHAR(MAX),
    phone VARCHAR(256),
    fax VARCHAR(256),
    rss_url VARCHAR(256),
    contact_form_url VARCHAR(256),
    created_at TIMESTAMP NOT NULL,
    updated_at TIMESTAMP NOT NULL,
    position_id BIGINT -- FK: legislative_positions.id
);

-- Physical office locations for legislative representatives.
CREATE TABLE legislative_offices (
    id BIGINT NOT NULL PRIMARY KEY,
    membership_id BIGINT NOT NULL -- FK: legislative_memberships.id,
    source_id VARCHAR(256) NOT NULL,
    phone VARCHAR(256),
    fax VARCHAR(256),
    description VARCHAR(256),
    hours VARCHAR(256),
    address VARCHAR(MAX),
    suite VARCHAR(256),
    building VARCHAR(256),
    region VARCHAR(256),
    locality VARCHAR(256),
    country VARCHAR(256),
    postal_code VARCHAR(256),
    latitude DECIMAL,
    longitude DECIMAL,
    time_zone VARCHAR(256),
    visitable BOOLEAN NOT NULL DEFAULT TRUE,
    deleted_at TIMESTAMP,
    created_at TIMESTAMP NOT NULL,
    updated_at TIMESTAMP NOT NULL
);

-- Elected officials and legislative representatives.
CREATE TABLE legislative_people (
    id BIGINT NOT NULL PRIMARY KEY,
    official_full_name VARCHAR(256),
    first_name VARCHAR(256) NOT NULL,
    middle_name VARCHAR(256),
    last_name VARCHAR(256) NOT NULL,
    birth_date DATE,
    bioguide_id VARCHAR(256),
    lis_id VARCHAR(256),
    govtrack_id VARCHAR(256),
    opensecrets_id VARCHAR(256),
    votesmart_id VARCHAR(256),
    wikidata_id VARCHAR(256),
    google_entity_id VARCHAR(256),
    facebook VARCHAR(256),
    twitter VARCHAR(256),
    created_at TIMESTAMP NOT NULL,
    updated_at TIMESTAMP NOT NULL
);

-- Time periods for legislative sessions (e.g. congressional terms).
CREATE TABLE legislative_periods (
    id BIGINT NOT NULL PRIMARY KEY,
    legislature_id BIGINT NOT NULL -- FK: legislative_legislatures.id,
    name VARCHAR(256) NOT NULL,
    start_date DATE NOT NULL,
    end_date DATE NOT NULL,
    slug VARCHAR(256) NOT NULL,
    created_at TIMESTAMP NOT NULL,
    updated_at TIMESTAMP NOT NULL
);

-- Legislative positions such as Senator or Representative for a specific district.
CREATE TABLE legislative_positions (
    id BIGINT NOT NULL PRIMARY KEY,
    area_id BIGINT NOT NULL -- FK: legislative_areas.id,
    legislature_id BIGINT NOT NULL -- FK: legislative_legislatures.id,
    name VARCHAR(256) NOT NULL,
    seat_class VARCHAR(256),
    external_id VARCHAR(256),
    created_at TIMESTAMP NOT NULL,
    updated_at TIMESTAMP NOT NULL,
    sort_order INTEGER DEFAULT 100
);

-- Mappings between postcodes and legislative areas for representative lookup.
CREATE TABLE legislative_postcode_area_mappings (
    id BIGINT NOT NULL PRIMARY KEY,
    postcode_id BIGINT NOT NULL -- FK: postcodes.id,
    area_id BIGINT NOT NULL -- FK: legislative_areas.id,
    legislative_period_id BIGINT NOT NULL -- FK: legislative_periods.id,
    overlap_area BIGINT,
    created_at TIMESTAMP NOT NULL,
    updated_at TIMESTAMP NOT NULL,
    kind VARCHAR(50) NOT NULL DEFAULT proportional
);

-- Postal codes with geographic coordinates for address resolution.
CREATE TABLE postcodes (
    id BIGINT NOT NULL PRIMARY KEY,
    country VARCHAR(256) NOT NULL,
    region VARCHAR(256),
    code VARCHAR(256) NOT NULL,
    latitude DECIMAL,
    longitude DECIMAL,
    created_at TIMESTAMP NOT NULL,
    updated_at TIMESTAMP NOT NULL
);

-- Group: Mobilize America

-- Attendee RSVPs for Mobilize America events.
CREATE TABLE mobilize_america_attendees (
    id BIGINT NOT NULL PRIMARY KEY,
    mobilize_america_event_id BIGINT -- The event this RSVP is for.. FK: mobilize_america_events.id,
    mobilize_america_integration_id VARCHAR(36),
    mobilize_america_event_timeslot_id BIGINT -- FK: mobilize_america_event_timeslots.id,
    external_id VARCHAR(256),
    status VARCHAR(50) -- RSVP status: registered, cancelled, confirmed, or waitlisted.,
    attended BOOLEAN,
    referrer_params VARCHAR(MAX),
    custom_signup_field_values VARCHAR(MAX),
    created_date_in_mobilize TIMESTAMP,
    modified_date_in_mobilize TIMESTAMP,
    created_at TIMESTAMP NOT NULL,
    updated_at TIMESTAMP NOT NULL
);

-- Time slots for Mobilize America events.
CREATE TABLE mobilize_america_event_timeslots (
    id BIGINT NOT NULL PRIMARY KEY,
    mobilize_america_event_id BIGINT -- The event this timeslot belongs to.. FK: mobilize_america_events.id,
    external_id VARCHAR(256) NOT NULL -- Mobilize America's ID for this timeslot.,
    start_at TIMESTAMP,
    end_at TIMESTAMP,
    max_attendees INTEGER,
    is_full BOOLEAN,
    instructions VARCHAR(256),
    created_at TIMESTAMP NOT NULL,
    updated_at TIMESTAMP NOT NULL
);

-- Events synced from Mobilize America.
CREATE TABLE mobilize_america_events (
    id BIGINT NOT NULL PRIMARY KEY,
    mobilize_america_integration_id VARCHAR(36),
    external_id VARCHAR(256) NOT NULL -- Mobilize America's ID for this event.,
    title VARCHAR(256) -- Event title.,
    description VARCHAR(256),
    timezone VARCHAR(256),
    event_type VARCHAR(256) -- Type of event (canvass, phone_bank, rally, etc.).,
    url VARCHAR(256),
    is_virtual BOOLEAN,
    latitude DECIMAL,
    longitude DECIMAL,
    country VARCHAR(256),
    region VARCHAR(256),
    locality VARCHAR(256),
    postal_code VARCHAR(256),
    street_address VARCHAR(256),
    venue VARCHAR(256),
    created_at TIMESTAMP NOT NULL,
    updated_at TIMESTAMP NOT NULL,
    featured_image_url VARCHAR(256),
    high_priority BOOLEAN,
    visibility VARCHAR(256),
    address_visibility VARCHAR(256),
    created_by_volunteer_host BOOLEAN,
    virtual_action_url VARCHAR(256),
    contact VARCHAR(MAX),
    accessibility_status VARCHAR(256),
    accessibility_notes VARCHAR(256),
    tags VARCHAR(MAX),
    approval_status VARCHAR(256),
    event_campaign_id VARCHAR(256),
    instructions VARCHAR(256),
    created_date_in_mobilize TIMESTAMP,
    modified_date_in_mobilize TIMESTAMP,
    tsvector VARCHAR(MAX)
);

-- Group: Questions

-- Predefined answer options for multiple choice and dropdown questions.
CREATE TABLE questions_answers (
    id BIGINT NOT NULL PRIMARY KEY,
    question_id BIGINT NOT NULL -- The question this answer option belongs to.. FK: questions_questions.id,
    value VARCHAR(256) NOT NULL,
    position INTEGER -- Display order position.,
    created_at TIMESTAMP NOT NULL,
    updated_at TIMESTAMP NOT NULL
);

-- Folder hierarchy for organizing questions.
CREATE TABLE questions_folders (
    id BIGINT NOT NULL PRIMARY KEY,
    account_id BIGINT NOT NULL,
    parent_folder_id BIGINT -- The parent folder for nested organization.. FK: questions_folders.id,
    name VARCHAR(256) NOT NULL -- Folder display name.,
    identifier VARCHAR(256) NOT NULL,
    position INTEGER,
    created_at TIMESTAMP NOT NULL,
    updated_at TIMESTAMP NOT NULL
);

-- Groups for organizing related questions together.
CREATE TABLE questions_groups (
    id BIGINT NOT NULL PRIMARY KEY,
    account_id BIGINT NOT NULL,
    name VARCHAR(256) NOT NULL -- Group display name.,
    identifier VARCHAR(256) NOT NULL,
    description VARCHAR(MAX),
    position INTEGER,
    created_at TIMESTAMP NOT NULL,
    updated_at TIMESTAMP NOT NULL
);

-- Individual responses from people to survey questions.
CREATE TABLE questions_person_responses (
    id BIGINT NOT NULL PRIMARY KEY,
    person_id BIGINT NOT NULL -- The person who responded.. FK: people.id,
    question_id BIGINT NOT NULL -- The question being answered.. FK: questions_questions.id,
    answer_id BIGINT -- The selected answer option, for multiple choice/dropdown.. FK: questions_answers.id,
    value VARCHAR(MAX) -- Free-text response value, for text/number/date questions.,
    created_at TIMESTAMP NOT NULL,
    updated_at TIMESTAMP NOT NULL,
    created_by_account_membership_id BIGINT,
    resolved BOOLEAN NOT NULL DEFAULT TRUE,
    survey_response_id BIGINT
);

-- Survey/question definitions for collecting structured data from people.
CREATE TABLE questions_questions (
    id BIGINT NOT NULL PRIMARY KEY,
    account_id BIGINT NOT NULL,
    folder_id BIGINT -- The folder this question is organized in.. FK: questions_folders.id,
    group_id BIGINT -- The question group this belongs to.. FK: questions_groups.id,
    name VARCHAR(256) NOT NULL,
    identifier VARCHAR(256) NOT NULL,
    description VARCHAR(MAX),
    kind VARCHAR(50) NOT NULL DEFAULT short_text -- Question type: multiple_choice, dropdown, boolean, long_text, short_text, number, or date.,
    position INTEGER,
    required BOOLEAN NOT NULL,
    created_at TIMESTAMP NOT NULL,
    updated_at TIMESTAMP NOT NULL,
    question_text VARCHAR(MAX)
);
