-
Notifications
You must be signed in to change notification settings - Fork 0
Production SQL Updates
Clay Benson edited this page Nov 4, 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.
We got a request in Slack to update the requester for a specific IT Gov request.
UPDATE system_intakes SET eua_user_id = 'REDACTED', requester = 'REDACTED', product_manager = 'REDACTED' WHERE id = 'e0760822-1b66-4457-b556-f6045a5fa66b';
UPDATE system_intake_contacts SET eua_user_id = 'REDACTED' WHERE id = '4986a40c-184f-4235-b9e4-abdf743fc880';
UPDATE system_intake_contacts SET eua_user_id = 'REDACTED' WHERE id = '977ddf90-572a-4887-9756-04af3554b34d';I've REDACTED the EUA and Name changes here, but the ID's have been kept so we can always look back and see what was updated.
N/AWe got a request in Slack to update the requester for a specific IT Gov request.
UPDATE system_intakes SET eua_user_id = 'REDACTED', requester = 'REDACTED', business_owner = 'REDACTED' WHERE id = '81106e15-611c-43fa-99e5-d3b28192e80a';
UPDATE system_intake_contacts SET eua_user_id = 'REDACTED' WHERE id = 'b869fbca-8f86-4049-8ad0-ab639589049f';
UPDATE system_intake_contacts SET eua_user_id = 'REDACTED' WHERE id = 'c1a796f1-1e5c-4601-8c09-74b68f3fd994';
I've REDACTED the EUA and Name changes here, but the ID's have been kept so we can always look back and see what was updated.
N/AWe got a request to remove/archive a bunch of test data in EASi Prod. The actual System Intake IDs have been omitted, but can be found in the Jira ticket.
We decided to soft-delete these requests rather than hard-delete, since we don't have ON CASCADE for any of these tables, it would've made hard-deleting these rows a bit more difficult.
-- System Intakes
UPDATE system_intakes SET archived_at = NOW(), updated_at = NOW() WHERE id IN (...)
-- Business Cases
UPDATE business_cases SET updated_at = NOW(), status = 'CLOSED' WHERE system_intake IN (...)N/AWe got a request in Slack to update the requester for a specific IT Gov request.
UPDATE system_intakes SET eua_user_id = 'REDACTED', requester = 'REDACTED' WHERE id = '64ffb28b-2ef4-49bd-a512-ff79e475db3b';
UPDATE system_intake_contacts SET eua_user_id = 'REDACTED' WHERE id = '7b25b870-356d-4c6a-b70c-fc0083268d18';
UPDATE system_intake_contacts SET eua_user_id = 'REDACTED' WHERE id = 'c210cfff-255c-4f9e-91e9-b42b4a8f6d83';I've REDACTED the EUA and Name changes here, but the ID's have been kept so we can always look back and see what was updated.
N/AWe got a request in Slack to update the requester for a specific IT Gov request, as the original requester was leaving the org.
UPDATE system_intakes SET eua_user_id = 'REDACTED', requester = 'REDACTED' WHERE id = 'bb47a6d6-f66a-4b5a-b8e4-f9910593ff9a';
UPDATE business_cases SET eua_user_id = 'REDACTED', requester = 'REDACTED' WHERE id = 'd0a23008-7881-4c31-9e02-6164724e9504';
UPDATE system_intake_contacts SET eua_user_id = 'REDACTED' WHERE id = 'ae409ea8-8f43-4e99-8280-b3882fd47ce9';I've REDACTED the EUA and Name changes here, but the ID's have been kept so we can always look back and see what was updated.
N/AWe 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.
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.*;-- 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', 'ABCD@example.com', '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', 'ABCD@example.com', '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', 'ABCD@example.com', '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', 'ABCD@example.com', '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', 'ABCD@example.com', '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', 'ABCD@example.com', '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');