Skip to content

Migrate saved searches into dedicated Postgres table

Warren Gifford requested to merge fa/migrate-saved-searches into master

Created by: attfarhan

Part of https://github.com/sourcegraph/sourcegraph/issues/3469. Fixes https://github.com/sourcegraph/sourcegraph/issues/3208.

This PR migrates saved searches from site settings into a dedicated PostgreSQL table.

Currently, saved searches are stored in user/org/global site settings. This is bad because:

  • Updating settings from outside the settings editor is complex, causing bugs such as https://github.com/sourcegraph/sourcegraph/issues/3208.
  • We've had to hack unique keys into the saved searches object and use odd "Spec" types (which include the hacky unique key and settings subject) to identify saved searches.
  • Storing saved searches in settings is inflexible. It'll be odd to store additional values (e.g. whether a saved search notification has been read, a list of specific emails to notify, etc.) in settings.
  • It's annoying to query for saved searches via our GQL API -- you'd need to query for settings and then parse the JSON for search.savedQueries. You should be able to query for saved searches directly.

Moving this to the DB fixes existing bugs and makes it easier to move forward on following saved search work.

This PR includes:

  • The migration logic to move existing saved searches from settings to a new saved_searches Postgres table. See migration SQL, Go script
  • New GraphQL mutations to create/update/delete saved searches.
  • Updates to the GraphQL API, to rename the SavedQuery type to SavedSearch, and remove now-irrelevant fields from that type. This is a breaking change to the API but @slimsag and I agreed that it's very unlikely that there are consumers of this API other than our app.
  • Updates to query-runner such that it reads from the saved_searches DB table. Query runner now reads from the saved_searches table at a fixed interval. Previously, the frontend would notify the query-runner every time a saved search was created/updated/deleted.
  • Migration logic to move Slack webhook URLs from user/global/org settings into the slack_webhook_url column in the saved_searches table, and updates to make sure consumers of the SlackWebhookURL read from the DB table rather than settings.

This PR also surfaced some refactoring TODOs for the future:

  1. Move the query-runner into the frontend. Removes the need for query-runner to request the list of saved searches and run queries over the network.
  2. Update the SavedQuerySpecAndConfig and SavedQueryIDSpec types to be simpler and more logical. We can get rid of a lot of the nasty complexity introduced by this type because we aren't using settings anymore. This might be easier once query-runner is moved to the frontend.

Remaining TODOs:

  • Migrate organization slackWebhookURLs into saved queries table. Do this so we can associate Slack webhook URLs with each saved search rather than with the org.
  • Make query runner read the Slack webhook from the DB table.
  • Clean up: rename savedQueries to savedSearches where appropriate, remove/deprecate savedQuery-related types and mutations in GraphQL API.

In a separate PR (since this is not a regression):

  • Update query_runner_state table to use a key other than the saved search's query. If there are two saved searches with the same query, only one of the owners gets notified.

Merge request reports