Configure preferences of updates to constituent through notification table

[manishapriya94]

Update constituent table to include a notifications table of preferences

Background info

Data types in constituent table:

• name is a string
• Street address, City, and State are strings
• Zipcode is a string
• Letters sent holds an array of ids of letter that was successfully posted with - Lob (payment and address verification went through)
• User agreement is a boolean that they abide by the platforms outlined = community safety and protection guidelines
• Updates is sending campaign_id so that we can send to advocacy groups so they can follow up and by our user education team

Notifications table from Updates field in constituent table:

• string message
• date to be sent out
• campaign id (we'll draw the letter from here)

Directions:

*Note that you will need to import the knex object and the express middleware in your Express app for this code to work.

To create a table for updates in the constituent table that includes a string message, date, and campaign id, you can create a new migration file using Knex. Here are the steps you can follow:

• 1.Create a new migration file using the Knex CLI:
  npx knex migrate:make create_updates_table
• 2. In the new migration file, create a new table called updates with columns for the message, date, and campaign id:

exports.up = function(knex) {
  return knex.schema.createTable('updates', (table) => {
    table.increments('id').primary()
    table.string('message').notNullable()
    table.date('date').notNullable()
    table.integer('campaign_id').unsigned().notNullable()
    table.foreign('campaign_id').references('campaigns.id')
    table.integer('constituent_id').unsigned().notNullable()
    table.foreign('constituent_id').references('constituent.id')
    table.timestamps(true, true)
  })
}

exports.down = function(knex) {
  return knex.schema.dropTable('updates')
}

This code creates a new table called updates with columns for the message, date, campaign id, constituent id, and timestamps.

• 3. Run the migration to create the updates table:
  npx knex migrate:latest
• 4. In your Express app, modify the route that updates the constituent table to also insert a new row into the updates table:

router.post('/update-constituent', async (req, res) => {
  const { id, message, campaign_id } = req.body

  try {
    // Update the constituent table
    await knex('constituent').where({ id }).update({ message })

    // Insert a new row into the updates table
    await knex('updates').insert({
      message,
      date: new Date(),
      campaign_id,
      constituent_id: id
    })

    res.sendStatus(200)
  } catch (error) {
    console.error(error)
    res.status(500).send('Internal server error')
  }
})

This code updates the constituent table with the new message, and then inserts a new row into the updates table with the message, current date, campaign id, and constituent id.