Design endpoints - names, filters, output fields, data structure, sort capability

[lbeaufort]

What we're after:
Design endpoints - names, filters, output fields, data structure, sort capability

Let's work with front-end team and @fec-jli to see if there's any gaps in the endpoint design.

Initial design concept

see research here: https://app.zenhub.com/workspaces/website-project-559bfc62e61e8f964c1ca7de/issues/fecgov/openfec/4170#issuecomment-580795643

Within endpoints include:

• Names
• Filters
• Sort capability
• Output

Completion criteria

• Define all endpoints needed for the presidential map functionality

[lbeaufort]

Draw.io link: https://www.draw.io/#G108WMWi2-V0EOjRuKer8m0uzOu_aJK9M8

Option 1

PRES_CA_CM_SCHED_STATE_20D
PRES_F3P_TOTALS_CA_CM_LINK_20D
PRES_CA_CM_SCHED_A_JOIN_20D
PRES_CA_CM_SCHED_LINK_SUM_20D
PRES_CA_CM_SCHED_STATE_20D

From @fec-jli:

the initial thought we may need 5 endpoints.
current classic pre tables are separated by election cycle (2016 and 2020), so it is better to create a view to union those tables and add new column=cycle, in this way, we can filter by cycle. The endpoint model will base on the view. the max record count is 20K, so performance should be ok. We need database team to help convert to Postgres query.

I just put endpoints list here, after we create issue, we can put inside the tickets.
currently

/*

Option 1

*/

5 endpoints:

1. /presidential/contributions/by_candidate/

(left side cand list; right side summary; Expenditure Tab ):
(view on DISCLOSURE.PRES_F3P_TOTALS_CA_CM_LINK_20D / _16)
Filter: cand_id, cycle

SELECT distinct 
cand_id AS cand_id, 
cand_nm AS candidate_name,
SUBSTR(cand_nm, 0, strpos(cand_nm,',')) AS cand_last_name, 
(CASE WHEN cand_pty_affiliation IS NULL THEN NULL ELSE '('||SUBSTR(CAND_PTY_AFFILIATION,1,1)||')' END) AS cand_pty_affiliation, 
ROUND(((INDV_CONTB_PER - REF_INDV_CONTB_PER) + 
   (POL_PTY_CMTE_CONTB_PER - REF_POL_PTY_CMTE_CONTB_PER) + 
   (OTHER_POL_CMTE_CONTB_PER - REF_OTHER_POL_CMTE_CONTB_PER) + 
   CAND_CONTB_PER + TRANF_FROM_AFFILATED_CMTE_PER + 
   (LOANS_RECEIVED_FROM_CAND_PER - REPYMTS_LOANS_MADE_BY_CAND_PER) + 
   (OTHER_LOANS_RECEIVED_PER - REPYMTS_OTHER_LOANS_PER) + OTHER_RECEIPTS_PER 
   + COALESCE(FED_FUNDS_PER,0))/1000000,1) AS net_receipts ,
*
FROM DISCLOSURE.PRES_F3P_TOTALS_CA_CM_LINK_20D
ORDER BY 4 DESC

===========================================================

2. /presidential/contributions/by_state/

