Currently the database module just offers direct access to a set of tables.

Modules should offer an API instead.

Currently code everywhere has to check structure constantly:

local db = MODULES.Database:Personal()

    if not db.bidding then
        db.bidding = { autoOpen = true }
    end
    self.db = db.bidding

Instead we should offer a more abstract API.

Modules care about setting and retrieving data from the database; proposed API:

    local database = {
        setBoolean = function(name, bool) .. end,
        getBoolean = function(name, default) .. end,
        setString = function(name, string) .. end,
        getString = function(name, default) .. end,
        ...
    }

In most cases modules should avoid nested config tables, but just use namespacing in the name. (LUA handles strings efficiently so it's fine, internally a module could / should use CONSTANTS)

The next challenge is to inject module specific database via name prefixing. This way each module has its own separate storage area and will never interfere with another module. This also enforces modules to never read / write directly to another modules' configuration.

Not sure we want this, this is mostly theoretical / purist rambling

I've created a basic module loader similar to AMD modules in JS.
Function signature would be somewhat like this:

define('myModule', {'dep1', 'dep2'}, function(dep1, dep2) 
  print("Loading myModule")
  return {}
end)

The cool things that this can unlock:

• We don't care about file inclusion order, the loader fixes that automatically.
• We can support asynchronous loading, for example if one module wants to wait until some ingame events have fired all modules that depend on it will automatically delay their loading as well.
• We can extend this to use localized dependencies.

Localized dependencies

Currently when loading modules we use a service locator pattern: CLM.XX.YY.ZZ, this has some known drawbacks.
Alternatively we can use dependency injection using the loader above.
The next step would be to have personalized dependencies, which means that for some modules each dependent module gets a different version.

This would allow us to pass in a different database object (that exposes the data only via an API) to each module.

So I like the general idea of API, but i dont like the idea of allowing access to specific fields. What if we would provide an API to access a whole namespace? Namespace could be private or shared. If private then any access after first would result in an error?
e.g.
MODULES.Database:Private("bid")?
MODULES.Database:Shared("config")?

And some special like MODULES.Database:Ledger()?

And then this namespace would be a big array that a module can populate any way it wants

We could split read and write?
The idea is to only provide each module with exactly what it needs and not more.
To me reading another modules' configuration is an indicator of tight coupling.

Not sure what you mean with private / shared here?