> For the complete documentation index, see [llms.txt](https://docs.lingoql.com/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://docs.lingoql.com/sub0/apis-abi/practical-examples.md). # Practical Examples Use this page like a cookbook. Each example combines patterns from earlier ABI pages into a production-style flow. Start with [Structure](/sub0/apis-abi/structure.md), [Authentication](/sub0/apis-abi/authentication.md), [Action Chaining](/sub0/apis-abi/action-chaining.md), [File Uploads](/sub0/apis-abi/file-uploads.md), and [Queueing](/sub0/apis-abi/queueing.md) if you need the building blocks first. ### Shared models {% hint style="info" %} Include `created_at`, `updated_at`, and `deleted_at` in SQL models. Sub0 assumes timestamp-friendly schemas for common write patterns. {% endhint %} #### `_users` ```json { "id": { "type": "String", "primary_key": true }, "name": { "type": "String", "fts": true, "indexable": true }, "email": { "type": "String", "max_length": 255, "fts": true, "indexable": true }, "wallet_balance": { "type": "Float" }, "password": { "type": "String", "max_length": 255 }, "age": { "type": "Number", "optional": true }, "created_at": { "type": "DateTime" }, "updated_at": { "type": "DateTime" }, "deleted_at": { "type": "DateTime", "optional": true } } ``` #### `_transactions` ```json { "id": { "type": "String", "primary_key": true }, "user_id": { "type": "String", "foreign_key": { "of": "_users", "key": "id" } }, "amount": { "type": "Float" }, "status": { "type": "String" }, "created_at": { "type": "DateTime" }, "updated_at": { "type": "DateTime" }, "deleted_at": { "type": "DateTime", "optional": true } } ``` #### `_gallery` ```json { "id": { "type": "String", "primary_key": true }, "user_id": { "type": "String", "foreign_key": { "of": "_users", "key": "id" } }, "storage_key": { "type": "String" }, "url": { "type": "String" }, "dominant_color": { "type": "String" }, "file_size": { "type": "String" }, "created_at": { "type": "DateTime" }, "updated_at": { "type": "DateTime" }, "deleted_at": { "type": "DateTime", "optional": true } } ``` #### `_csrf_token_store` ```json { "id": { "type": "String", "primary_key": true }, "user_id": { "type": "String", "foreign_key": { "of": "_users", "key": "id" } }, "csrf_token": { "type": "String" }, "created_at": { "type": "DateTime" }, "updated_at": { "type": "DateTime" }, "deleted_at": { "type": "DateTime", "optional": true } } ``` ### MongoDB workflows > NOTE: `mongo_query` is nothing but mongodb raw query commands. This includes `$aggregate`, `$filter,` `$sort`, `$limit`, `$set` etc. on Sub0, `$filter` is `filter_predicate`, `$aggregate` is `aggregate`, for updates, you would typically use `update_predicate` etc. `User Sign Up` ```json { "id": "gF96WzGkO9eo12vq1l0ndIDe6", // resource id. MUST BE UNIQUE FOR ALL RESOURCES/ENDPOINTS "_comment": "Endpoint to sign up a new user and return an auth token", "resource": "sign-up", // i.e "https:///user-signup". you can also do {resource: "user/sign-up"} or other URL-compatible variants "tokenize": { "custom_claim_fields": ["id", "email"], "type": "JWT", "algorithm": "HS256", "expiration": 3600, // token expires after 1 hour "encryption_key": "$ENV.JWT_SECRET_KEY", "property_name": "token" }, "actionables": [ { "id": 1, "mode": "QUERY", "payload_validation": [ { "field": { "name": { "type": "STRING" } }, "min_length": 10, "max_length": 255 }, { "field": { "email": { "type": "EMAIL" } }, "min_length": 50, "max_length": 255 }, { "field": { "password": { "type": "STRING" } }, "min_length": 50, "max_length": 255 }, { "field": { "hobbies": { "type": "STRINGARRAY", "arr_length": 2, "items_min_length": 5, "items_max_length": 10 } } } ], "hashables": [ { "property": "password", // hashes the password before storing in the db "algorithm": "BCRYPT", "options": { "rounds_cost": 12, "salt": "$ENV.MY_HASHING_SALT" } } ], "mongo_query": { "collection": "users", "action": "INSERT", "parameters": ["name", "email", "password"], // if you omit this, the full payload gets inserted/stored "unique": ["email"] }, "main_returnable": true, "returnables": ["id", "name", "email"] } ] } ``` ```json { // sample payload "name": "Samuel Johnson", "email": "test@gmail.com", "password": "test-password", "hobbies": ["swimmm", "coding"] } ``` > NOTE: Same insert operation works too for bulk inserts. Simply send in an array of objects. > **Note:** All resource IDs must be unique. When two resources use the same ID, the first one is executed and the other is ignored, which may cause unintended results. ```json { // example bulk insert "id": "gF96WzGkO9eo12vq1l0n", "_comment": "Endpoint for bulk inserting records", "resource": "bulk-insert", "tokenize": { "type": "JWT", "algorithm": "HS256", "encryption_key": "$ENV.JWT_SECRET_KEY" }, "protected": { "provide_as": "x-access-token", "extract_claims": ["id"] }, "actionables": [ { "id": 1, "mode": "QUERY", "mongo_query": { "collection": "transactions", "action": "INSERT", "parameters": ["txn_user_id", "txn_type", "txn_amount"], "with_timestamp": true // required }, "returnables": ["id", "txn_user_id", "..."] } ] } ``` ```json [ // sample payload { "txn_user_id": "6948fa461936f3cf934061f5", "txn_type": "credit", "txn_amount": 24.75 } ] ``` ```json { // sample response "success": true, "message": "success", "data": [ { "id":"694a77c4aa07a00ec8b4768c", "txn_user_id": "...", // ... } ] } ``` `User Sign In` ```json { "id": "1l0ndIDe6gF96WzGkO9eo12vq", "_comment": "Endpoint to sign in a new user and return an auth token", "resource": "sign-in", "tokenize": { "custom_claim_fields": ["id", "email"], "type": "JWT", "algorithm": "HS256", "expiration": 86400, // token expires after 24 hours "encryption_key": "$ENV.JWT_SECRET_KEY", "property_name": "token" }, "actionables": [ { "id": 1, "mode": "QUERY", "payload_validation": [ { "field": { "email": { "type": "EMAIL" } }, "min_length": 50, "max_length": 255 }, { "field": { "password": { "type": "STRING" } }, "min_length": 50, "max_length": 255 } ], "mongo_query": { "collection": "users", "action": "FETCH", "filter_predicate": { "email": "$PAYLOAD.email" } }, "verify_hashables": [ { "property": "password", "algorithm": "BCRYPT" } ], "returnables": ["id", "name", "email"] } ] } ``` ```json { // sample payload "email": "test@gmail.com", "password": "test-password" } ``` `Expected Response:` ```json { // for successful sign in "success": true, "message": "success", "data": { "id": "6948fa461936f3cf934061f5", "name": "Samuel", "email": "test@gmail.com", "token": "eyJ0eXAiOiJKV1QiLCJhbGciOiJIUzI1NiJ9.eyJpc3MiOiJMaW5nb1FMIiwiaWF0IjoxNzY2Mzk2MzIwLCJleHAiOjE3NjYzOTk5MjAsInN1YiI6IjY5NDhmYTQ2MTkzNmYzY2Y5MzQwNjFmNSIsImlkIjoiNjk0OGZhNDYxOTM2ZjNjZjkzNDA2MWY1IiwiZW1haWwiOiJ0ZXN0QGdtYWlsLmNvbSJ9.Pm_1iITxweWulK-M92BEuhRc5W0oUZRUuCrAccg9A3c" } } ``` ```json { // for failed hashable verification "success": false, "message": "password verification failed!", "data": null } ``` `Making Authenticated Calls` ```json { "id": "De6gF961l0ndo12vqIWzGkO9e", "_comment": "Endpoint to fetch users based on status and age limit", "resource": "fetch-users", "tokenize": { // minimal property needed for token verification "type": "JWT", "algorithm": "HS256", "encryption_key": "$ENV.JWT_SECRET_KEY" //"local": true, may be needed when the token type is PASETO }, "protected": { // enforces authentication on this endpoint "provide_as": "x-access-token" // i.e {headers: {"x-access-token": ""}} }, "actionables": [ { "id": 1, "mode": "QUERY", "mongo_query": { "collection": "users", "action": "FETCH", "multi": true, // return multiple matching records (i.e an array). When omitted/false, a single matching object is returned "filter_predicate": { "status": "$PAYLOAD.status", "$or": [ { "age": { "$lt": "$PAYLOAD.age" } } ] }, "max_filter_and_aggregate_result": 2 // used for limiting filter or aggregate results returned }, "returnables": ["id", "name", "status", "age"] } ] } ``` ```json { // sample payload "status": "student", "age": 20 } ``` ```json { // response when token is valid for the payload above "success": true, "message": "success", "data": [ { "name": "Samuel", "age": 14, "status": "student", "id": "6948fa461936f3cf934061f5" }, { "name": "John", "age": 18, "status": "student", "id": "694955cb88e76aa04acd6556" } ] } ``` ```json { // response when token is invalid "success": false, "message": "Token verification failed: ExpiredSignature", "data": null } ``` `aggregated pipeline` ```json { // this aggregate pipeline produces same result as the filter_predicate above. Pipelines give more control for running complex mongodb queries "id": "1l0ndIDe6gF96WzGkO9eo12vq", "_comment": "Endpoint to fetch users based on status and age limit", "resource": "fetch-users", "tokenize": { "type": "JWT", "algorithm": "HS256", "encryption_key": "$ENV.JWT_SECRET_KEY" }, "protected": { "provide_as": "x-access-token" }, "actionables": [ { "id": 1, "mode": "QUERY", "mongo_query": { "collection": "users", "action": "FETCH", "multi": true, // this can be omitted when using aggregate "max_filter_and_aggregate_result": 10, // this can be omitted in favor of `"$limit": 10` below "aggregate": [ { "$match": { "$expr": { "$and": [ { "$eq": ["$status", "$$PAYLOAD.status"] }, { "$or": [ { "$lt": ["$age", "$$PAYLOAD.age"] } ] } ] } } }, { "$sort": { "age": -1 } }, { "$limit": 10 }, { "$project": { "_id": 1, // Sub0 auto maps all "_id" to "id" when returning responses "name": 1, "status": 1, "age": 1 } } ] }, "returnables": ["id", "name", "status", "age"] } ] } ``` ```json { // sample payload "status": "student", "age": 20 } ``` `Updating a record` ```json { "id": "1l0ndIDe6gF96WzGkO9eo12vq", "_comment": "Endpoint to update user info", "resource": "update-user", "tokenize": { "type": "JWT", "algorithm": "HS256", "encryption_key": "$ENV.JWT_SECRET_KEY" }, "protected": { "provide_as": "x-access-token", "extract_claims": ["id"] // extract id for use in query below }, "actionables": [ { "id": 1, "mode": "QUERY", "mongo_query": { "collection": "users", "action": "UPDATE", "filter_predicate": { "_id": "$PROTECTED.id" // id of the caller (from their token) }, "parameters": ["name", "status"], // properties of the user to update. You can also use "update_predicate" for fine-grained control "with_timestamp": true // applies updated_at at the time of update } } ] } ``` ```json { // sample payload "name": "Rustacean", "status": "staff" // change status from "student" to "staff" } ``` ```json { // typical update response "success": true, "message": "success", "data": null } ``` > NOTE: to apply same update to multiple records, simply set `multi:true` and use the `filter_predicate` property to filter the records to apply the bulk update to `Deleting a record` There are two types of deletes: **soft\_delete: true** (soft delete) and **soft\_delete: false** (hard delete). When **true**, the record is not entirely removed from the database, rather a deleted\_at property is added and used to prevent further reads of that record. For hard deletes, the record is removed entirely from the database. This works out of the box for **MongoDB**, even when your model doesn’t include a **deleted\_at** column/property. For **SQL** based databases, you must include **deleted\_at** in your database schema/model for soft delete to work; otherwise, hard delete is assumed. ```json { "id": "gF96WzGkO91l0ndIDe", "_comment": "Endpoint to delete user info", "resource": "delete-user", "tokenize": { "type": "JWT", "algorithm": "HS256", "encryption_key": "$ENV.JWT_SECRET_KEY" }, "protected": { "provide_as": "x-access-token", "extract_claims": ["id"] }, "actionables": [ { "id": 1, "mode": "QUERY", "mongo_query": { "collection": "users", "action": "DELETE", "filter_predicate": { "_id": "$PROTECTED.id" // or $PAYLOAD.id }, "soft_delete": true // appends deleted_at to the record. if false, the record is removed entirely from the db } } ] } ``` ```json { // sample payload } ``` ```json { // typical delete response "success": true, "message": "success", "data": null } ``` > NOTE: to apply same delete operation to multiple records, simply set `multi:true` and use the `filter_predicate` property to filter the records to apply the bulk delete to `bulk delete` ```json { "id": "gF96WzGkO91l0ndIDe", "_comment": "Endpoint to bulk delete users", "resource": "bulk-delete-users", "tokenize": { "type": "JWT", "algorithm": "HS256", "encryption_key": "$ENV.JWT_SECRET_KEY" }, "protected": { "provide_as": "x-access-token", "extract_claims": ["id"] }, "actionables": [ { "id": 1, "mode": "QUERY", "mongo_query": { "collection": "users", "action": "DELETE", "filter_predicate": { "_id": { "$in": "$PAYLOAD.ids" } }, "multi": true, // required "soft_delete": true } } ] } ``` ```json { // sample payload "ids": ["694a77c4aa07a00ec8b4768c","694a77c4aa07a00ec8b4768b"] } ``` so far, we’ve been looking at single action per API call. Let’s now look into multiple actions per API call. `action chaining within same resource` ```json { "id": "gF96WzGkO91l0ndIDe", "_comment": "Endpoint to update the wallet balance of a user from a transaction and return the new balance", "resource": "update-wallet-balance", "tokenize": { "type": "JWT", "algorithm": "HS256", "encryption_key": "$ENV.JWT_SECRET_KEY" }, "protected": { "provide_as": "x-access-token", "extract_claims": ["id"] }, "actionables": [ { "id": 1, "mode": "QUERY", "mongo_query": { "collection": "users", "action": "UPDATE", "filter_predicate": { "_id": "$PROTECTED.id" }, "update_predicate": { "$inc": { "wallet_balance": "$PAYLOAD.wallet_balance" // this payload comes from the extracted values from the depends_on. No request payload was sent to this endpoint } }, "with_timestamp": true }, "depends_on": [ { "resource_id": "self", // or this resource's id (also works) "action_ids": [2], "extract_response_for": [ { "extract": "amount", "for": "wallet_balance" } ] } ], "returnables": ["wallet_balance"], // returns the amount that was added to the user's wallet balance, not the total. you might need another actionable to get the real updated wallet balance "main_returnable": true // since there are more than one actionables, we want this action to be the main result-returning action }, { "id": 2, "mode": "QUERY", "no_op": true, // this means this actionable will be executed only when its referenced within a "depends_on" construct. Without it, our API request will call this particular actionable twice: first from the actionable of id 1 above, and second the actionable of id 2 (we're currently on) since all actionables are executed sequentially or in parallel. "mongo_query": { "collection": "transactions", "action": "FETCH", "filter_predicate": { "user_id": "$PROTECTED.id", "status": "FULFILLED" } }, "returnables": ["amount"] } ] } ``` ```json { // sample payload } ``` ```json { // sample response "success": true, "message": "success", "data": { "wallet_balance": 24.75 // this is the amount that was added to the user's wallet balance. not the current wallet balance. } } ``` `action chaining within another resource` ```json // resource 1 { "id": "O91l0ndIDegF96WzGk", "_comment": "Endpoint to fetch users transaction amount", "resource": "fetch-txn-amount", "tokenize": { "type": "JWT", "algorithm": "HS256", "encryption_key": "$ENV.JWT_SECRET_KEY" }, "protected": { "provide_as": "x-access-token", "extract_claims": ["id"] }, "actionables": [ { "id": 1, "mode": "QUERY", "no_op": true, // can be omitted here, since it doesn't lead to duplicate calls "mongo_query": { "collection": "transactions", "action": "FETCH", "filter_predicate": { "user_id": "$PROTECTED.id", "status": "FULFILLED" } }, "returnables": ["amount"] } ] } ``` ```json // resource 2 { "id": "gF96WzGkO91l0ndIDe", "_comment": "Endpoint to update the wallet balance of a user from a transaction and return the new balance", "resource": "update-wallet-balance", "tokenize": { "type": "JWT", "algorithm": "HS256", "encryption_key": "$ENV.JWT_SECRET_KEY" }, "protected": { "provide_as": "x-access-token", "extract_claims": ["id"] }, "actionables": [ { "id": 1, "mode": "QUERY", "mongo_query": { "collection": "users", "action": "UPDATE", "filter_predicate": { "_id": "$PROTECTED.id" }, "update_predicate": { "$inc": { "wallet_balance": "$PAYLOAD.wallet_balance" } }, "with_timestamp": true }, "depends_on": [ { "resource_id": "O91l0ndIDegF96WzGk", // calls actionable in another resource with id "O91l0ndIDegF96WzGk" above "action_ids": [1], // the id(s) of the actionable(s) to call in the specified resource "extract_response_for": [ { "extract": "amount", "for": "wallet_balance" } ] } ], "returnables": ["wallet_balance"] } ] } ``` ```json { // sample payload } ``` ### Postgres workflows Sub0 excels in the SQL ecosystem. By default, it eliminates the risk of SQL injection by enforcing parameterized queries (`$1` for PostgreSQL and `?` for MySQL/MariaDB). > NOTE: Similar operation works for **mysql/mariadb**. The major difference is that you would use `$1` for **postgres** and `?` for **mysql/mariadb** for parametrization of queries; also, some minor changes applies to mysql/mariadb. > NOTE: if you encounter an error like “error occurred while decoding column created\_at: error in Any driver mapping: Any driver does not support the Postgres type PgTypeInfo(Timestamptz)” you’d have to apply a type casting on the column in your query. e.g `created_at::text` or the correct casting depending on the database you’re making use of `Sign up user` ```json { "id": "9eo1gFo12vq1l0ndIDe6", "_comment": "Endpoint to sign up a new user and return an auth token", "resource": "sign-up", "tokenize": { "custom_claim_fields": ["id", "email"], "type": "JWT", "algorithm": "HS256", "expiration": 3600, // token expires after 1 hour "encryption_key": "$ENV.JWT_SECRET_KEY", "property_name": "token" }, "actionables": [ { "id": 1, "mode": "QUERY", "payload_validation": [ { "field": { "name": { "type": "STRING" } }, "min_length": 10, "max_length": 255 }, { "field": { "email": { "type": "EMAIL" } }, "min_length": 50, "max_length": 255 }, { "field": { "password": { "type": "STRING" } }, "min_length": 50, "max_length": 255 } ], "hashables": [ { "property": "password", "algorithm": "BCRYPT", "options": { "rounds_cost": 12, "salt": "$ENV.MY_HASHING_SALT" } } ], "sql_query": { "query": "INSERT INTO users (id, name, email, password, wallet_balance, created_at, updated_at) VALUES ($1, $2, $3, $4, $5::float8, $6::timestamptz, $7::timestamptz) RETURNING id, name, email", "parameters": [ "$GENERATOR.KSUID", // or "id", if using the generators property below. That way, the generator can safely replace it with the generated value "name", "email", "password", "0", // default wallet balance "$DATETIME", // i.e dates of the form "2018-01-26T18:30:09.453+00:00" "$DATETIME" ], "with_timestamp": true, // (Optional) when present, it auto inserts the values of "created_at" and "updated_at". When its false or not present, you must apply the method used in the query and parameters above! // "multi": true, // (Option) set multi to true for bulk inserts/update/delete when using sql_query. This expects you to send an array of items to be inserted. "unique": ["email"] }, "generators": [ // optional since we've already used "$GENERATOR.KSUID" above. { "generate": "KSUID", // generates a KSUID string for id. Possible values are "CUID", "CUID2", "KSUID", "NANOID", "SHORTID", "SLUGID", "SHORTUUID", "ULID", "UNIQID", "UUID", "BSON" "for": "id" // property to generate a generator for } ], "returnables": ["id", "name", "email", "token"] } ] } ``` ```json { // sample payload "name": "Samuel Johnson", "email": "test@gmail.com", "password": "test-password" } ``` ```json { // sample response. Note that data could be an array, for multiple insert "success": true, "message": "success", "data": { "name": "Samuel", "id": "37IWbgjDC45gzTza3HVTbja4SPK", "email": "test@gmail.com", "token": "eyJ0eXAiOiJKV1QiLCJhbGciOiJIUzI1NiJ9.eyJpc3MiOiJMaW5nb1FMIiwiaWF0IjoxNzY2NTkxMTM4LCJleHAiOjE3NjcxOTU5Mzh9.NdMRHxUci2ztv8hqgvKGqhvaKMgPCKA1PpATPV4DMAg" } } ``` `sign in` ```json { "id": "ndIDe6gF1l096", "_comment": "Endpoint to sign in a new user and return an auth token", "resource": "sign-in", "tokenize": { "custom_claim_fields": ["id", "email"], "type": "JWT", "algorithm": "HS256", "expiration": 86400, // token valid for 24 hours "encryption_key": "$ENV.JWT_SECRET_KEY", "property_name": "token" }, "actionables": [ { "id": 1, "mode": "QUERY", "payload_validation": [ { "field": { "email": { "type": "EMAIL" } }, "min_length": 50, "max_length": 255 }, { "field": { "password": { "type": "STRING" } }, "min_length": 50, "max_length": 255 } ], "sql_query": { "query": "SELECT id, name, email, password FROM users WHERE email = $1", "parameters": ["email"] }, "verify_hashables": [ { "property": "password", "algorithm": "BCRYPT" } ], "returnables": ["id", "name", "email", "token"] } ] } ``` ```json { // sample payload "email": "test@gmail.com", "password": "test-password" } ``` `Making Authenticated Calls` ```json { "id": "12vqIWzGkODe6gF", "_comment": "Endpoint to fetch users matching a certain name criteria", "resource": "fetch-users", "tokenize": { "type": "JWT", "algorithm": "HS256", "encryption_key": "$ENV.JWT_SECRET_KEY" }, "protected": { "provide_as": "x-access-token" }, "actionables": [ { "id": 1, "mode": "QUERY", "sql_query": { "query": "WHERE name ILIKE CONCAT('%', $1, '%')", "parameters": ["search"] }, "returnables": ["id", "name"] } ] } ``` ```json { // sample payload "search": "uel" } ``` ```json { // sample response. // A single object is returned when only 1 record is found. // But when more than 1 records are found, an array is returned! "success": true, "message": "success", "data": { "name": "Samuel", "id": "37IXguREZ5YYadKE3Vpz1bcwIYC" } } ``` `update record` ```json { "id": "gF96W1zGkO9e", "_comment": "Endpoint to update user info", "resource": "update-user", "tokenize": { "type": "JWT", "algorithm": "HS256", "encryption_key": "$ENV.JWT_SECRET_KEY" }, "protected": { "provide_as": "x-access-token", "extract_claims": ["id"] }, "actionables": [ { "id": 1, "mode": "QUERY", "sql_query": { "query": "UPDATE users SET name = $1 WHERE id = $2", "parameters": ["name", "$PROTECTED.id"], "with_timestamp": true // updates the value of "updated_at" at the time of update } } ] } ``` ```json { // sample payload "name": "Maya Angelou" } ``` ```json { // typical response "success": true, "message": "success", "data": null } ``` `delete record` ```json { "id": "gF96WzGkO91l0ndIDe", "_comment": "Endpoint to delete user info", "resource": "delete-user", "tokenize": { "type": "JWT", "algorithm": "HS256", "encryption_key": "$ENV.JWT_SECRET_KEY" }, "protected": { "provide_as": "x-access-token", "extract_claims": ["id"] }, "actionables": [ { "id": 1, "mode": "QUERY", "sql_query": { "query": "DELETE FROM users WHERE id = $1", "parameters": ["$PROTECTED.id"], "soft_delete": true // works only when your table has a "deleted_at" column. Also, soft delete doesn't yet work with complex queries involving a delete operation! } } ] } ``` ```json { // sample payload } ``` ```json { // typical response "success": true, "message": "success", "data": null } ``` > NOTE: when you apply `soft_delete` while using SQL-database, Sub0 attempts to filter out soft deleted records from the results, when you run a `SELECT` statement. For complex `SELECT` statements that includes jsonb\_agg or nested queries, you must add an additional clause to filter records that have not yet been soft deleted. e.g `WHERE deleted_at IS NULL` This way, you’re sure your fetch/filter query doesn’t accidentally fetch already soft deleted records! > > Also, you can as well implement your own `soft_delete` logic. simply use an `UPDATE SET` query and set the `deleted_at = CURRENT_TIMESTAMP` > > For more info on what happens when you apply soft delete, please refer to the [Extras page](https://docs.lingoql.com/sub0/extras) `action chaining` ```json { "id": "gF96WzGkO91l0ndIDe", "_comment": "Endpoint to update the wallet balance of a user from a transaction and return the new user's wallet balance", "resource": "update-wallet-balance", "tokenize": { "type": "JWT", "algorithm": "HS256", "encryption_key": "$ENV.JWT_SECRET_KEY" }, "protected": { "provide_as": "x-access-token", "extract_claims": ["id"] }, "actionables": [ { "id": 1, "mode": "QUERY", "sql_query": { "query": "UPDATE users SET wallet_balance = wallet_balance + $1::float8 WHERE id = $2 RETURNING wallet_balance AS user_wallet_balance", "parameters": ["add_bal", "$PROTECTED.id"], "with_timestamp": true // auto updates the value of "updated_at" }, "depends_on": [ { "resource_id": "self", // or this resource's id (also works) "action_ids": [2], "extract_response_for": [ { "extract": "amount", "for": "add_bal" } ] } ], "returnables": ["user_wallet_balance"], "main_returnable": true }, { "id": 2, "mode": "QUERY", "no_op": true, "sql_query": { "query": "SELECT amount FROM transactions WHERE status='FULFILLED' AND user_id = $1", "parameters": ["$PROTECTED.id"] }, "returnables": ["amount"] } ] } ``` ```json { // sample payload } ``` ```json { // sample response "success": true, "message": "success", "data": { "user_wallet_balance": 73.5 // user's current wallet balance after increment. } } ``` ### External API patterns `fetch request` ```json { "id": "kO91lgF96WzGkO", "_comment": "fetch records from an external API", "resource": "make-api-call", "actionables": [ { "id": 1, "mode": "HTTPREQUEST", "http": { "url": "https://jsonplaceholder.typicode.com/posts/$PAYLOAD.page_number", // inject page number from request payload. Another test url `https://jsonplaceholder.typicode.com/posts/1/comments` "method": "GET", // http method. "POST", "GET", "PUT" etc. "headers": { "Content-Type": "application/json" }, "is_form": false // (Optional) if true, it sends the payload as a form (i.e for headers with application/x-www-form-urlencoded), else json. }, "returnables": ["id", "title", "body"] // if omitted, the actual response body is returned when no "returnables" is specified. } ] } ``` ```json { // sample payload. i.e fetch page 1 "page_number": 1 } ``` ```json { // response gotten from calling https://jsonplaceholder.typicode.com/posts/1 "success": true, "message": "success", "data": { "id": 1, "title": "sunt aut facere repellat provident occaecati excepturi optio reprehenderit", "body": "quia et suscipit\nsuscipit recusandae consequuntur expedita et cum\nreprehenderit molestiae ut ut quas totam\nnostrum rerum est autem sunt rem eveniet architecto" } } ``` `making post request` ```json { "id": "gF96WzGkO91l0ndIDe", "_comment": "post request to an external API", "resource": "make-api-post-call", "actionables": [ { "id": 1, "mode": "HTTPREQUEST", "http": { "url": "https://lingoql.free.beeceptor.com/api/user?type=$GENERATOR.KSUID", // we generate a random id and send as a query param "method": "POST", "headers": { "Content-Type": "application/json" }, "request_body": { "email": "$PAYLOAD.email" } }, "returnables": ["id", "email", "type"] } ] } ``` > NOTE: The contents of the `request_body` are what the API you're calling requests for. They're not static or Sub0 specific. It totally depends on what the endpoint is expecting! You can also send `"request_body": []` i.e an array too! ```json { // sample payload "email": "sam@lingoql.com" } ``` ```json { // sample response. typically returns exactly what we sent, plus a randomly generated id. "success": true, "message": "success", "data": { "id": "b9b59ef8-7947-4184-a7d7-18e4d629fe5f", "email": "sam@lingoql.com", // the email we sent. "type": "37Lj4XzkGlLByQCZuxZb3OaDcsO" // the query param we sent } } ``` `sending array payload` ```json { "id": "gF96WzGkO91l0ndIDe", "_comment": "post request to an external API", "resource": "make-api-post-call", "actionables": [ { "id": 1, "mode": "HTTPREQUEST", "http": { "url": "https://lingoql.free.beeceptor.com/api/user?type=$GENERATOR.KSUID", "method": "POST", "headers": { "Content-Type": "application/json" }, "request_body": "self_" // "self_" here means: "send the full request payload as-is, to the target API endpoint" }, "returnables": ["id", "payload", "type"] } ] } ``` ```json // payload sent ["sam@lingoql.com","jake@lingoql.com"] ``` ```json { // response gotten "success": true, "message": "success", "data": { "id": "4b58abbc-6d7d-4e0e-ba81-b77ddcc4b06a", "payload": [ // returns back the payload we sent. "sam@lingoql.com", "jake@lingoql.com" ], "type": "37LnqrebpqKGnb2QxaWg6gqpVOh" // returns back the query param we generated } } ``` > **Note:** By default, `request_body` expects an object whose fields map to the API’s expected payload structure. However, when you need to send **an array or the full incoming request payload** as-is, set `"request_body": "self_"`. > > This tells Sub0 to **forward the entire request payload directly** to the target API instead of mapping individual fields. > > The same rule applies during **sequential execution of actionables** — e.g., `[ { ACTION_A }, { ACTION_B } ]`. After `ACTION_A` runs, if `ACTION_B` needs to use the **full response from ACTION\_A** as its outgoing request body, simply set `"request_body": "self_"`. `sending email to recipients` ```json { "id": "gF96WzGkO91l0ndIDe", "_comment": "send welcome email to customer", "resource": "send-welcome-email", "actionables": [ { "id": 1, "mode": "HTTPREQUEST", "http": { "url": "https://api.lambahq.com/v1/action/$ENV.LAMBA_CUSTOMER_ID", "method": "POST", "headers": { "Content-Type": "application/json", "Authorization": "Basic $ENV.LAMBA_API_KEY" }, "request_body": { "service": "low_mail", "low_mail_input": { "integration": "any", "subject": "$PAYLOAD.subject", "message": "$PAYLOAD.message", "recipients": "$PAYLOAD.recipients" } } } // "run_in_background": true, emails are best sent in the background } ] } ``` ```json { // payload sent "subject": "Welcome to Sub0", "message": "Hello World!", "recipients": [ { "name": "Sam", "email": "sam@lingoql.com" } ] } ``` ```json { // response "success": true, "message": "success", "data": { // response returned by the email publisher "success": true, "message": "success", "data": { "id": "694dcd008bc230f69281a96e", "status": "processing" } } } ``` ![Email Received](https://cdn.hashnode.com/res/hashnode/image/upload/v1766706684090/c0164a07-5398-470e-9c84-a7252d970e50.jpeg) `sending email with a template` ```json { "id": "gF96WzGkO91l0ndIDe", "_comment": "send welcome email to customer", "resource": "send-email-with-template", "actionables": [ { "id": 1, "mode": "HTTPREQUEST", "http":{ "url": "https://api.lambahq.com/v1/action/$ENV.LAMBA_CUSTOMER_ID", "method": "POST", "headers": { "Content-Type": "application/json", "Authorization": "Basic $ENV.LAMBA_API_KEY" }, "request_body": { "service": "low_mail", "low_mail_input": { "integration": "any", "subject": "$PAYLOAD.subject", "message": "Hi %name%,
Welcome to Sub0!
You live in %city%", // You can use the email template directly "recipients": "$PAYLOAD.recipients" } }, "replacers": { // use replacers to customize template to a particular user "low_mail_input.message": [ // path to the body of the email to replace items in {"%name%": "recipients.0.name"}, // means replace %name% in the email template (i.e the message) with the recipients actual name from the request payload {"%city%": "recipients.0.address.city"} ] } } } ] } ``` ```json { // sample payload "subject": "Welcome to Sub0", "recipients": [ { "name": "Sam", "email": "sam@lingoql.com", "address": { "city": "Dubai" } } ] } ``` Notice the HTML template was applied. Look at the image below and observe the words in bold: ![](https://cdn.hashnode.com/res/hashnode/image/upload/v1766711150206/391b830c-b144-49c5-b7b4-442dc1f27622.jpeg) > NOTE: observe that `"low_mail_input.message"` is the path to the email body within `request_body` property. This can be nested up to multi-levels. If the message property is directly in the `request_body`, we would have simply used `“message“` instead of `low_mail_input.message` `sending email by fetching template online` ```xml Welcome Email

Welcome to Our Service!

Hi %name%,

We are thrilled to have you with us. Your registered city is %city%.

You can use the button below to get started.

Get Started

© 2025 Your Company. All rights reserved.

Unsubscribe
``` ```json { "id": "gF96WzGkO91l0ndIDe", "_comment": "send welcome email to customer", "resource": "send-email-with-online-template", "actionables": [ { "id": 1, "mode": "HTTPREQUEST", "no_op": true, // only executed below, through the depends_on reference "http":{ "url": "https://storage.lingoql.com/lingoql-test-email-template.html", // same html template above, hosted online "method": "GET", "headers": { "Content-Type": "application/json" }, "read_file_contents": true // means this action should read the contents of the .html file and return it. } }, { "id": 2, "mode": "HTTPREQUEST", "http":{ "url": "https://api.lambahq.com/v1/action/$ENV.LAMBA_CUSTOMER_ID", "method": "POST", "headers": { "Content-Type": "application/json", "Authorization": "Basic $ENV.LAMBA_API_KEY" }, "request_body": { "service": "low_mail", "low_mail_input": { "integration": "any", "subject": "$PAYLOAD.subject", "message": "", // message is empty at the start. will be populated through the depends_on below, before the replacers are executed. "recipients": "$PAYLOAD.recipients" } }, "replacers": { "low_mail_input.message": [ // replaces placeholder tokens in the template, with actual values. {"%name%": "recipients.0.name"}, {"%city%": "recipients.0.address.city"}, {"%unsubscribe_link%": "https://my-unsubscribe-link.com"} ] } }, "depends_on": [ { "resource_id": "self", // or this resource's id (also works) "action_ids": [1], "extract_response_for": [ { "extract": "self_", // "self_" here means the full response from the dependent action. This is different from "self" used above. "for": "low_mail_input.message" // means we should replace the "message" value inside "request_body.low_mail_input", with the template gotten from the dependent response. } ] } ] } ] } ``` ```json { // sample payload "subject": "Welcome to Sub0", "recipients": [ { "name": "Sam", "email": "sam.maker@gmail.com", "address": { "city": "Dubai" } } ] } ``` ![](https://cdn.hashnode.com/res/hashnode/image/upload/v1766745792458/f569fd56-4796-4c79-91a6-3a58fd8ca39c.jpeg) ### Upload patterns `uploading files` ```json { "id": "gF96W1zGkO9e", "_comment": "This endpoint uploads files", "resource": "upload", // /upload "tokenize": { "type": "JWT", "algorithm": "HS256", "encryption_key": "$ENV.JWT_SECRET_KEY" }, "protected": { "provide_as": "x-access-token", "extract_claims": ["id"] }, "actionables": [ { "id": 1, "mode": "UPLOAD", "uploads": { "max_file_uploads": 1, // limit the number of files to upload at once "expiration": 3600, // (Optional) The date and time at which the object is no longer cacheable. For more information, see http://www.w3.org/Protocols/rfc2616/rfc2616-sec14.html#sec14.21. "max_upload_file_size": 20971520, // (20mb) i.e max. upload file size in bytes. "allowed_mimetypes": ["image/png", "video/mp4"], // optional. helps you limit the type of files that can be uploaded to your storage bucket. Supports all types by default; i.e if no allowed_mimetypes is empty or property not set. we recommend against supporting all mimetypes. only whitelist the file types your application needs! files that are not supported are ignored during upload! "upload_folder": "/gallery/$PROTECTED.id" // (Optional) what folder to store this file in }, "returnables": ["storage_key", "url", "dominant_color", "file_size"] } ] } ``` ```json // sample request payload multipart/form-data file=@banner.jpg ``` ```json { // successful upload response "success": true, "message": "success", "data": [ { "storage_key": "uploads/29292/gallery/37IXguREZ5YYadKE3Vpz1bcwIYC/LingoQL App Banner new_1766775720244.jpg", "url": "https://storage.lingoql.com/uploads/29292/gallery/37IXguREZ5YYadKE3Vpz1bcwIYC/LingoQL App Banner new_1766775720244.jpg", "dominant_color": "#D8B5E6", "file_size": 991931 // in bytes (i.e about 968kb) // "file_type": "image", // "extension": "jpg", // "original_file_name": "LingoQL App Banner new.jpg", // "new_file_name": "LingoQL App Banner new_1766775720244.jpg", } ] } ``` ```json // sample request payload for multiple uploads multipart/form-data file=@banner.jpg file=@avatar.jpeg ``` ```json { // typical response for multiple file uploads "success": true, "message": "success", "data": [ { "storage_key": "uploads/29292/gallery/37IXguREZ5YYadKE3Vpz1bcwIYC/LingoQL_App_Banner_new_1766788192220.jpg", "url": "https://storage.lingoql.com/uploads/29292/gallery/37IXguREZ5YYadKE3Vpz1bcwIYC/LingoQL_App_Banner_new_1766788192220.jpg", "dominant_color": "#D8B5E6", "file_size": 991931 }, { "storage_key": "uploads/29292/gallery/37IXguREZ5YYadKE3Vpz1bcwIYC/52568057_1766788194111.jpeg", "url": "https://storage.lingoql.com/uploads/29292/gallery/37IXguREZ5YYadKE3Vpz1bcwIYC/52568057_1766788194111.jpeg", "dominant_color": "#636365", "file_size": 20972 } ] } ``` `deleting uploaded files` ```json { // content type here is application/json, not multipart/form-data "id": "gF96W1zGkO9e", "_comment": "This endpoint uploads files", "resource": "delete-files", // /delete-files "tokenize": { "type": "JWT", "algorithm": "HS256", "encryption_key": "$ENV.JWT_SECRET_KEY" }, "protected": { "provide_as": "x-access-token", "extract_claims": ["id"] }, "actionables": [ { "id": 1, "mode": "UPLOAD", "uploads": { "delete_files": true // indicates a file deletion action } } ] } ``` ```json [ // delete files payload. i.e an array of storage keys to delete from s3 "uploads/29292/gallery/37IXguREZ5YYadKE3Vpz1bcwIYC/LingoQL App Banner new_1766775720244.jpg" ] ``` ```json { // sample response "success": true, "message": "success", "data": null } ``` `uploading files and storing to gallery in db (Postgres)` Here, we leverage Sub0’s sequential execution of `actionables`. After the files (2 in this case) are uploaded, the results from that are passed down to the second `actionable` that stores it in the db. ```json { "id": "gF96W1zGkO9e", "_comment": "This endpoint uploads files", "resource": "upload-store-files", "tokenize": { "type": "JWT", "algorithm": "HS256", "encryption_key": "$ENV.JWT_SECRET_KEY" }, "protected": { "provide_as": "x-access-token", "extract_claims": ["id"] }, "actionables": [ { "id": 1, "mode": "UPLOAD", "uploads": { "max_file_uploads": 2, "max_upload_file_size": 20971520, "allowed_mimetypes": ["image/png", "video/mp4", "image/jpg", "image/jpeg"], "upload_folder": "/gallery/$PROTECTED.id" }, "returnables": ["storage_key", "url", "dominant_color", "file_size"] }, { "id": 2, "mode": "QUERY", "sql_query": { "query": "INSERT INTO gallery (id,user_id,storage_key,url,dominant_color,file_size) VALUES ($1,$2,$3,$4,$5,$6) RETURNING id, user_id, storage_key,url,dominant_color,file_size", "parameters": ["$GENERATOR.KSUID", "$PROTECTED.id", "storage_key", "url", "dominant_color", "file_size"], "with_timestamp": true, // this auto generates and inserts "created_at" and "updated_at" into our query. Very much cleaner and convenient than using $DATETIME parametrization "multi": true // enables multi insert (also works for multi update, delete etc. This means you must send an array (of same action type) as payload) }, "returnables": ["id", "user_id", "storage_key", "url", "dominant_color", "file_size"] // "main_returnable": true, (Optional) by default, the last actionable in sequential execution is the result-returning action. In cases where clarity is needed, you should include it. } ] } ``` ```json // sample request payload multipart/form-data file=@banner.jpg file=@avatar.jpeg ``` preview of the result in the db ![](https://cdn.hashnode.com/res/hashnode/image/upload/v1766835501845/469594c9-949c-4d0c-b6c1-b5581ca0c85f.png) ```json { // sample result of calling the endpoint /upload-store-files "success": true, "message": "success", "data": [ { "user_id": "37IXguREZ5YYadKE3Vpz1bcwIYC", "dominant_color": "#636365", "id": "37QVhns6PCWZBPGXT8bZIRGKXgq", "file_size": 20972, "storage_key": "uploads/29292/gallery/37IXguREZ5YYadKE3Vpz1bcwIYC/52568057_1766835401912.jpeg", "url": "https://storage.lingoql.com/uploads/29292/gallery/37IXguREZ5YYadKE3Vpz1bcwIYC/52568057_1766835401912.jpeg" }, { "file_size": 991931, "dominant_color": "#D8B5E6", "user_id": "37IXguREZ5YYadKE3Vpz1bcwIYC", "storage_key": "uploads/29292/gallery/37IXguREZ5YYadKE3Vpz1bcwIYC/LingoQL_App_Banner_new_1766835399014.jpg", "url": "https://storage.lingoql.com/uploads/29292/gallery/37IXguREZ5YYadKE3Vpz1bcwIYC/LingoQL_App_Banner_new_1766835399014.jpg", "id": "37QVhskYRzlCq1nDMHDD4v1wc5z" } ] } ``` With these stored in the db, you can easily remove a file using the storage\_key whenever you delete records from your gallery `uploading files and storing to gallery in db (MongoDB)` ```json { "id": "gF96W1zGkO9e", "_comment": "This endpoint uploads files", "resource": "upload-store-file-mongodb", "tokenize": { "type": "JWT", "algorithm": "HS256", "encryption_key": "$ENV.JWT_SECRET_KEY" }, "protected": { "provide_as": "x-access-token", "extract_claims": ["id"] }, "actionables": [ { "id": 1, "mode": "UPLOAD", "uploads": { "max_file_uploads": 2, "max_upload_file_size": 20971520, "allowed_mimetypes": ["image/png", "video/mp4", "image/jpg", "image/jpeg"], "upload_folder": "/gallery/$PROTECTED.id" }, "returnables": ["storage_key", "url", "dominant_color", "file_size"] }, { "id": 2, "mode": "QUERY", "mongo_query": { "collection": "gallery", "action": "INSERT", "parameters": ["user_id", "storage_key", "url", "dominant_color", "file_size"], "with_timestamp": true }, "extract_response_for": [ { "extract": "id", // "id" here is the one from $PROTECTED.id. Remember "id" in token claims are injected into the payload as "id" and used for later processing down the line. "for": "user_id" } ], "returnables": ["id", "user_id", "storage_key", "url", "dominant_color", "file_size"] } ] } ``` ```json // sample request payload multipart/form-data file=@banner.jpg file=@avatar.jpeg ``` ```json { // sample response of uploaded files stored in mongodb gallery collection "success": true, "message": "success", "data": [ { "storage_key": "uploads/29292/gallery/6948fa461936f3cf934061f5/LingoQL_App_Banner_new_1766843566560.jpg", "user_id": "6948fa461936f3cf934061f5", "url": "https://storage.lingoql.com/uploads/29292/gallery/6948fa461936f3cf934061f5/LingoQL_App_Banner_new_1766843566560.jpg", "dominant_color": "#D8B5E6", "file_size": "991931", "id": "694fe4b29576d1dd9dbc47c5" }, { "storage_key": "uploads/29292/gallery/6948fa461936f3cf934061f5/52568057_1766843569278.jpeg", "user_id": "6948fa461936f3cf934061f5", "url": "https://storage.lingoql.com/uploads/29292/gallery/6948fa461936f3cf934061f5/52568057_1766843569278.jpeg", "dominant_color": "#636365", "file_size": "20972", "id": "694fe4b29576d1dd9dbc47c6" } ] } ``` ![](https://cdn.hashnode.com/res/hashnode/image/upload/v1766843833586/ed66383e-d899-4fe8-bb7d-f011eb43c9b0.png) ### Caching pattern ```json { "id": "12vqIWzGkODe6gF", "_comment": "Endpoint to search/fetch users based on status with cache applied", "resource": "fetch-users-with-cache", "tokenize": { "type": "JWT", "algorithm": "HS256", "encryption_key": "$ENV.JWT_SECRET_KEY" }, "protected": { "provide_as": "x-access-token" }, "cacheable": { "read_from_cache": true, // means we'll be reading from cache if available "cache_key": "users_page_$PAYLOAD.page_number", // the key of the cache "duration": 3600 // cache for 1 hour // "invalidate_cache_keys": ["users_page_$PAYLOAD.page_number"], (Optional) only use this to invalidate this cache with the key specified by "cache_key" above. }, "actionables": [ { "id": 1, "mode": "QUERY", "sql_query": { "query": "SELECT id, name FROM users WHERE name ILIKE CONCAT('%', $1, '%')", "parameters": ["search"] }, "returnables": ["id", "name"] // you can also cache this single action by applying "cacheable" here. } ] } ``` ```json { // sample payload "search": "aka", "page_number": 1 } ``` record stored to cache for `1hr`: `users_page_1` ![](https://cdn.hashnode.com/res/hashnode/image/upload/v1766850271705/a6d4bf20-70ad-4559-9225-1a929b57cc26.png) first request takes `38ms` to process ![](https://cdn.hashnode.com/res/hashnode/image/upload/v1766866951837/421b3285-2f4e-4dbb-a074-47a543a80a21.png) subsequent requests take just `~3ms`, a massive `≈92.1%` performance boost! (i.e `12.7×` speed-up) ![](https://cdn.hashnode.com/res/hashnode/image/upload/v1766866955147/94f22fb2-75c1-4c80-af8f-82436d2b19de.png) > NOTE: we recommend setting a lower expiration for your cache, say a few seconds or minutes or hours (and eventually days, depending on your use case). Only use `"invalidate_cache_keys"` on endpoints that changes state (e.g `INSERT`, `UPDATE`, `DELETE`) not on `FETCH`, since it fires for every API request made. Using it with `FETCH` is useless, since the record to be read from the cache is invalidated even before the `FETCH` reaches the cache. Hence, records are fetched from the db or other time-consuming processes. > NOTE: you can also cache a single actionable, rather than the entire endpoint actionables. simply apply same `cacheable` object to the action you want to apply caching to. ### Cron pattern ```json { "id": "12vqIWzGkODe6gF", "_comment": "Cronjob that revives soft deleted accounts", "resource": "my-cronjob", "actionables": [ { "id": 1, "mode": "QUERY", "sql_query": { "query": "UPDATE users SET deleted_at = NULL WHERE deleted_at IS NOT NULL" }, "run_in_background": true, "cron_job": { "execution_interval": "*/30 * * * * *" // executes every 30 seconds } } ] } ``` `before` ![](https://cdn.hashnode.com/res/hashnode/image/upload/v1766874164127/e3a01bcd-2496-4a03-b7f9-ba29b958508c.png) `after 30 seconds` ![](https://cdn.hashnode.com/res/hashnode/image/upload/v1766874208949/a1739182-8bba-427d-a5f3-b2dfd78e8b61.png) > NOTE: > > * Cron Jobs can’t be invoked via http request, even though they still have a resource/id just like other endpoints. > * Cron Jobs are only applied to individual actions, and not on the resource itself > * Cron Jobs are auto started when your service starts. You don’t need any special command to start processing cron jobs. ### Queueing pattern ```json { "id": "12vqIWzGkODe6gF", "_comment": "Queueing jobs", "resource": "queue-job", // endpoint "/queue-job" "tokenize": { "type": "JWT", "algorithm": "HS256", "encryption_key": "$ENV.JWT_SECRET_KEY" }, "protected": { "provide_as": "x-access-token", "extract_claims": ["id"] }, // we can also apply queue to the entire resource/endpoint. // "queue": { // "delay": 180, // "priority": 0 // }, "actionables": [ { "id": 1, "mode": "QUERY", "sql_query": { "query": "UPDATE users SET deleted_at = NULL WHERE id = $1", "parameters": ["$PROTECTED.id"] // the parameters would be captured and stored in the queue to accompany processing later }, "queue": { "delay": 180, // store in queue for 3 minutes before executing the query "priority": 0 // queues with higher priority values are processed first } } ] } ``` `before` ![](https://cdn.hashnode.com/res/hashnode/image/upload/v1766874774146/1f51feea-f89b-45ab-a743-a8e8b0c9f7ef.png) `after` ![](https://cdn.hashnode.com/res/hashnode/image/upload/v1766874866288/5eec0827-ceb3-408a-978f-9ff91fc390b6.png) `logs of the queueing and dequeueing process:` ![](https://cdn.hashnode.com/res/hashnode/image/upload/v1766875686017/ec49cf4a-4277-4e25-aeb4-8ed3a5758520.png) > NOTE: > > * Queued actions can be invoked via http request > * Payload, claims etc. sent to a queued action is persisted with the queue to aid execution later > * Queues with higher priorities are processed first. > * You can specify how long to delay > * Queueing can be applied to both the entire resource or a single actionable (as shown above) > * After processing, the queued job is removed from the queue > * An http request to a queued action returns immediately after the job has been enqueued > * Queues are durable (persistent). i.e stopping, restarting and soft + hard redeploying your app does not affect your queued jobs! ### Webhook pattern A webhook on Sub0 is essentially an endpoint with `run_in_background:true` and a `webhook` property for verifying webhook signatures. Refer to the [Webhooks page](https://docs.lingoql.com/introduction/sub0/apis-abi/webhooks) for more info on how to secure your webhook endpoints. `example webhook` ```json { "id": "0012vDe633281gF", "_comment": "Handling Webhook", "resource": "webhook", "webhook": { "verifications": { "HeaderTokenVerifier": { "header_key": "$HEADER.X-GITLAB-TOKEN", "secret_key": "$ENV.MY_GILAB_TOKEN" }, "IpAllowListVerifier": { "allowed_ips": "$ENV.ALLOWED_IPS" }, "composition_strategy_mode": "ALL" // or "ANY" (defaults to "ALL") } }, // "run_in_background": true, here "actionables": [ { "id": 1, "mode": "QUERY", "sql_query": { "query": "UPDATE users SET deleted_at = NULL WHERE deleted_at IS NOT NULL" }, "run_in_background": true // OR here. whichever one works } ] } ``` `missing allowed ips` ![](https://cdn.hashnode.com/res/hashnode/image/upload/v1766935207422/461b58e7-5de0-4d1b-9898-3097cf523ce5.png) `success (both header token and allowed ips fully validated)` ![](https://cdn.hashnode.com/res/hashnode/image/upload/v1766935236242/ef1c0520-75b1-49f0-bf7c-e03d8a718b15.png) `env. variables` ![](https://cdn.hashnode.com/res/hashnode/image/upload/v1766935265638/7fff2b6f-dd43-4df4-b1a0-d73338d24b37.png) ### Websocket pattern `connecting to websocket on Sub0` Sub0 provides a WebSocket endpoint at `/ws` that clients can connect to. This path is configurable — you can change it at any time from your LingoQL dashboard (for example, to `/my-ws` or any custom route). You may connect to the WebSocket with or without a `uid` (i.e `FORCE_WEBSOCKET_WITH_UID=true`) query parameter. Supplying a `uid` (typically the currently authenticated user’s ID) enables precise, user-specific message delivery — useful when updates occur outside the user’s own WebSocket actions. For example, if an API call credits a user’s wallet, Sub0 can immediately push the updated balance to that specific user’s session. If you connect without a `uid`, you can still send and receive messages normally and will receive any broadcasts sent to all connected clients by default. ```javascript // connecting without a uid const socket = new WebSocket("wss://{{YOUR_SUB0_URL}}/ws"); socket.onopen = () => { console.log("Connected to Sub0 WebSocket"); socket.send(JSON.stringify({ type: "ping" })); }; socket.onmessage = (event) => { const msg = JSON.parse(event.data); console.log("Message received:", msg); }; socket.onerror = (err) => { console.error("WebSocket error:", err); }; socket.onclose = () => { console.log("Connection closed"); }; ``` ```javascript // connecting with a uid const uid = "user_12345"; // e.g. logged-in user id const socket = new WebSocket(`wss://{{YOUR_SUB0_URL}}/ws?uid=${encodeURIComponent(uid)}`); socket.onopen = () => { console.log("Connected with uid:", uid); }; socket.onmessage = (event) => { const msg = JSON.parse(event.data); console.log("User-scoped message:", msg); }; ``` `calling a resource via websocket connection` you can interact with a resource/endpoint just like you would from a http API call. All you need to do is send a payload of the form via websocket: ```json { "resource": "fetch-balance", // resource/endpoint to call. you can use the `id` or `resource` of the endpoint you want to call "action": "balance_check", // (Optional) just a reference you can use to decide what operation to perform on your frontend client "payload": { "example": "value" } // accompanying payload for the request } ``` after a while of processing your call, on success, you would get a response in this form: ```json { "uid": "user_12345", // i.e if you connected with a uid. else this won't be included in the response "action": "balance_check", // (Optional) just a reference you can use to decide what operation to perform on your frontend client "data": { "wallet_balance": 73.5 } // the response from the resource you called } ``` ![](https://cdn.hashnode.com/res/hashnode/image/upload/v1766939945623/b21764fa-693b-44c0-a122-b79d5b0073e3.png) ![](https://cdn.hashnode.com/res/hashnode/image/upload/v1766939952958/c6546a40-791f-4828-85a8-ecc68fffd765.png) `connecting with a uid and calling a resource. fetching wallet balance of current logged in user` ![](https://cdn.hashnode.com/res/hashnode/image/upload/v1766939958962/544ae8c1-66e1-45aa-83e9-e5f356a8ddec.png) the resource for the wallet balance check above is below: ```json { "id": "93fj0012vDe633281gF", "_comment": "Fetching wallet balance", "resource": "fetch-balance", "actionables": [ { "id": 1, "mode": "QUERY", "sql_query": { "query": "SELECT wallet_balance FROM users WHERE id = $1", "parameters": ["$PROTECTED.id"] // i.e the uid connected from websocket }, "returnables": ["wallet_balance"] } ] } ``` how do we protect our websocket to avoid abuse? It’s very easy, simply mark the resource as protected as shown below: ```json { "id": "93fj0012vDe633281gF", "_comment": "Fetching wallet balance with token protection", "resource": "fetch-balance-with-token-protection", "tokenize": { "type": "JWT", "algorithm": "HS256", "encryption_key": "$ENV.JWT_SECRET_KEY" }, "protected": { "provide_as": "x-access-token", "extract_claims": ["id"] }, "actionables": [ { "id": 1, "mode": "QUERY", "sql_query": { "query": "SELECT wallet_balance FROM users WHERE id = $1", "parameters": ["$PROTECTED.id"] }, "returnables": ["wallet_balance"] } ] } ``` next, how do we interract with a protected resource? Simply send a header bearing the logged-in user’s token when connecting to the websocket. Sub0 auto captures this header token and keeps track of it for as long as the connection is valid, and uses it for subsequent calls to protected endpoints. `when no header token is provided the connection fails` ![](https://cdn.hashnode.com/res/hashnode/image/upload/v1766942353272/edd2a1b5-4f02-4367-96d7-dcd3aeb15398.png) `when we provide a header token, the request succeeds` ![](https://cdn.hashnode.com/res/hashnode/image/upload/v1766942375725/22456366-42b1-42da-99f3-921d433c2567.png) `how do i send headers with websocket?` ```javascript const ws = new WebSocket( 'ws://example.com/socket', ['x-access-token', 'eyJ0eXAiO....'] // like so ); ``` `broadcasting websocket messages on Sub0 from actionables` you can broadcast messages to a single or group of connected clients when an action is performed, by simply adding a `broadcast_websocket_message` to any actionable you want to emit an event from. e.g ```json { "id": "0012vDe633281gF", "_comment": "Emitting websocket events to clients", "resource": "websocket", "actionables": [ { "id": 1, "mode": "QUERY", "sql_query": { "query": "UPDATE users SET wallet_balance = wallet_balance + $1::float8 WHERE deleted_at IS NOT NULL", "parameters": ["wallet_balance"] }, "returnables": ["wallet_balance"], // this gets returned as a response via the websocket broadcast "broadcast_websocket_message": { "broadcast_type": "ALL", // i.e emits to all the websocket connected clients. (or SINGLE, or BULK) "action": "wallet_balance_update", "broadcast_to": "$PAYLOAD.uid" // optional. use $PAYLOAD.uids for BULK } } ] } ``` > NOTE: > > * For Websocket requests, your payload is injected with the `uid` (if available) used in initiating the websocket connection, as `id`, only if your payload is an object! > * By default, responses from websocket are returned after execution. So, do not specify `broadcast_websocket_message` in any of the actions that the websocket would execute, otherwise you'll receive duplicate results. > * You don’t need to specify a payload to broadcast when making use of `broadcast_websocket_message`. Sub0 auto sends the response from that action’s returnables. > * When you set `FORCE_WEBSOCKET_WITH_UID=true` in your env. your websocket will force all connections to have a `uid` > * Websockets are disabled by default. You must set the env. `ALLOW_WEBSOCKET_CONNECTIONS=true` to allow connections. ### Combined production flows `sign up and send welcome email to user` Here, we leverage Sub0’s sequential execution of `actionables`. After the sign up occurs, the results from that are passed down to the next `actionable` (runs in background though) that sends a welcome email. ```json { "id": "gF96WzGkO9eo12vq1l0ndIDe6", "_comment": "Endpoint to sign up a new user and sends a welcome email", "resource": "sign-up-send-email", "tokenize": { "custom_claim_fields": ["id", "email"], "type": "JWT", "algorithm": "HS256", "expiration": 604800, "encryption_key": "$ENV.JWT_SECRET_KEY", "property_name": "token" }, "actionables": [ { "id": 1, "mode": "QUERY", "payload_validation": [ { "field": { "name": { "type": "STRING" } }, "min_length": 10, "max_length": 255 }, { "field": { "email": { "type": "EMAIL" } }, "min_length": 50, "max_length": 255 }, { "field": { "password": { "type": "STRING" } }, "min_length": 50, "max_length": 255 } ], "hashables": [ { "property": "password", "algorithm": "BCRYPT", "options": { "rounds_cost": 12, "salt": "$ENV.MY_HASHING_SALT" } } ], "sql_query": { "collection": "users", "query": "INSERT INTO users (id, name, email, password, wallet_balance, created_at, updated_at) VALUES ($1, $2, $3, $4, $5::float8, $6::timestamptz, $7::timestamptz) RETURNING id, name, email", "parameters": ["$GENERATOR.KSUID", "name", "email", "password", "0", "$DATETIME", "$DATETIME"], "with_timestamp": true, "unique": ["email"] }, "main_returnable": true, "returnables": ["id", "name", "email", "token"] }, { "id": 2, "mode": "HTTPREQUEST", "no_op": true, "http": { "url": "https://storage.lingoql.com/lingoql-test-email-template.html", "method": "GET", "headers": { "Content-Type": "application/json" }, "read_file_contents": true } }, { "id": 3, "mode": "HTTPREQUEST", "run_in_background": true, "queue": { "delay": 10, // i.e email would be sent 10 seconds after sign up occurs "priority": 0 }, "http": { "url": "https://api.lambahq.com/v1/action/$ENV.LAMBA_CUSTOMER_ID", "method": "POST", "headers": { "Content-Type": "application/json", "Authorization": "Basic $ENV.LAMBA_API_KEY" }, "request_body": { "service": "low_mail", "low_mail_input": { "integration": "any", "subject": "$PAYLOAD.subject", "message": "", // message body will be auto populated by "extract_response_for" below "recipients": "$PAYLOAD.recipients" } }, "replacers": { "low_mail_input.message": [ { "%name%": "recipients.0.name" }, { "%city%": "recipients.0.address.city" }, { "%unsubscribe_link%": "https://my-unsubscribe-link.com" } ] } }, "depends_on": [ { "resource_id": "self", "action_ids": [2], "extract_response_for": [ { "extract": "self_", "for": "low_mail_input.message" } ] } ] } ] } ``` ```json { // payload // the sign up part "name": "Samuel", "email": "test2@gmail.com", "password": "test", // for the email "subject": "Welcome to Sub0", "recipients": [ { "name": "Sam", "email": "test2@gmail.com", "address": { "city": "Dubai" } } ] } ``` ```json { // result. Email was sent in the background "success": true, "message": "success", "data": { "email": "test2@gmail.com", "id": "37UZarRba0kGKiK4tbqJBcMBaxR", "name": "Samuel", "token": "eyJ0eXAiOiJKV1QiLCJhbGciOiJIUzI1NiJ9.eyJpc3MiOiJMaW5nb1FMIiwiaWF0IjoxNzY2OTU5Njc3LCJleHAiOjE3Njc1NjQ0NzcsInN1YiI6IjM3VVphclJiYTBrR0tpSzR0YnFKQmNNQmF4UiIsImlkIjoiMzdVWmFyUmJhMGtHS2lLNHRicUpCY01CYXhSIiwiZW1haWwiOiJ0ZXN0MkBnbWFpbC5jb20ifQ.Fs2a4so6mJxESPXrZUNMdIkGewAVnV647XkOZWiuL7s" } } ``` `Integrating OAuth` We’ll first start by generating a CSRF token to ensure maximum security. ```json { // generating a CSRF token for a specific user "id": "93fj0012vDe633281gF", "_comment": "Generates and returns a csrf token for a secure OAUTH integration", "resource": "get-csrf-token", "tokenize": { "type": "JWT", "algorithm": "HS256", "encryption_key": "$ENV.JWT_SECRET_KEY" }, "protected": { "provide_as": "x-access-token", "extract_claims": ["id"] }, "actionables": [ { "id": 1, "mode": "QUERY", "sql_query": { "query": "INSERT INTO csrf_token_store (id,user_id,csrf_token) VALUES ($1,$2,$3) RETURNING csrf_token", "parameters": ["$GENERATOR.KSUID", "$PROTECTED.id", "$GENERATOR.SHORTID"] }, "with_timestamp": true, "returnables": ["csrf_token"] } ] } ``` ```json { // sample payload } ``` ```json { // typical response "success": true, "message": "success", "data": { "csrf_token": "1MK2tPDPPr88C0uouPhHD0" } } ``` next, you’ll attach the csrf token to your redirect url to the service you want to authorize your app. In this example, we’ll use github’s OAuth as a use case: ```json { "redirectUri": "https://github.com/login/oauth/authorize?client_id=YOUR_CLIENT_ID&redirect_uri=https://yourapp.com/oauth/callback&scope=read:user%20user:email&state={{csrf_token}}" } ``` When the user gets redirected back, you get a `code`, `state` and possibly the `redirect_uri`. You’ll then call your endpoint below to verify the state (csrf\_token), make a call to github’s endpoint to exchange the code for an access\_token (that you can later store in the user’s record in your db) for further subsequent calls to github (say fetching user’s github profile and extracting relevant info from the response). `verifying and getting access_token from github` ```json { // sequential execution is in play here. with results being passed down to the next actionable below it. "id": "vD93fj0012e633281gF", "_comment": "Verifies Github OAuth. First verifies the csrf token, followed by code to token exchange", "resource": "verify-github-oauth", "tokenize": { "type": "JWT", "algorithm": "HS256", "encryption_key": "$ENV.JWT_SECRET_KEY" }, "protected": { "provide_as": "x-access-token", "extract_claims": ["id"] }, "actionables": [ { "id": 1, "mode": "QUERY", "sql_query": { "query": "SELECT user_id, csrf_token FROM csrf_token_store WHERE csrf_token = $1 AND user_id = $2", "parameters": ["$PAYLOAD.state", "$PROTECTED.id"] }, "must_return_value": true, // forces this actionable to return a value, and if empty, the request fails and exits, indicating the csrf_token isn't valid to begin with "returnables": ["user_id"] }, { "id": 2, "mode": "HTTPREQUEST", "http": { "url": "https://github.com/login/oauth/access_token", "method": "POST", "headers": { "Content-Type": "application/json" }, "request_body": { "client_id": "$ENV.YOUR_CLIENT_ID", "client_secret": "$ENV.YOUR_CLIENT_SECRET", "code": "$PAYLOAD.code", "redirect_uri": "$PAYLOAD.redirect_uri", "state": "$PAYLOAD.state" } }, "returnables": ["access_token"] }, { "_comment": "Your next action should be to store the access token." }, { "_comment": "Then clear the csrf_token from csrf_token_store." }, { "_comment": "Finally, use the access_token to fetch the user's info and store it in the db." } ] } ``` ```json { // sample payload "code": "github_oauth_code_from_callback", "state": "1MK2tPDPPr88C0uouPhHD0", "redirect_uri": "https://yourapp.com/oauth/callback" } ``` These examples show the payoff of the ABI. You are not wiring controllers, workers, upload handlers, and socket servers by hand. You are defining one backend system that already knows how to behave in production. When you want to move faster, start with [Speed up with Sub0 AI](/sub0/speed-up-with-sub0-ai.md). --- # Agent Instructions This documentation is published with GitBook. GitBook is the documentation platform designed so that both humans and AI agents can read, navigate, and reason over technical content effectively. Learn more at gitbook.com. ## Querying This Documentation If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question. Perform an HTTP GET request on the current page URL with the `ask` query parameter: ``` GET https://docs.lingoql.com/sub0/apis-abi/practical-examples.md?ask= ``` The question should be specific, self-contained, and written in natural language. The response will contain a direct answer to the question and relevant excerpts and sources from the documentation. Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.