(national map circle per candidate): (view on DISCLOSURE.PRES_CA_CM_SCHED_A_JOIN_20D / _16 Filter: cand_id, cycle
P00000001

SELECT 
cand_id AS cand_id, 
--cand_nm,
contbr_st as contbr_st,
contb_receipt_amt as contb_receipt_amt,
*
FROM
DISCLOSURE.PRES_CA_CM_SCHED_A_JOIN_20D
WHERE cand_id= 'P00000001' 
AND CONTBR_ST IN ('CA', 'NY', 'TX', 'FL', 'IL', 'VA', 'MA', 'DC', 'NJ', 'MD', 'PA', 'WA', 'CT', 'GA', 'CO', 'OH', 'AZ', 'NC', 'MI', 'TN', 'NM', 'MO', 'MN', 'OR', 'UT', 'WI', 'NV', 'SC', 'IN', 'OK', 'LA', 'AL', 'KY', 'AR', 'NH', 'IA', 'KS', 'HI', 'ME', 'MS', 'RI', 'DE', 'VT', 'ID', 'NE', 'MT', 'WV',  'AK', 'WY', 'SD', 'ND') 
AND ZIP_3 ='NA'
ORDER BY cand_id desc nulls first limit 100

===========================================================

3. /presidential/contributions/by_size/

(national map right side contribution size)
(DISCLOSURE.PRES_CA_CM_SCHED_LINK_SUM_20D / _16)
Filter: cand_id, cycle

SELECT contb_range_id AS contb_range_id, 
ROUND(contb_receipt_amt) AS contb_receipt_amt 
FROM DISCLOSURE.PRES_CA_CM_SCHED_LINK_SUM_20D
WHERE cand_id ='P00009621'  order by contb_range_id

===========================================================

4. /presidential/coverage_date/

(dateThrough) (DISCLOSURE.PRES_NML_FORM_3P_20D / _16)
Filter: cand_id, cycle

select to_char(max(cvg_end_dt), 'mm/dd/yyyy')
from DISCLOSURE.PRES_NML_FORM_3P_20D
where cand_id='P00009621' 
 

===========================================================

5. /presidential/contributions/by_candidate/state_filter/ <<???

(State Map) get all candidate list
(view on DISCLOSURE.PRES_CA_CM_SCHED_STATE_20D / _16)
Filter: contributor_state, cycle

SELECT CAND_ID as cand_id,
CAND_PTY_AFFILIATION as cand_pty_affiliation, 
CAND_NM  as cand_nm, 
ROUND (SUM(NET_RECEIPTS_STATE )) as net_receipts_state,
contbr_st
FROM DISCLOSURE.PRES_CA_CM_SCHED_STATE_20D
WHERE contbr_st='VA' 
GROUP BY cand_id,cand_pty_affiliation,Cand_nm, contbr_st
ORDER BY 4 desc

===========================================================

Option 2

/*

Option 2

*/

5 endpoints:

1. /presidential/contributions/by_candidate/

combine state data with national data

• TODO- Add 2016 tables

(left side cand list, national and state wide get all candidate list)
(view on DISCLOSURE.PRES_CA_CM_SCHED_STATE_20D / _16 - state data
UNION PRES_F3P_TOTALS_CA_CM_LINK_20D/ _16 - national data)
Filter: contributor_state, election_year

Include rounded and original totals

--US totals
SELECT DISTINCT
cand_id AS candidate_id, 
--TRIM(TRAILING ',' FROM cand_nm) AS candidate_name, --remove trailing commas
SUBSTR(cand_nm, 0, strpos(cand_nm,',')) AS candidate_last_name, 
(CASE WHEN cand_pty_affiliation IS NULL THEN NULL ELSE '('||SUBSTR(CAND_PTY_AFFILIATION,1,1)||')' END) AS candidate_party_affiliation, 
((INDV_CONTB_PER - REF_INDV_CONTB_PER) + 
    (POL_PTY_CMTE_CONTB_PER - REF_POL_PTY_CMTE_CONTB_PER) + 
    (OTHER_POL_CMTE_CONTB_PER - REF_OTHER_POL_CMTE_CONTB_PER) + 
    CAND_CONTB_PER + TRANF_FROM_AFFILATED_CMTE_PER + 
    (LOANS_RECEIVED_FROM_CAND_PER - REPYMTS_LOANS_MADE_BY_CAND_PER) + 
    (OTHER_LOANS_RECEIVED_PER - REPYMTS_OTHER_LOANS_PER) + OTHER_RECEIPTS_PER 
    + COALESCE(FED_FUNDS_PER,0)) AS net_receipts,
ROUND(((INDV_CONTB_PER - REF_INDV_CONTB_PER) + 
    (POL_PTY_CMTE_CONTB_PER - REF_POL_PTY_CMTE_CONTB_PER) + 
    (OTHER_POL_CMTE_CONTB_PER - REF_OTHER_POL_CMTE_CONTB_PER) + 
    CAND_CONTB_PER + TRANF_FROM_AFFILATED_CMTE_PER + 
    (LOANS_RECEIVED_FROM_CAND_PER - REPYMTS_LOANS_MADE_BY_CAND_PER) + 
    (OTHER_LOANS_RECEIVED_PER - REPYMTS_OTHER_LOANS_PER) + OTHER_RECEIPTS_PER 
    + COALESCE(FED_FUNDS_PER,0))/1000000,1) AS rounded_net_receipts,
'US' AS contributor_state,
2020 as election_year -- TODO: Bring in other 2016 data
FROM DISCLOSURE.PRES_F3P_TOTALS_CA_CM_LINK_20D
UNION
-- Per-state totals
SELECT CAND_ID as candidate_id,
CAND_NM  as candidate_last_name, 
CAND_PTY_AFFILIATION as candidate_party_affiliation, 
SUM(NET_RECEIPTS_STATE) AS net_receipts,
ROUND (SUM(NET_RECEIPTS_STATE )/1000000,1) as rounded_net_receipts, --per state
contbr_st as contributor_state,
2020 as election_year -- TODO: Bring in other 2016 data
FROM DISCLOSURE.PRES_CA_CM_SCHED_STATE_20D
GROUP BY candidate_id,candidate_party_affiliation,candidate_last_name, contributor_state
ORDER BY contributor_state, rounded_net_receipts DESC

2. /presidential/financial_summary/

(right side summary; Expenditure Tab ):
(view on DISCLOSURE.PRES_F3P_TOTALS_CA_CM_LINK_20D / _16)
Filter: candidate_id, election_year

-- TODO: Determine financial summary fields, use readable column names

SELECT distinct 
cand_id AS candidate_id, 
cand_nm AS candidate_name,
SUBSTR(cand_nm, 0, strpos(cand_nm,',')) AS candidate_last_name, 
(CASE WHEN cand_pty_affiliation IS NULL THEN NULL ELSE '('||SUBSTR(CAND_PTY_AFFILIATION,1,1)||')' END) AS candidate_party_affiliation, 
*
FROM DISCLOSURE.PRES_F3P_TOTALS_CA_CM_LINK_20D
ORDER BY 4 DESC

===========================================================

3. /presidential/contributions/by_size/

(national map right side contribution size)
(DISCLOSURE.PRES_CA_CM_SCHED_LINK_SUM_20D / _16)
Filter: candidate_id, election_year

TODO: Consider using "size" filter like /schedules/schedule_a/by_size/ instead (in addition to? ) contribution_range_id

The total all contributions in the following ranges:

-0 $200 and under
-200 $200.01 - $499.99
-500 $500 - $999.99
-1000 $1000 - $1999.99
-2000 $2000 +

SELECT contb_range_id AS contribution_range_id, 
-- TODO: Add size? more readable for devs
ROUND(contb_receipt_amt) AS contribution_receipt_amount 
FROM DISCLOSURE.PRES_CA_CM_SCHED_LINK_SUM_20D
order by contb_range_id

===========================================================

4. /presidential/contributions/by_state/

(national map circle per candidate):
(view on DISCLOSURE.PRES_CA_CM_SCHED_A_JOIN_20D / _16
Filter: candidate_id, election_year

SELECT 
cand_id AS candidate_id, 
contbr_st as contributor_state,
contb_receipt_amt as contribution_receipt_amount,
election_yr as election_year
FROM
DISCLOSURE.PRES_CA_CM_SCHED_A_JOIN_20D
WHERE CONTBR_ST IN ('CA', 'NY', 'TX', 'FL', 'IL', 'VA', 'MA', 'DC', 'NJ', 'MD', 'PA', 'WA', 'CT', 'GA', 'CO', 'OH', 'AZ', 'NC', 'MI', 'TN', 'NM', 'MO', 'MN', 'OR', 'UT', 'WI', 'NV', 'SC', 'IN', 'OK', 'LA', 'AL', 'KY', 'AR', 'NH', 'IA', 'KS', 'HI', 'ME', 'MS', 'RI', 'DE', 'VT', 'ID', 'NE', 'MT', 'WV',  'AK', 'WY', 'SD', 'ND') 
AND ZIP_3 ='NA'
ORDER BY cand_id desc nulls first limit 100

===========================================================

5. /presidential/coverage_date/

(dateThrough) (DISCLOSURE.PRES_NML_FORM_3P_20D / _16)
Filter: candidate_id, election_year

--TODO: Bring in 2016 data

select cand_id AS candidate_id,
to_char(max(cvg_end_dt), 'mm/dd/yyyy') as coverage_date,
'2020' as election_year
from DISCLOSURE.PRES_NML_FORM_3P_20D

===========================================================

[lbeaufort]

As a team, we decided we'll go with option 2. I updated the SQL, endpoint numbers, and the draw.io drawing (https://www.draw.io/#G108WMWi2-V0EOjRuKer8m0uzOu_aJK9M8) based on some conversations. Some additional work noted as TODOS.

PRES_CA_CM_SCHED_STATE_20D
PRES_F3P_TOTALS_CA_CM_LINK_20D
PRES_CA_CM_SCHED_A_JOIN_20D
PRES_CA_CM_SCHED_LINK_SUM_20D
PRES_CA_CM_SCHED_STATE_20D

From @fec-jli:

the initial thought we may need 5 endpoints.
current classic pre tables are separated by election cycle (2016 and 2020), so it is better to create a view to union those tables and add new column=cycle, in this way, we can filter by cycle. The endpoint model will base on the view. the max record count is 20K, so performance should be ok. We need database team to help convert to Postgres query.

I just put endpoints list here, after we create issue, we can put inside the tickets.
currently

Per @patphongs, use election_year instead of cycle

/*

Option 2

*/

5 endpoints:

===========================================================

1) /presidential/contributions/by_candidate/

combine state data with national data

• TODO- Add 2016 tables

(left side cand list, national and state wide get all candidate list)
(view on DISCLOSURE.PRES_CA_CM_SCHED_STATE_20D / _16 - state data
UNION PRES_F3P_TOTALS_CA_CM_LINK_20D/ _16 - national data)
Filter: contributor_state, election_year

Include rounded and original totals

--US totals
SELECT DISTINCT
cand_id AS candidate_id, 
--TRIM(TRAILING ',' FROM cand_nm) AS candidate_name, --remove trailing commas
SUBSTR(cand_nm, 0, strpos(cand_nm,',')) AS candidate_last_name, 
(CASE WHEN cand_pty_affiliation IS NULL THEN NULL ELSE '('||SUBSTR(CAND_PTY_AFFILIATION,1,1)||')' END) AS candidate_party_affiliation, 
((INDV_CONTB_PER - REF_INDV_CONTB_PER) + 
    (POL_PTY_CMTE_CONTB_PER - REF_POL_PTY_CMTE_CONTB_PER) + 
    (OTHER_POL_CMTE_CONTB_PER - REF_OTHER_POL_CMTE_CONTB_PER) + 
    CAND_CONTB_PER + TRANF_FROM_AFFILATED_CMTE_PER + 
    (LOANS_RECEIVED_FROM_CAND_PER - REPYMTS_LOANS_MADE_BY_CAND_PER) + 
    (OTHER_LOANS_RECEIVED_PER - REPYMTS_OTHER_LOANS_PER) + OTHER_RECEIPTS_PER 
    + COALESCE(FED_FUNDS_PER,0)) AS net_receipts,
ROUND(((INDV_CONTB_PER - REF_INDV_CONTB_PER) + 
    (POL_PTY_CMTE_CONTB_PER - REF_POL_PTY_CMTE_CONTB_PER) + 
    (OTHER_POL_CMTE_CONTB_PER - REF_OTHER_POL_CMTE_CONTB_PER) + 
    CAND_CONTB_PER + TRANF_FROM_AFFILATED_CMTE_PER + 
    (LOANS_RECEIVED_FROM_CAND_PER - REPYMTS_LOANS_MADE_BY_CAND_PER) + 
    (OTHER_LOANS_RECEIVED_PER - REPYMTS_OTHER_LOANS_PER) + OTHER_RECEIPTS_PER 
    + COALESCE(FED_FUNDS_PER,0))/1000000,1) AS rounded_net_receipts,
'US' AS contributor_state,
2020 as election_year -- TODO: Bring in other 2016 data
FROM DISCLOSURE.PRES_F3P_TOTALS_CA_CM_LINK_20D
UNION
-- Per-state totals
SELECT CAND_ID as candidate_id,
CAND_NM  as candidate_last_name, 
CAND_PTY_AFFILIATION as candidate_party_affiliation, 
SUM(NET_RECEIPTS_STATE) AS net_receipts,
ROUND (SUM(NET_RECEIPTS_STATE )/1000000,1) as rounded_net_receipts, --per state
contbr_st as contributor_state,
2020 as election_year -- TODO: Bring in other 2016 data
FROM DISCLOSURE.PRES_CA_CM_SCHED_STATE_20D
GROUP BY candidate_id,candidate_party_affiliation,candidate_last_name, contributor_state
ORDER BY contributor_state, rounded_net_receipts DESC

===========================================================

2) /presidential/financial_summary/

