Non-destructive E2E testing

[mwarman]

Describe the story

Run E2E tests in a manner that most closely resembles the actual application runtime environment but without altering the state of the local or hosted environment. Explore options such as using an in-memory database or a dedicated Docker database.

Option: SQLite

Add support for SQLite to the e2e tests allowing them to setup and tear down the tests at will and without impacting persistent data stores. This will also allow the e2e tests to run in CI workflows.

Option: Docker

Use a dedicated, dockerized PostgreSQL for the E2E tests which may be set up and torn down as needed to facilitate the E2E test criteria.

Acceptance criteria

GIVEN the e2e tests use SQLite
WHEN the e2e tests are run
THEN the SQLite database is set up and torn down dynamically as needed

GIVEN the e2e tests use Docker
When the e2e tests are run
THEN a Postgres database is run within a Docker container
AND the e2e tests interact with the Postgres database as needed

Additional context

Add any other context about the story here.

[mwarman]

SQLite may not be the best approach. SQLite data types differ slightly, particularly timestamps.

Research indicates that a better approach may be to use Docker and run a separate, dedicated Postgres database dedicated for E2E tests.