Migration to Postgres

[neongreen]

Postgres

• Design schemas for core types
• Design schemas for pending_edits and users
• Write table creation code
• Write table queries (see Guide.State)
• Write table updates (add, modify)
• Use a class instead of explicitly specifying encoders
• Add deleted to the categories table
• Think about reorganizing get/set/modify functions, will be done by @neongreen.
• Go over postgres-related TODOs in code, will be done by @neongreen.
• Add functions for working with pending edits.
• Encode all database invariants in a separate module
• Check database invariants every day (if possible)

Tests

• Make sure transactions are aborted when an error is thrown (either a hasql error or a custom error).
• Make sure items and traits are put into the right position after deleting and restoring an item/trait.
• Add, get, modify work properly.
• Conversion to/from rows works properly.
• Items and categories can still be edited when they are deleted.

What other tests do we need?

acid-state

• Add a flag for loading the database and saving it into Postgres, will be done by @willbasky.
• Add loading/saving of edits into Postgres.
• Remove acid-state code after the migration is done in production.
• Deprecate safecopy-migrate and archive the repository, will be done by @neongreen.

Authentication

We're going to switch to authentication via Github or something. The old session store will only be used for Spock and it's okay if it will be in memory only.

• Move the old session store into memory and out of GlobalState.

Infrastructure

• Set up database backups (DigitalOcean should have that automatically).
• Publish database dumps (see https://www.postgresql.org/docs/11/backup-dump.html), perhaps host them on DO Spaces. See https://www.notion.so/aelve/DigitalOcean-Spaces-128576da71f144b9aa738147c9d89265.
• Kill https://github.com/aelve/guide-database.