(right side summary; Expenditure Tab ):
(view on DISCLOSURE.PRES_F3P_TOTALS_CA_CM_LINK_20D / _16)
Filter: candidate_id, election_year

-- TODO: Determine financial summary fields, use readable column names. Maybe bring them all over as readable columns in case we need others later

SELECT distinct 
cand_id AS candidate_id, 
cand_nm AS candidate_name,
SUBSTR(cand_nm, 0, strpos(cand_nm,',')) AS candidate_last_name, 
(CASE WHEN cand_pty_affiliation IS NULL THEN NULL ELSE '('||SUBSTR(CAND_PTY_AFFILIATION,1,1)||')' END) AS candidate_party_affiliation, 
*
FROM DISCLOSURE.PRES_F3P_TOTALS_CA_CM_LINK_20D
ORDER BY 4 DESC

===========================================================

3) /presidential/contributions/by_size/

(national map right side contribution size)
(DISCLOSURE.PRES_CA_CM_SCHED_LINK_SUM_20D / _16)
Filter: candidate_id, election_year

TODO: Consider using "size" filter like /schedules/schedule_a/by_size/ instead (in addition to? ) contribution_range_id

The total all contributions in the following ranges:

-0 $200 and under
-200 $200.01 - $499.99
-500 $500 - $999.99
-1000 $1000 - $1999.99
-2000 $2000 +

