remove direct SQL db edits

[josh-chamberlain]

Context

To the extent it's possible, we should use API endpoints to make changes to the database, never direct SQL edits.

From @maxachis here:

Things such as the Google Agency searcher, as designed, would be inserting data directly into the database. And then there are some things, such as user roles, which don't appear to be governed by an API endpoint and so must be modified through direct SQL queries. As a heuristic (for my own sake rather than yours), any SQL string located in a file that isn't in the data-sources-app repository (and isn't a test) would strongly suggest someplace that's modifying the database outside of an endpoint.

Requirements

• establish an ADR / policy about when we can have SQL in the code, what kinds of exceptions to allow
  • possible exceptions:
    • testing & migrations
    • ?
• use the data-sources-app-v2 fork; make changes to dev, eventually merge to main
• locate cases of SQL which changes the database, and list the cases somewhere we can discuss them
  • SQL located in a file outside data-sources-app repo
  • SQL located outside endpoints in data-sources-app repo
  • keywords like INSERT, UPDATE, DELETE, ALTER, CREATE, DROP, TRUNCATE
• identify which endpoints + logic would be needed to replace
• pause, discuss
• do the thing; replace the SQL with API calls. split it up into chunks/child issues of this one if needed
• any time SQL affects the db, we should be logging it; especially if it's not through an endpoint

Open questions

• will we need to worry about auth for new endpoints?
• when should we do this relative to other work?

[maxachis]

@josh-chamberlain I'm marking this as post-v2 because I think :

1. This will be much easier to navigate once we are freed from Airtable
2. Similarly, once we're done with the old v1 environment, it'll be easier to develop new endpoints