# 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`