Follow+Notes: Create backend library for grants collaboration management

[TylerHendrickson]

Subtask of [STORY]: Update 'Status' to 'Follow + Note' feature #2960

Blocked by

• Follow+Notes: Add grant notes tables to database #3200

Blocks

• Follow+Notes: Add grant followers management to collaboration library #3203
• Follow+Notes: Expose grant notes creation in Finder API #3204
• Follow+Notes: Expose grant notes retrieval in Finder API #3205

Definition of Done

A new library, packages/server/src/lib/grantsCollaboration exists and provides an interface for saving and retrieving notes created by users about particular grants.

Implementation Details

• A user may only have one note per grant (which is enforced by a unique constraint on (grant_notes.grant_id, grant_notes.user_id)) but may have infinite revisions.
• This functionality should probably be encapsulated in a grantsCollaboration/notes.js file and imported/exported by a grantsCollaboration/index.js file, which will allow for this library to remain well-organized as it is expanded to provided additional features.
• Create a new library module at packages/server/src/lib/grantsCollaboration which exports the following interface:
  • saveNoteRevision(knex, grantId, userId, text)
    • The knex argument represents a knex connection object, e.g. as exported by packages/server/src/db/index.js.
    • Determines whether a row in the grant_notes table already exists for the given grantId and userId. If a row for this combination does not already exist, a new one must be inserted. In case of an insert, the id of the newly-inserted row must be retained. Otherwise, the id of the existing row must be retained.
    • Using the retained grant_notes.id value, insert a new row into the grant_notes_revisions table with the provided text.
    • Note that none of the provided arguments to this function may be null or undefined or else the database insert operation(s) will fail.
    • To ensure atomic writes, this function should create a database transaction that encapsulates all write operations. The transaction should be committed before the function returns.
    • This function should return an object that matches the following structure:

      { id: grant_notes_revisions.id }

  • getOrganizationNotesForGrant(knex, grantId, organizationId, { afterRevision, limit = 50 } = {})
    • Queries for grant note revisions pertaining to the identified grant and organization.
    • Results should be ordered by grant_notes_revisions.created_at in descending order (most recent first).
    • If a non-null/-undefined value is provided for the afterRevision argument, the query should be limited to results where grant_notes_revisions.id is greater than that value.
    • This function should return an object that matches the following structure:

      {
        notes: [{
          id: grant_notes_revisions.latest_revision_id,
          createdAt: grant_notes_revisions.created_at,
          text: grant_notes_revisions.text,
          grant: { id: grant_notes.grant_id },
          user: {
            id: users.id,
            name: users.name,
            team: {
              id: users.agency_id,
              name: agencies.name,
            },
            organization: {
              id: users.tenant_id,
              name: tenants.name,
            },
          }
        }],
        pagination: {
          from: notes.length > 0 ? notes[notes.length - 1].id : afterRevision,
        },
      }

    • The querying pattern for selecting results requires a somewhat non-obvious (imo) LEFT JOIN LATERAL expression to efficiently limit results to the latest revision of each note. In case it's useful, please refer to the following raw SQL as a demonstration of this pattern:

      SELECT
        grant_notes.id, grant_notes.created_at, grant_notes.grant_id,
        rev.id as latest_revision_id, rev.created_at as revised_at, rev.text,
        users.id as user_id, users.name as user_name, users.email as user_email, 
        tenants.id as organization_id, tenants.display_name as organization_name,
        agencies.id as team_id, agencies.name as team_name
      FROM grant_notes
      LEFT JOIN LATERAL (
        SELECT r.id, r.grant_note_id, r.created_at, r.text
        FROM grant_notes_revisions r
        WHERE r.grant_note_id = grant_notes.id
        ORDER BY r.created_at DESC
        LIMIT 1
      ) AS rev ON rev.grant_note_id = grant_notes.id
      JOIN users ON users.id = grant_notes.user_id
      JOIN agencies ON agencies.id = users.agency_id
      JOIN tenants ON tenants.id = users.tenant_id
      WHERE
        grant_notes.grant_id = :grantId
        AND tenants.id = :organizationId
        -- Optional, if `afterRevision` is provided in the function call:
        AND rev.id > :afterRevision
      -- Not sure if this ORDER BY is strictly necessary, 
      -- as order should be controlled by the LEFT JOIN LATERAL above
      ORDER BY rev.created_at DESC
      LIMIT :limit

    • Worth noting that Knex does not support LEFT JOIN LATERAL, so this will need to be provided as text to a .joinRaw() call on the chained query object.

[ClaireValdivia]

no front end changes to test, but general regression testing looked good on staging!