I am having difficulty grasping this API.

In my first attempts, I expected to retrieve the fully qualified name of a database opened using sqlite3_open by passing the handle ppDb. I always end up with an empty string, in line with

The sqlite3_db_filename(D,N) interface returns a pointer to the filename associated with database N of connection D. If there is no attached database N on the database connection D, or if database N is a temporary or in-memory database, then this function will return either a NULL pointer or an empty string.

The vital part is If there is no attached database N on the database connection D; when that is the case (no attached database), given

int sqlite3_open(
  const char *filename,   /* Database filename (UTF-8) */
  sqlite3 **ppDb          /* OUT: SQLite db handle */
);

is there a way of retrieving a fully qualified filename given ppDb?

I am having difficulty grasping this API.

Without seeing any code or schema definitions, you're making us guess. Mine is that you're overlooking the fact that multiple DBs can be attached to a given SQLite conn, so you need to pass the correct schema name to this API to get the expected file name back.

If you're looking for the default schema's DB file name, pass a C NULL pointer, not a schema name:

#include <sqlite3.h>
#include <stdio.h>

int main()
{
    sqlite3* db;
    sqlite3_open("foo.db", &db);
    printf("DB absolute file name: %s\n", sqlite3_db_filename(db, NULL));
    return 0;
}

If you had multiple DBs attached, you'd pass the "AS" value, not NULL.

The latter.