Querying

In Akan, database queries usually live in `document.ts` filters. Pages and services ask for a named filter instead of rebuilding the same condition everywhere.

Use `filter().arg()` for required inputs.
Use `filter().opt()` for optional inputs.
Use the `q` helper for readable conditions.

Basic Filter

Start with the query your screen needs. For example, a project page often needs active tasks in that project.

Tasks in project

Optional Conditions

Optional filters should add conditions only when the user actually selected something. `q.when` keeps that logic compact.

Filter by assignees

Range And OR

Use `q.between` for periods and `q.any` for OR. This keeps date dashboards and status boards readable.

Dashboard period

Raw Query

Use raw query only when the helper cannot express the condition. Keep it as a small SQL fragment and always pass values as parameters.

Score threshold

How It Becomes SQL

Akan stores most model data as document fields, then turns filter objects into SQL where clauses. You write in document shape, the adaptor compiles it for the database.

Query helper	Document query	SQL condition text
`plain equality`	`{ status: "done" }`	`json_extract(_doc, '$.status') = ?`
`q.eq`	`{ priority: q.eq("high") }`	`json_extract(_doc, '$.priority') = ?`
`q.ne`	`{ status: q.ne("archived") }`	`json_extract(_doc, '$.status') != ?`
`q.oneOf`	`{ status: q.oneOf(["done", "reviewing"]) }`	`json_extract(_doc, '$.status') IN (?, ?)`
`q.notOneOf`	`{ status: q.notOneOf(["archived", "deleted"]) }`	`json_extract(_doc, '$.status') NOT IN (?, ?)`
`q.gt`	`{ score: q.gt(80) }`	`json_extract(_doc, '$.score') > ?`
`q.gte`	`{ progress: q.gte(50) }`	`json_extract(_doc, '$.progress') >= ?`
`q.lt`	`{ retryCount: q.lt(3) }`	`json_extract(_doc, '$.retryCount') < ?`
`q.lte`	`{ dueAt: q.lte(to) }`	`json_extract(_doc, '$.dueAt') <= ?`
`q.between`	`{ updatedAt: q.between(from, to) }`	`json_extract(_doc, '$.updatedAt') BETWEEN ? AND ?`
`q.exists`	`q.exists("assignee")`	`json_type(_doc, '$.assignee') IS NOT NULL`
`q.missing`	`q.missing("deletedAt")`	`json_type(_doc, '$.deletedAt') IS NULL`
`q.empty`	`q.empty("removedAt")`	`json_extract(_doc, '$.removedAt') IS NULL OR json_extract(_doc, '$.removedAt') = ''`
`q.has`	`{ tags: q.has("urgent") }`	`EXISTS (SELECT 1 FROM json_each(json_extract(_doc, '$.tags')) WHERE value = ?)`
`q.contains`	`{ title: q.contains("release") }`	`json_extract(_doc, '$.title') LIKE ?`
`q.all`	`q.all({ project }, { status: "active" })`	`(json_extract(_doc, '$.project') = ?) AND (json_extract(_doc, '$.status') = ?)`
`q.any`	`q.any({ status: "done" }, { status: "reviewing" })`	`(json_extract(_doc, '$.status') = ?) OR (json_extract(_doc, '$.status') = ?)`
`q.not`	`q.not({ status: "archived" })`	`NOT (json_extract(_doc, '$.status') = ?)`
`q.when true`	`q.when(userIds.length, { user: q.oneOf(userIds) })`	`json_extract(_doc, '$.user') IN (?, ...)`
`q.when false`	`q.when(false, { user })`	`1 = 1`
`nested path`	`{ "profile.city": "Seoul" }`	`json_extract(_doc, '$.profile.city') = ?`
`array field`	`{ watchers: q.has(userId) }`	`EXISTS (SELECT 1 FROM json_each(json_extract(_doc, '$.watchers')) WHERE value = ?)`
`q.raw`	`q.raw("json_extract(_doc, '$.score') > ?", [minScore])`	`(json_extract(_doc, '$.score') > ?)`

These SQL snippets are simplified to show the idea. Akan keeps values parameterized, so user input should be passed as values instead of being pasted into raw SQL strings.

Why does Akan store most model data as JSON document fields?

Schema changes are lighter. Adding a small field usually does not require a table migration, so product code can move faster.
Akan prefers query-first document design before low-level query tuning. Data that is read together can stay together, reducing extra joins and service glue code.
Business models often contain nested settings, histories, options, and snapshots. JSON fields keep those shapes natural while still allowing SQL filters for important paths.
You can denormalize intentionally for list/detail screens, then add indexes only to the paths that become hot.

Tips

Name filters after screens or use cases: `inProject`, `inPeriod`, `forDashboard`.
Keep page code free of query-building details.
Prefer helper queries before raw SQL.
Add indexes for filters that become important traffic paths.