SELECT contb_range_id AS contribution_range_id, 
-- TODO: Add size? more readable for devs
ROUND(contb_receipt_amt) AS contribution_receipt_amount 
FROM DISCLOSURE.PRES_CA_CM_SCHED_LINK_SUM_20D
order by contb_range_id

===========================================================

4) /presidential/contributions/by_state/

(national map circle per candidate):
(view on DISCLOSURE.PRES_CA_CM_SCHED_A_JOIN_20D / _16
Filter: candidate_id, election_year

SELECT 
cand_id AS candidate_id, 
contbr_st as contributor_state,
contb_receipt_amt as contribution_receipt_amount,
election_yr as election_year
FROM
DISCLOSURE.PRES_CA_CM_SCHED_A_JOIN_20D
WHERE CONTBR_ST IN ('CA', 'NY', 'TX', 'FL', 'IL', 'VA', 'MA', 'DC', 'NJ', 'MD', 'PA', 'WA', 'CT', 'GA', 'CO', 'OH', 'AZ', 'NC', 'MI', 'TN', 'NM', 'MO', 'MN', 'OR', 'UT', 'WI', 'NV', 'SC', 'IN', 'OK', 'LA', 'AL', 'KY', 'AR', 'NH', 'IA', 'KS', 'HI', 'ME', 'MS', 'RI', 'DE', 'VT', 'ID', 'NE', 'MT', 'WV',  'AK', 'WY', 'SD', 'ND') 
AND ZIP_3 ='NA'
ORDER BY cand_id desc nulls first limit 100

===========================================================

5) /presidential/coverage_date/

(dateThrough) (DISCLOSURE.PRES_NML_FORM_3P_20D / _16)
Filter: candidate_id, election_year

--TODO: Bring in 2016 data

select cand_id AS candidate_id,
to_char(max(cvg_end_dt), 'mm/dd/yyyy') as coverage_date,
'2020' as election_year
from DISCLOSURE.PRES_NML_FORM_3P_20D

===========================================================