Why we shouldn't drop the SQL database tables anymore

[csirmazbendeguz]

The database consists of the following tables:

• annotations - OCI
• domains - OCI
• locators - OCI
• migrations - defined in the source code
• modules - OCI
• name_verifications - OCI
• publications - created by dirctl routing publish
• record_usage_metrics
• records - OCI
• scan_reports - OCI
• signature_verifications - OCI
• skills - OCI
• syncs - created by dirctl sync

---

Most tables come from the OCI. For example, records has the same records as the OCI registry.

syncs and publications are operations created by dirctl

migrations are defined in the source code

record_usage_metrics is automatically created / updated on each pull, lookup, or periodically by the reconciler

---

Previously, we used to drop the database tables when changing the database schema. This was okay, since most tables were coming from the OCI registry. Yes, we lost previous syncs and publications operations, but that was acceptable to make migrations easier

v1.6.1 comes with the record_usage_metrics table which makes dropping & rebuilding the database tables a much less acceptable solution to migrations. These metrics are tracked in the database only, so resetting the database would mean losing these metrics

NB: it doesn't make sense to move these metrics into the OCI registry:

1. they are not verifiable and can be easily falsified
2. they refer to the local Directory instance, so it makes no sense to sync them into other registries

---

Because of this, I believe we can't drop the database anymore. If we make a change to the database schema, we should use the existing migration system to handle the changes