Skip to content

Production SQL Updates

Jump to bottom
Clay Benson edited this page Jan 18, 2024 · 7 revisions

This page is meant to capture and document SQL queries/updates that we've run against our production database (and are NOT captured in Flyway migrations).

These likely will never be run again, but having a historical record of what was done can be helpful! If adding to this page, simply copy the example and paste it to the top of the list.

History

2024-01-18, Updating System Intakes to have LCID Retire Dates

Description

We ran this update in production in order to update System Intake records that were in the NO_GOVERNANCE status to have a LCID retire date associated with them. The reason for this is because, historically (in IT Gov V1), the IT Governance team would take the Close Project action (which results in setting a System Intake's status to NO_GOVERNANCE and creating an action of type NO_GOVERNANCE_NEEDED) in order to represent a "Retired" LCID, which, at the time, didn't exist as a concept.

Script(s)

WITH actions as (
    SELECT
    actions.id as action_id,
    actions.intake_id as action_intake_id,
    actions.created_at as action_created_at

    FROM system_intakes
    LEFT join actions on actions.intake_id = system_intakes.id AND actions.action_type = 'NO_GOVERNANCE_NEEDED'
),

intakes_to_update as (
SELECT
system_intakes.id, 
(SELECT  action_created_at FROM actions WHERE actions.action_intake_id = system_intakes.id ORDER BY action_created_at DESC LIMIT 1) as no_governance_time,
status,
decision_state,
lcid,
step
      FROM system_intakes WHERE status = 'NO_GOVERNANCE' AND lcid IS NOT NULL AND lcid_retires_at IS NULL

)

--SELECT * FROM
--    intakes_to_update, system_intakes
--WHERE
--    system_intakes.id = intakes_to_update.id AND intakes_to_update.no_governance_time IS NOT NULL;

UPDATE system_intakes
SET 
    lcid_retires_at = intakes_to_update.no_governance_time
FROM 
    intakes_to_update
WHERE
    system_intakes.id = intakes_to_update.id AND intakes_to_update.no_governance_time IS NOT NULL
RETURNING
system_intakes.id,
system_intakes.lcid,
system_intakes.decision_state,
system_intakes.step,
system_intakes.lcid_retires_at,
intakes_to_update.*,
system_intakes.*;

Test Data

-- LCID 1, no governance action and status no governance, no lcid retires at
INSERT INTO "public"."system_intakes"("id", "eua_user_id", "requester", "component", "business_owner", "business_owner_component", "product_manager", "product_manager_component", "project_name", "existing_funding", "business_need", "solution", "process_status", "ea_support_request", "existing_contract", "status", "updated_at", "submitted_at", "created_at", "lcid", "lcid_expires_at", "lcid_scope", "contractor", "request_type", "grt_date", "grb_date", "decision_next_steps", "isso_name", "trb_collaborator_name", "oit_security_collaborator_name", "ea_collaborator_name", "contract_start_date", "contract_end_date", "lcid_cost_baseline", "cedar_system_id", "contract_number", "has_ui_changes", "request_form_state", "draft_business_case_state", "final_business_case_state", "decision_state", "step", "state", "current_annual_spending", "planned_year_one_spending", "trb_follow_up_recommendation", "lcid_issued_at") VALUES('fec8e351-809c-4af2-bd0d-197b6b433206', 'USR1', 'User One', 'Office of the Actuary', 'Ally Anderson', 'Office of Minority Health', 'Hallie O''Hara', 'Emergency Preparedness and Response Operations', 'LCID issued, but reopened', 'TRUE', 'An intense business need', 'with a great business solution', 'Some CEDAR System ID', 'TRUE', 'HAVE_CONTRACT', 'NO_GOVERNANCE', '2024-01-17 22:16:57.130717+00', '2024-01-17 22:16:56.884693+00', '2024-01-17 22:16:56.776999+00', '000001', '2025-01-17 22:16:57.102192+00', 'scope for this lcid', 'Dr Doom', 'NEW', '2023-11-17 22:16:56.918599+00', '2023-12-17 22:16:56.776996+00', 'next steps for this intake', 'Leatha Gorczany', 'Mrs TRB member', 'Ms security team member', 'Mr Enterprise Architecture', '2023-01-17', '2027-01-17', 'cost baseline for this request', 'the current stage', '123456789', 'TRUE', 'SUBMITTED', 'SUBMITTED', 'SUBMITTED', 'LCID_ISSUED', 'DECISION_AND_NEXT_STEPS', 'OPEN', 'It''s kind of a lot', 'A little bit more', 'NOT_RECOMMENDED', '2024-01-17 22:16:57.1078+00');
INSERT INTO "public"."actions"("id", "action_type", "actor_name", "actor_email", "actor_eua_user_id", "created_at", "intake_id", "feedback", "step") VALUES('fec8e351-809c-4af2-bd0d-197b6b433206', 'NO_GOVERNANCE_NEEDED', 'ABCDDoe', '[email protected]', 'ABCD', '2024-01-10 22:17:04.104945+00', 'fec8e351-809c-4af2-bd0d-197b6b433206', 'additional info for INITIAL_REQUEST_FORM progressing to DRAFT_BUSINESS_CASE', 'DRAFT_BUSINESS_CASE');
-- LCID 2, no governance action and status no governance -- LCID already has retires at
INSERT INTO "public"."system_intakes"("id", "eua_user_id", "requester", "component", "business_owner", "business_owner_component", "product_manager", "product_manager_component", "project_name", "existing_funding", "business_need", "solution", "process_status", "ea_support_request", "existing_contract", "status", "updated_at", "submitted_at", "created_at", "lcid", "lcid_expires_at", "lcid_scope", "contractor", "request_type", "grt_date", "grb_date", "decision_next_steps", "isso_name", "trb_collaborator_name", "oit_security_collaborator_name", "ea_collaborator_name", "contract_start_date", "contract_end_date", "lcid_cost_baseline", "cedar_system_id", "contract_number", "has_ui_changes", "request_form_state", "draft_business_case_state", "final_business_case_state", "decision_state", "step", "state", "current_annual_spending", "planned_year_one_spending", "trb_follow_up_recommendation", "lcid_retires_at", "lcid_issued_at") VALUES('fec8e351-809c-4af2-bd0d-197b6b433207', 'USR1', 'User One', 'Office of the Actuary', 'Ally Anderson', 'Office of Minority Health', 'Hallie O''Hara', 'Emergency Preparedness and Response Operations', 'LCID issued, but reopened', 'TRUE', 'An intense business need', 'with a great business solution', 'Some CEDAR System ID', 'TRUE', 'HAVE_CONTRACT', 'NO_GOVERNANCE', '2024-01-17 22:16:57.130717+00', '2024-01-17 22:16:56.884693+00', '2024-01-17 22:16:56.776999+00', '000002', '2025-01-17 22:16:57.102192+00', 'scope for this lcid', 'Dr Doom', 'NEW', '2023-11-17 22:16:56.918599+00', '2023-12-17 22:16:56.776996+00', 'next steps for this intake', 'Leatha Gorczany', 'Mrs TRB member', 'Ms security team member', 'Mr Enterprise Architecture', '2023-01-17', '2027-01-17', 'cost baseline for this request', 'the current stage', '123456789', 'TRUE', 'SUBMITTED', 'SUBMITTED', 'SUBMITTED', 'LCID_ISSUED', 'DECISION_AND_NEXT_STEPS', 'OPEN', 'It''s kind of a lot', 'A little bit more', 'NOT_RECOMMENDED', '2024-01-17 22:17:08.488013+00', '2024-01-17 22:16:57.1078+00');
INSERT INTO "public"."actions"("id", "action_type", "actor_name", "actor_email", "actor_eua_user_id", "created_at", "intake_id", "feedback", "step") VALUES('fec8e351-809c-4af2-bd0d-197b6b433207', 'NO_GOVERNANCE_NEEDED', 'ABCDDoe', '[email protected]', 'ABCD', '2024-01-11 22:17:04.104945+00', 'fec8e351-809c-4af2-bd0d-197b6b433207', 'additional info for INITIAL_REQUEST_FORM progressing to DRAFT_BUSINESS_CASE', 'DRAFT_BUSINESS_CASE');
-- LCID 3, no governance action and status no governance, no lcid retires at
INSERT INTO "public"."system_intakes"("id", "eua_user_id", "requester", "component", "business_owner", "business_owner_component", "product_manager", "product_manager_component", "project_name", "existing_funding", "business_need", "solution", "process_status", "ea_support_request", "existing_contract", "status", "updated_at", "submitted_at", "created_at", "lcid", "lcid_expires_at", "lcid_scope", "contractor", "request_type", "grt_date", "grb_date", "decision_next_steps", "isso_name", "trb_collaborator_name", "oit_security_collaborator_name", "ea_collaborator_name", "contract_start_date", "contract_end_date", "lcid_cost_baseline", "cedar_system_id", "contract_number", "has_ui_changes", "request_form_state", "draft_business_case_state", "final_business_case_state", "decision_state", "step", "state", "current_annual_spending", "planned_year_one_spending", "trb_follow_up_recommendation", "lcid_issued_at") VALUES('fec8e351-809c-4af2-bd0d-197b6b433208', 'USR1', 'User One', 'Office of the Actuary', 'Ally Anderson', 'Office of Minority Health', 'Hallie O''Hara', 'Emergency Preparedness and Response Operations', 'LCID issued, but reopened', 'TRUE', 'An intense business need', 'with a great business solution', 'Some CEDAR System ID', 'TRUE', 'HAVE_CONTRACT', 'NO_GOVERNANCE', '2024-01-17 22:16:57.130717+00', '2024-01-17 22:16:56.884693+00', '2024-01-17 22:16:56.776999+00', '000003', '2025-01-17 22:16:57.102192+00', 'scope for this lcid', 'Dr Doom', 'NEW', '2023-11-17 22:16:56.918599+00', '2023-12-17 22:16:56.776996+00', 'next steps for this intake', 'Leatha Gorczany', 'Mrs TRB member', 'Ms security team member', 'Mr Enterprise Architecture', '2023-01-17', '2027-01-17', 'cost baseline for this request', 'the current stage', '123456789', 'TRUE', 'SUBMITTED', 'SUBMITTED', 'SUBMITTED', 'LCID_ISSUED', 'DECISION_AND_NEXT_STEPS', 'OPEN', 'It''s kind of a lot', 'A little bit more', 'NOT_RECOMMENDED', '2024-01-17 22:16:57.1078+00');
INSERT INTO "public"."actions"("id", "action_type", "actor_name", "actor_email", "actor_eua_user_id", "created_at", "intake_id", "feedback", "step") VALUES('fec8e351-809c-4af2-bd0d-197b6b433208', 'NO_GOVERNANCE_NEEDED', 'ABCDDoe', '[email protected]', 'ABCD', '2024-01-12 22:17:04.104945+00', 'fec8e351-809c-4af2-bd0d-197b6b433208', 'additional info for INITIAL_REQUEST_FORM progressing to DRAFT_BUSINESS_CASE', 'DRAFT_BUSINESS_CASE');
-- LCID 4, no governance action and status no governance, no lcid retires at
INSERT INTO "public"."system_intakes"("id", "eua_user_id", "requester", "component", "business_owner", "business_owner_component", "product_manager", "product_manager_component", "project_name", "existing_funding", "business_need", "solution", "process_status", "ea_support_request", "existing_contract", "status", "updated_at", "submitted_at", "created_at", "lcid", "lcid_expires_at", "lcid_scope", "contractor", "request_type", "grt_date", "grb_date", "decision_next_steps", "isso_name", "trb_collaborator_name", "oit_security_collaborator_name", "ea_collaborator_name", "contract_start_date", "contract_end_date", "lcid_cost_baseline", "cedar_system_id", "contract_number", "has_ui_changes", "request_form_state", "draft_business_case_state", "final_business_case_state", "decision_state", "step", "state", "current_annual_spending", "planned_year_one_spending", "trb_follow_up_recommendation", "lcid_issued_at") VALUES('fec8e351-809c-4af2-bd0d-197b6b433209', 'USR1', 'User One', 'Office of the Actuary', 'Ally Anderson', 'Office of Minority Health', 'Hallie O''Hara', 'Emergency Preparedness and Response Operations', 'LCID issued, but reopened', 'TRUE', 'An intense business need', 'with a great business solution', 'Some CEDAR System ID', 'TRUE', 'HAVE_CONTRACT', 'NO_GOVERNANCE', '2024-01-17 22:16:57.130717+00', '2024-01-17 22:16:56.884693+00', '2024-01-17 22:16:56.776999+00', '000004', '2025-01-17 22:16:57.102192+00', 'scope for this lcid', 'Dr Doom', 'NEW', '2023-11-17 22:16:56.918599+00', '2023-12-17 22:16:56.776996+00', 'next steps for this intake', 'Leatha Gorczany', 'Mrs TRB member', 'Ms security team member', 'Mr Enterprise Architecture', '2023-01-17', '2027-01-17', 'cost baseline for this request', 'the current stage', '123456789', 'TRUE', 'SUBMITTED', 'SUBMITTED', 'SUBMITTED', 'LCID_ISSUED', 'DECISION_AND_NEXT_STEPS', 'OPEN', 'It''s kind of a lot', 'A little bit more', 'NOT_RECOMMENDED', '2024-01-17 22:16:57.1078+00');
INSERT INTO "public"."actions"("id", "action_type", "actor_name", "actor_email", "actor_eua_user_id", "created_at", "intake_id", "feedback", "step") VALUES('fec8e351-809c-4af2-bd0d-197b6b433209', 'NO_GOVERNANCE_NEEDED', 'ABCDDoe', '[email protected]', 'ABCD', '2024-01-13 22:17:04.104945+00', 'fec8e351-809c-4af2-bd0d-197b6b433209', 'additional info for INITIAL_REQUEST_FORM progressing to DRAFT_BUSINESS_CASE', 'DRAFT_BUSINESS_CASE');
-- LCID 5, no governance action and status CLOSED, no lcid retires at
INSERT INTO "public"."system_intakes"("id", "eua_user_id", "requester", "component", "business_owner", "business_owner_component", "product_manager", "product_manager_component", "project_name", "existing_funding", "business_need", "solution", "process_status", "ea_support_request", "existing_contract", "status", "updated_at", "submitted_at", "created_at", "lcid", "lcid_expires_at", "lcid_scope", "contractor", "request_type", "grt_date", "grb_date", "decision_next_steps", "isso_name", "trb_collaborator_name", "oit_security_collaborator_name", "ea_collaborator_name", "contract_start_date", "contract_end_date", "lcid_cost_baseline", "cedar_system_id", "contract_number", "has_ui_changes", "request_form_state", "draft_business_case_state", "final_business_case_state", "decision_state", "step", "state", "current_annual_spending", "planned_year_one_spending", "trb_follow_up_recommendation", "lcid_issued_at") VALUES('fec8e351-809c-4af2-bd0d-197b6b433210', 'USR1', 'User One', 'Office of the Actuary', 'Ally Anderson', 'Office of Minority Health', 'Hallie O''Hara', 'Emergency Preparedness and Response Operations', 'LCID issued, but reopened', 'TRUE', 'An intense business need', 'with a great business solution', 'Some CEDAR System ID', 'TRUE', 'HAVE_CONTRACT', 'CLOSED', '2024-01-17 22:16:57.130717+00', '2024-01-17 22:16:56.884693+00', '2024-01-17 22:16:56.776999+00', '000005', '2025-01-17 22:16:57.102192+00', 'scope for this lcid', 'Dr Doom', 'NEW', '2023-11-17 22:16:56.918599+00', '2023-12-17 22:16:56.776996+00', 'next steps for this intake', 'Leatha Gorczany', 'Mrs TRB member', 'Ms security team member', 'Mr Enterprise Architecture', '2023-01-17', '2027-01-17', 'cost baseline for this request', 'the current stage', '123456789', 'TRUE', 'SUBMITTED', 'SUBMITTED', 'SUBMITTED', 'LCID_ISSUED', 'DECISION_AND_NEXT_STEPS', 'OPEN', 'It''s kind of a lot', 'A little bit more', 'NOT_RECOMMENDED', '2024-01-17 22:16:57.1078+00');
INSERT INTO "public"."actions"("id", "action_type", "actor_name", "actor_email", "actor_eua_user_id", "created_at", "intake_id", "feedback", "step") VALUES('fec8e351-809c-4af2-bd0d-197b6b433210', 'NO_GOVERNANCE_NEEDED', 'ABCDDoe', '[email protected]', 'ABCD', '2024-01-14 22:17:04.104945+00', 'fec8e351-809c-4af2-bd0d-197b6b433210', 'additional info for INITIAL_REQUEST_FORM progressing to DRAFT_BUSINESS_CASE', 'DRAFT_BUSINESS_CASE');
-- LCID 6, no governance action and status no governance, no lcid retires at
INSERT INTO "public"."system_intakes"("id", "eua_user_id", "requester", "component", "business_owner", "business_owner_component", "product_manager", "product_manager_component", "project_name", "existing_funding", "business_need", "solution", "process_status", "ea_support_request", "existing_contract", "status", "updated_at", "submitted_at", "created_at", "lcid", "lcid_expires_at", "lcid_scope", "contractor", "request_type", "grt_date", "grb_date", "decision_next_steps", "isso_name", "trb_collaborator_name", "oit_security_collaborator_name", "ea_collaborator_name", "contract_start_date", "contract_end_date", "lcid_cost_baseline", "cedar_system_id", "contract_number", "has_ui_changes", "request_form_state", "draft_business_case_state", "final_business_case_state", "decision_state", "step", "state", "current_annual_spending", "planned_year_one_spending", "trb_follow_up_recommendation", "lcid_issued_at") VALUES('fec8e351-809c-4af2-bd0d-197b6b433211', 'USR1', 'User One', 'Office of the Actuary', 'Ally Anderson', 'Office of Minority Health', 'Hallie O''Hara', 'Emergency Preparedness and Response Operations', 'LCID issued, but reopened', 'TRUE', 'An intense business need', 'with a great business solution', 'Some CEDAR System ID', 'TRUE', 'HAVE_CONTRACT', 'NO_GOVERNANCE', '2024-01-17 22:16:57.130717+00', '2024-01-17 22:16:56.884693+00', '2024-01-17 22:16:56.776999+00', '000006', '2025-01-17 22:16:57.102192+00', 'scope for this lcid', 'Dr Doom', 'NEW', '2023-11-17 22:16:56.918599+00', '2023-12-17 22:16:56.776996+00', 'next steps for this intake', 'Leatha Gorczany', 'Mrs TRB member', 'Ms security team member', 'Mr Enterprise Architecture', '2023-01-17', '2027-01-17', 'cost baseline for this request', 'the current stage', '123456789', 'TRUE', 'SUBMITTED', 'SUBMITTED', 'SUBMITTED', 'LCID_ISSUED', 'DECISION_AND_NEXT_STEPS', 'OPEN', 'It''s kind of a lot', 'A little bit more', 'NOT_RECOMMENDED', '2024-01-17 22:16:57.1078+00');
-- INSERT INTO "public"."actions"("id", "action_type", "actor_name", "actor_email", "actor_eua_user_id", "created_at", "intake_id", "feedback", "step") VALUES('fec8e351-809c-4af2-bd0d-197b6b433211', 'NO_GOVERNANCE_NEEDED', 'ABCDDoe', '[email protected]', 'ABCD', '2024-01-13 22:17:04.104945+00', 'fec8e351-809c-4af2-bd0d-197b6b433211', 'additional info for INITIAL_REQUEST_FORM progressing to DRAFT_BUSINESS_CASE', 'DRAFT_BUSINESS_CASE');
Clone this wiki locally