Give each external module its own schema

Context

PostgreSQL is the panel's only database, as ADR-0023 decides. Its schemas give a set of tables a namespace of its own inside one database, and a schema can be created or dropped, with everything in it, inside a transaction.

Bundled modules ship inside the binary, per ADR-0009, and are written, reviewed and upgraded together. External modules are installed, updated and removed one at a time, through Composer per ADR-0012, by authors who do not coordinate with each other or with the panel. Two external modules, or an external module and the panel's own, can choose the same table name, and removing an external module means finding everything it created.

Decision

We will give each external module a PostgreSQL schema of its own, named from its module identifier, and create its tables, and the objects that belong to them, in that schema. Bundled modules will share the default schema. An external module's schema will be created inside the transaction that first migrates the module.

Alternatives

A schema for every module, bundled modules included. Each bundled module would have a schema of its own as well. We rejected this because bundled modules ship, upgrade and are reviewed together as one binary, so separating them from each other buys nothing: a schema exists to separate what is installed separately.

Consequences

Easier:

• The names of an external module's own tables will not conflict with another module's, or with those of the bundled modules.
• Removing an external module's own tables will be a matter of dropping its schema. Anything it changed outside its schema, such as a column added to another module's table, will have to be reversed separately.
• The bundled modules' tables will keep unqualified names in the default schema.

Harder:

• Bundled modules will have to avoid conflicting table names with each other by convention, checked in review.
• Nothing in the database will record which bundled module owns which table in the default schema.
• A reference from one external module to another's table will have to name that module's schema.

Constrained:

• An external module's schema name will be derived from its module identifier, so the identifier will have to make a usable schema name: not the default schema's, not one PostgreSQL reserves, such as a name beginning pg_, and no longer than its 63-byte limit on names.