Currently, we have a 10-year-old database made for Asterisk realtime over SQLite with 4 years of glue added on top to avoid breaking the old web interface. We don’t have Asterisk realtime anymore, nor SQLite nor the web interface, so we can refactor all this cruft away.

List of SQLAlchemy schemas

How to refactor DB

• refactor only to remove glue/fixes

Easy tasks

• Remove unused columns

• Rename columns to reflect real use cases (ex: rightcall lol)

• Add nullable, unique and [consistency? integrity?](forget the world for coherence)

• Move management schemas to the appropriate service (asterisk, agentd, call-logd, stat)

• Add UUID primary keys (but we cannot easily remove IDs because of funckeys)

• Change UUID columns to the UUID type in PG

• Merge manage-db and dao

Hard/long tasks

• Add foreign keys

  • context has ~ 30 related

• Use relational tables instead of type/typeval. How to do polymorphism in DB for dialaction? Mehdi has an answer with SQLAlchemy. Can we avoid polymorphism altogether?

• A lot of dust to anticipate

• Be careful to not do too much refactoring to avoid losing time

• Make all resources tenant aware

Services that access the asterisk database (from wazo-confd) directly

• wazo-agentd

• wazo-agid

• wazo-confgend

• xivo-config

• xivo-stat

• wazo-auth → ldap_user

• wazo-purge-db

• wazo-call-logd

• wazo-nestbox-plugin

Services must be able to manage their own database (cloud native requirement)

Tests

• Alembic has tools to compare schemas / data before/after upgrade

• Compare HTTP requests results from before and after upgrade.

Original discussion:

• https://docs.google.com/document/d/1HXrRhl9qm5zqAk-iCqfMJDHPLWHjutS0NZ2rT59t6H0