SQLite has a built in mechanism for loading extensions at runtime. The extensions themselves are just dynamic libraries. The main entry point for the extension is an init function that SQLite calls when the library is loaded. Within that init function, the extension can register a number of different kinds of functionality:

* custom functions, including aggregate functions, window functions, and scalar functions: https://sqlite.org/appfunc.html

* virtual tables: https://sqlite.org/appfunc.html (This is how stanchion and other extensions like FTS and sqlite-vss are implemented)

* table valued functions (also implemented through the virtual table mechanism)

* virtual file systems: https://www.sqlite.org/vfs.html

It's really impressive how extensible SQLite is, and it's the power of that extensibility that makes stanchion possible.