Skip to content

Data Structures

Jump to bottom
Nathaly Toledo edited this page Nov 10, 2022 · 14 revisions 
classDiagram
    campaigns "1" --> "0...n" letter_versions: can have
    sent_letters "0...n" -->"1" letter_versions: corresponds to
    constituents "1"--> "0...n" sent_letters:receives
    stripe_payments_table "1" --> "1" sent_letters: is linked to
    class campaigns{
      +int id 
      +string organization
      +string page_url
      +string name
      +int letters_goal
      +int donation_goal
      +string logo_url
      +enum cause
      +enum type
      +getCampaign()
      +getCampaigns()
      
    }
    class letter_versions{
      +int id
      +int template_id
      +enum office_division
      +string state
      +string country
      +string municipality
      +integer campaign_id
      +getLetterVersions()
    
    }
    class sent_letters{
      +int id
      +int letter_version_id
      +int constituent_id
      +int request_id
      +timestampt requested_at
      +string rep_name
      +string rep_address 
      +createLetters()
      
    }

    class constituents{
        +string first_name
        +string last_name
        +int letters_sent
        +boolean user_agreement
        +string street_address
        +string address_two
        +string city
        +string state
        +string zip
    }

    class stripe_payments_table{
        +int id
        +timestamp day 
        +int amount
        +string currency
        +int source_id
        +string type
        +createTransaction()
    }

campaigns table

Describes all campaigns and the number of letters sent for each campaign.

API reference

API reference Retrieve information for all campaigns listed in the table or just one.

Data description

Column Name Data Type Description
id integer Auto-increments with each new campaign added. (Not editable.)
organization string The name of the organization responsible for this campaign.
name string The name of this particular campaign.
cause enum Must be one of: Civic Rights, Education, Climate Justice The cause this campaign is supporting.
type enum Must be one of: Starter, Accelerator, Grant The type of campaign this is.
page_url text The URL for this campaign's call to action.
letters_counter integer The number of letters sent for this campaign.

(Editor's note: Could not determine when/how letters_counter is modified)

Example

CampaignTable

letter_versions table

This data is sent to Lob, primarily template_id which is specific to each campaign by office division. This data structure triages the letter object that's being displayed and sent, specific to the template_id of the region that's being picked by the office.

API reference

API reference Returns information about every letter sent for a campaign.

Data description

Column Name Data Type Description
id integer Auto-increments with each new letter version added. (Not editable.) This is used to create join or belong relationships between Users, Campaigns, and Letters Sent.
template_id string The Lob html letter template id.
office_division enum Must be one of: Federal, State, County, Municipality. Default: Federal Each campaign contains a different letter to enable filtering.
state string The state this letter version is for.
county string The county this letter version is for.
CampaignID integer Maps this letter to a campaign

Example

​​LetterVersions

sent_letters table

A way to understand volumes of letters being sent. This table is used to measure conversion rates for letter_upload, constituent and user_campaign.

API reference

None.

Data description

Column Name Data Type Description
id integer Auto-increments with each new letter sent added. (Not editable.)
letter_version_id integer Foreign key that references id in the letter_versions table
constituent_id integer Foreign key that references id in the constituents table
request_id string The Lob API response ID, for tracking and management purposes
requested_at timestamp
rep_name string
rep_address string

Example

letter_sent

constituents table

This information is collected from the review letter screen.

API reference

None.

Data description

Column Name Data Type Description
Letters sent Integer The id of a letter that was successfully posted with Lob (payment and address verification went through)
User agreement Boolean User agrees to abide by the platform's best practices
Updates Sends sending campaign_id so that we can send to advocacy groups so they can follow up and by our user education team *
Street address string
City string
State string
Zipcode string

* Editor's note: This sentence was left as written.

Example

constituent

Stripe Payments Table

API reference

None.

Data description

Column Name Data Type Description
day timestamp mm-dd-yyyy
id *
amount integer The amount of the charge or refund.
currency string Currency identifier
source_id *
type string Payment type: charge or refund

* (Editor's note: Do not know what id and source_id are. It may be that one is the Amplify database identifier and the other is the Stripe identifier, so they can be mapped to each other.)

Example

stripe

Clone this wiki locally