capsul-flask/docs/database.md

# capsul-flask's relationship to its Database Server

Capsul has a ["hub and spoke" architecture](./architecture.md). The "Hub" runs the web application and talks to the Postgres database, while the "Spoke"s are responsible for creating/managing virtual machines. One instance of the capsul-flask application can run in both hub mode and spoke mode at the same time, however there must only be one instance of the app running in "Hub" mode at any given time.

The Postgres connections parameters are [configurable](./configuration.md).

## <a name="schema_management"></a>Database schema management (schema versions)

capsul-flask has a concept of a schema version. When the application starts, it will query the database for a table named `schemaversion` that has one row and one column (`version`). If the `version` it finds is not equal to the `desiredSchemaVersion` variable set in `db.py`, it will run  migration scripts from the `schema_migrations` folder one by one until the `schemaversion` table shows the correct version.

For example, the script named `02_up_xyz.sql` should contain code that migrates the database from schema version 1 to schema version 2. Likewise, the script `02_down_xyz.sql` should contain code that migrates from schema version 2 back to schema version 1.

**IMPORTANT: if you need to make changes to the schema, make a NEW schema version. DO NOT EDIT the existing schema versions.**

In general, for safety, schema version upgrades should not delete data. Schema version downgrades will simply throw an error and exit for now. 

## <a name="manual_queries"></a>Running manual database queries

You can manually mess around with the database like this:

```
pipenv run flask cli sql -f test.sql
```

```
pipenv run flask cli sql -c 'SELECT * FROM vms'
```

This one selects the vms table with the column name header:

```
pipenv run flask cli sql -c "SELECT string_agg(column_name::text, ', ') from information_schema.columns WHERE table_name='vms'; SELECT * from vms"
```

How to modify a payment manually, like if you get a chargeback or to fix customer payment issues:

```
$ pipenv run flask cli sql -c "SELECT id, created, email, dollars, invalidated from payments"
1, 2020-05-05T00:00:00, forest.n.johnson@gmail.com, 20.00, FALSE

$ pipenv run flask cli sql -c "UPDATE payments SET invalidated = True WHERE id = 1"
1 rows affected.

$ pipenv run flask cli sql -c "SELECT id, created, email, dollars, invalidated from payments"
1, 2020-05-05T00:00:00, forest.n.johnson@gmail.com, 20.00, TRUE
```

## how to view the logs on the database server (legion.cyberia.club)

`sudo -u postgres pg_dump capsul-flask | gzip -9 > capsul-backup-2021-02-15.gz`


## changing the email address on an account

```
UPDATE accounts SET lower_case_email = 'new@email.address' WHERE email = 'old@email.address' ;
UPDATE accounts SET email = 'new@email.address' WHERE email = 'old@email.address' ;
UPDATE login_tokens SET email = 'new@email.address' WHERE email = 'old@email.address' ;
UPDATE operations SET email = 'new@email.address' WHERE email = 'old@email.address' ;
UPDATE payment_sessions SET email = 'new@email.address' WHERE email = 'old@email.address' ;
UPDATE payments SET email = 'new@email.address' WHERE email = 'old@email.address' ;
UPDATE ssh_public_keys SET email = 'new@email.address' WHERE email = 'old@email.address' ;
UPDATE unresolved_btcpay_invoices SET email = 'new@email.address' WHERE email = 'old@email.address' ;
UPDATE vm_ssh_authorized_key SET email = 'new@email.address' WHERE email = 'old@email.address' ;
UPDATE vm_ssh_host_key SET email = 'new@email.address' WHERE email = 'old@email.address' ;
UPDATE vms SET email = 'new@email.address' WHERE email = 'old@email.address' ;
```