> what's the point of using this API?
[Layering][0].
Let us say that you are creating a web app based on SQLite. Your web app has a login system, which controls who gets to do what with the underlying data, stored in SQLite. You could:
1. Use whatever user authentication mechanism is built into your web app framework. Problem is, it probably won't know anything about SQLite, so it can't say, "This record is Bob's, and this record over here is Alice's, but Alice is an Admin, so she can modify Bob's records."
2. Write your own user authentication framework.
3. Start with this one, which embeds concepts of who can perform [CRUD operations][1] on the data down into the data layer, allowing the application code to largely ignore such things.
I say "start with" because the extension as-given probably doesn't do everything you need it to, but it's often much easier to start with code that does most of what you want and then modify it to suit than it is to start from scratch.
(Only "often" because that's dependent on how easy it is to understand and modify the existing code.)
Regardless of your choice, it is the access layer at the HTTP front end here that provides the enforcing power behind your authentication layer. If you took path #1 or #2 above but let anyone SSH into the server and modify the DB directly, they'd be just as bypassed as if you used this SQLite extension.
[0]: https://en.wikipedia.org/wiki/Multitier_architecture
[1]: https://en.wikipedia.org/wiki/Create,_read,_update_and_delete