[STORY]: Data Migration from 'Status' to 'Follow + Note'

[ekhoffman]

Full User Story

As an organization, we would like to foster more collaboration between our users to allow for the sharing of knowledge and resources to empower them to more efficiently and effectively apply for the grants discovered on our platform.

As a grantseeker, I want to be able to easily share grants I’ve discovered with my team.

As a grantseeker, I want to be able to easily find collaborators who may be able to help me when preparing and applying for grants.

As a grantseeker, I want to be able to quickly see what grants my team members are interested in.

As an admin, I want to be able to easily surface and resolve issues and blockers my team is running into.

Why is this issue important?

As we make the move from 'Status' to 'Mark as Interested', we want to ensure that we don't lose any of the work our users have put in to record status so far. Additionally, if we can kickstart usage of this feature with previously entered information, we may be able to boost adoption.

Action Plan

• [ ] Initial design drafted
• [ ] Initial acceptance criteria drafted
• [ ] High level technical approach & feature flag use determined
• [ ] Designs & Acceptance criteria finalized
• [ ] Eng task breakout complete
• [ ] Assessment of whether partner training & communication actions are needed
• [ ] Acceptance criteria testing completed & passed in staging
• [ ] Feature flags enabled in production
• [ ] Feature flags are removed once the feature is deemed stable

Acceptance Criteria

Currently a status is set for an entire team, moving forward 'Following' will apply to an individual user.

1. For the user who set the team status, we will migrate from 'Status' to 'Following' as outlined below.
   • If the current team status is 'Interested - Will Apply', we will set the user who selected this status to 'Following' for that grant and set the Note to 'Will Apply.'
   • If the current team status is 'Interested - Will Consider', we will set the user who selected this status to 'Following' and set the Note to 'Will Consider'.
   • If the current team status is 'Interested - Will Support', we will set the user who selected this status to 'Following' and set the Note to 'Will Support'.

None of this data migration should trigger any notifications

Implementation Details

• Create an initial Knex migration to migrate existing data
  • This migration will need to insert one row into the grant_followers table for each row in the grants_interested table where the grants_interested.interested_code_id references a row in the interested_codes table where interested_codes.status_code = 'Interested'.
  • Example query to get grant_id, user_id, created_at values (i.e. as they would be inserted into grant_followers), while excluding records where the grant_id and user_id are already represented in the grant_followers table:

    SELECT 
      gi.grant_id as grant_id,
      gi.user_id as user_id,
      coalesce(gi.updated_at, gi.created_at) as created_at
    FROM grants_interested gi
    JOIN interested_codes ic ON ic.id = gi.interested_code_id
    WHERE ic.status_code = 'Interested'
    AND (grant_id, user_id) NOT IN (SELECT grant_id, user_id FROM grant_followers);

  • Note that this should be a pure SQL operation using INSERT INTO ... SELECT ... syntax. See this post for more details and examples.
• Modify the PUT /:grantId/interested/:agencyId request handler in packages/server/src/routes/grants.js so that the grant_followers table is kept up-to-date even after the Knex migration (detailed above) has been executed.
  • Following the call to db.markGrantAsInterested(), this endpoint handler should implement the following new functionality as a side-effect of the endpoint:
    • Query the database to check whether the interestedCode provided in the request body is an identifier for a row in the interested_codes table that has `status_code = 'Interested'.
    • If the interestedCode is associated with status_code = 'Interested', then call the followGrant() function exported by packages/server/src/lib/grantsCollaboration so that the user is also a follower.
    • If the interestedCode is not associated with status_code = 'Interested', then call the unfollowGrant() function exported by packages/server/src/lib/grantsCollaboration so that the user is no longer a follower.

[ClaireValdivia]

@amyewang based on the implementation plan, is it possible for initial work to be done for this issue, but then to actually run the migration closer to when we are launching the new feature? ideally we want this migration to be able to be run close to the launch so it migrates as much recent data as possible.

[amyewang]

@ClaireValdivia the second bullet point addresses this consistency issue - once the migration is run, we'll have the endpoint update the grant followers table as well; both of these changes should be deployed together. Once the feature is fully rolled out we can clean up all the team status stuff.