Querying

In Akan, database queries usually live in `document.ts` filters. Pages and services ask for a named filter instead of rebuilding the same condition everywhere.

Use `filter().arg()` for required inputs.
Use `filter().opt()` for optional inputs.
Use the `q` helper for readable conditions.

Basic Filter

Start with the query your screen needs. For example, a project page often needs active tasks in that project.

Tasks in project

Optional Conditions

Optional filters should add conditions only when the user actually selected something. `q.when` keeps that logic compact.

Filter by assignees

Range And OR

Use `q.between` for periods and `q.any` for OR. This keeps date dashboards and status boards readable.

Dashboard period

Raw Query

Use raw query only when the helper cannot express the condition. Keep it as a small SQL fragment and always pass values as parameters.

Score threshold

How It Becomes SQL

Akan stores most model data as document fields, then turns filter objects into SQL where clauses. You write in document shape, the adaptor compiles it for the database.

Query helper	Document query	SQL condition text
`plain equality`	`{ status: "done" }`	`json_extract(_doc, '$.status') = ?`
`q.eq`	`{ priority: q.eq("high") }`	`json_extract(_doc, '$.priority') = ?`
`q.ne`	`{ status: q.ne("archived") }`	`json_extract(_doc, '$.status') != ?`
`q.oneOf`	`{ status: q.oneOf(["done", "reviewing"]) }`	`json_extract(_doc, '$.status') IN (?, ?)`
`q.notOneOf`	`{ status: q.notOneOf(["archived", "deleted"]) }`	`json_extract(_doc, '$.status') NOT IN (?, ?)`
`q.gt`	`{ score: q.gt(80) }`	`json_extract(_doc, '$.score') > ?`
`q.gte`	`{ progress: q.gte(50) }`	`json_extract(_doc, '$.progress') >= ?`
`q.lt`	`{ retryCount: q.lt(3) }`	`json_extract(_doc, '$.retryCount') < ?`
`q.lte`	`{ dueAt: q.lte(to) }`	`json_extract(_doc, '$.dueAt') <= ?`
`q.between`	`{ updatedAt: q.between(from, to) }`	`json_extract(_doc, '$.updatedAt') BETWEEN ? AND ?`
`q.exists`	`q.exists("assignee")`	`json_type(_doc, '$.assignee') IS NOT NULL`
`q.missing`	`q.missing("deletedAt")`	`json_type(_doc, '$.deletedAt') IS NULL`
`q.empty`	`q.empty("removedAt")`	`json_extract(_doc, '$.removedAt') IS NULL OR json_extract(_doc, '$.removedAt') = ''`
`q.has`	`{ tags: q.has("urgent") }`	`EXISTS (SELECT 1 FROM json_each(json_extract(_doc, '$.tags')) WHERE value = ?)`
`q.contains`	`{ title: q.contains("release") }`	`json_extract(_doc, '$.title') LIKE ?`
`q.all`	`q.all({ project }, { status: "active" })`	`(json_extract(_doc, '$.project') = ?) AND (json_extract(_doc, '$.status') = ?)`
`q.any`	`q.any({ status: "done" }, { status: "reviewing" })`	`(json_extract(_doc, '$.status') = ?) OR (json_extract(_doc, '$.status') = ?)`
`q.not`	`q.not({ status: "archived" })`	`NOT (json_extract(_doc, '$.status') = ?)`
`q.when true`	`q.when(userIds.length, { user: q.oneOf(userIds) })`	`json_extract(_doc, '$.user') IN (?, ...)`
`q.when false`	`q.when(false, { user })`	`1 = 1`
`nested path`	`{ "profile.city": "Seoul" }`	`json_extract(_doc, '$.profile.city') = ?`
`array field`	`{ watchers: q.has(userId) }`	`EXISTS (SELECT 1 FROM json_each(json_extract(_doc, '$.watchers')) WHERE value = ?)`
`q.raw`	`q.raw("json_extract(_doc, '$.score') > ?", [minScore])`	`(json_extract(_doc, '$.score') > ?)`

These SQL snippets are simplified to show the idea. Akan keeps values parameterized, so user input should be passed as values instead of being pasted into raw SQL strings.

Why does Akan store most model data as JSON document fields?

Schema changes are lighter. Adding a small field usually does not require a table migration, so product code can move faster.
Akan prefers query-first document design before low-level query tuning. Data that is read together can stay together, reducing extra joins and service glue code.
Business models often contain nested settings, histories, options, and snapshots. JSON fields keep those shapes natural while still allowing SQL filters for important paths.
You can denormalize intentionally for list/detail screens, then add indexes only to the paths that become hot.

Tips

Name filters after screens or use cases: `inProject`, `inPeriod`, `forDashboard`.
Keep page code free of query-building details.
Prefer helper queries before raw SQL.
Add indexes for filters that become important traffic paths.