DBM

DataBase Management. Provides initialization of our "standard" db users and common tables.

ENV

Several DB connection urls are used in initialization

export DATABASE_URL_DBA=postgresql://postgres@$DBHOST:$DBPORT/
export DATABASE_URL_DDL=postgresql://${PROJECT_NAME}_ddl@$DBHOST:$DBPORT/$PROJECT_NAME
export DATABASE_URL_CONFIG=postgresql://${PROJECT_NAME}_config@$DBHOST:$DBPORT/$PROJECT_NAME

Passwords as set via:

export DATABASE_DDL_PASSWORD=
export DATABASE_CONFIG_PASSWORD=
export DATABASE_DELETE_PASSWORD=
export DATABASE_WRITE_PASSWORD=
export DATABASE_READ_PASSWORD=

db-config

Additional DB connection urls are provided via the db-config:

postgresql://${PROJECT_NAME}_read@$DBHOST:$DBPORT/$PROJECT_NAME
postgresql://${PROJECT_NAME}_write@$DBHOST:$DBPORT/$PROJECT_NAME
postgresql://${PROJECT_NAME}_delete@$DBHOST:$DBPORT/$PROJECT_NAME

Note about SSL

When using ssl use both sslmode=required and ssl=true in the connection arguments as different drivers use on or the other.

Scripts

db-init (and db-init-*)

db-init initializes the database (assumes it exists and is empty). This is done by running db-init-dba, db-init-ddl, db-init-config and loading ./data/seed.sql using the ddl user. Note: the ddl user can only create or alter tables in the data schema and add functions in the func schema; extensions must be loaded by the dba user when the database is created.

db-init-dba creates 5 users, ddl for running seed and db migrations, config for accessing the config table, read, write, and delete for the application to use as appropriate. The applications users cannot alter schema.

db-snapshot

Produce a snapshot of the db in ./data/snaphosts.

db-restore-last

(not done yet) will eventually restore the last snapshot taken.

db-new-migration description

Creates an empty sql file in ./data/migrations named date_time_description. Edit this file with SQL for the ddl user to apply.

db-up

Applies migrations from ./data/migrations in date/time order that haven't already been applied. Use DRY_RUN=1 yarn db-up to apply and rollback the migration. Eventually the intent is to use db-snapshot, db-up, and db-restore-last to develop migrations (and avoid having to write "down" migrations).

Baseline Schema

All tables use tuids for ids or are single-data tables for enforcing set membership.

DBM provides the tables:

• config: dynamic configuration data
• users: to track users of the system
• user_emails: used for mapping oauth based login to users
• sessions: used to track session (including anonymous users)
• _data_migrations: used to track applied migrations
• permissions: valid permission names in the system
• permission_groups: valid permission group names in the system
• user_permissions: what permissions a user has
• user_permission_groups: what permission groups a user has
• permissions_permission_groups: what permissions a permission group has

In general these tables are not directly accessed (the exception, currently, is adding and granting permissions), and instead the library code is used.

Library Code

db-provider

Intended for use by the application, resolves to three dbPromise instances, one for each read, write, and delete. Ensure that all actions that need to see modifications are wrapped in a tx as these will create overlapped connections to the db when used.

db-config

Resolves to the current dynamic configuration as read from the config table. This is cached for 1 minute internally.

db-session

Manages sessions. Default is 1 hour expiry. Used mainly by the middleware in serf

db-user

Manages users. Used mainly by the middleware in serf

db-permissions

Manages getting the permissions for user. Used mainly by the middleware in serf