## Guide to FanSifter Database Migrations

### Types of schemas

FanSifter database has 5 types of schemas:

1. `public` - default PostgreSQL schema, currently not used for storing any data.

2. `commons` - main schema to store system parameters, data about users and their workspaces, and other common data, which is not related to specific workspaces.

3. "workspace" schema (all schemas starting with `w`) - schema with workspace data.
One user can have multiple workspaces, which are separated on the schema level.

4. "company" schema (all schemas starting with `c`, also referred to as "management schema") - a schema of the individual user's main workspace, which can't be deleted.
Along with all tables of "workspace" schemas, it also includes some user-specific tables (e.g. about purchased product packages or Facebook integrations).

5. "alliance" schema (all schemas starting with `a`) - schema with alliance data.
Multiple users can collaborate on their data by merging it in alliance schema.
Selected data from their workspaces is then copied into an alliance workspace, which (as of now) has a table structure identical to "workspace" schemas.

### Applying migrations

The project uses [dbmate](https://github.com/amacneil/dbmate) migration tool which allows easily starting migrations both from CLI and the code.
We use it both for applying migrations to **existing** schemas and for **creating new** company/workspace/alliance schemas.

In order to apply migrations to the database, please use:
```bash
python manage.py db migrate -s <schema_path>
```

Parameters:

- `schema_path`: one of `[public, commons, workspace, company, alliance]` (default: `public`) - a schema type to apply migrations to.

### NB! Company and alliance migrations include workspace migrations!
As "company" or "alliance" schema includes all tables of workspace schema, it is usually the case that migrations applied to "workspace" schema should also be applied to other types of schemas.

So, when applying migrations to `company` schema, all migrations from `workspace` folder will be executed first before executing migrations from `company` schema. Same applies to `alliance` migrations (`workspace` => `alliance`).

### Writing migrations
Please check [dbmate](https://github.com/amacneil/dbmate) documentation.

The migrations are stored in `migrations/` folder under subfolders of each schema type.

To create new schema migration, please use:
```bash
python manage.py db new -m <message> -s <schema_path>
```