Build an API with related database tables

A task belongs to a project, while a project can have many tasks. Represent that one-to-many relationship with two tables: projects containing a text name, and tasks containing text title, text status and a project_id reference. Twidget generates an _id for each record.

Create a synthetic project called “Marketing launch”. Copy its actual generated _id into the project_id value of two disposable tasks. Store a compatible identifier value; the task's own _id and its project_id mean different things. Use a second project and task when checking that filters do not mix the projects together.

Choose the reference field type that preserves the ID representation your query uses, and inspect a returned record to confirm it. Do not use the project name as a substitute: names can change or repeat. The table-relationship documentation describes ID-based one-to-one, one-to-many and junction-table patterns.

These references are a data model. Storing a project ID does not itself prove that the project exists, belongs to the current user or will survive a later deletion. Add those decisions explicitly. For your first tables, follow the CRUD API guide.

Create a custom GET endpoint such as /examples/tasks-with-projects. Add Get Multiple Data and select the tasks table. Set an intentional record limit before adding related records so a demonstration cannot become an unbounded list.

Open OUTPUT. Under Related records, select Add related records. Set Related table to projects, Field in this table to project_id, and Matching field in related table to _id. Set Include as to project and choose the single-record option under Include.

Use project as a new output name; do not overwrite project_id or an existing task field. Give the database command a clear Return as name, then select that value in the endpoint's Output. Run a draft check and inspect where the project appears in each task.

The intended contract is a task containing its own fields and a nested project object. Confirm the actual structure before building a client around it. The related-records documentation shows the same controls using orders and customers; the field names here belong to this example.

For a project detail screen, use a route such as GET /examples/projects/{project_id}/tasks. Add the project_id path input and map it into the tasks query: stored project_id equals the path input. Keep the identifier in the path consistent with the one the client selected.

Set a limit and stable ordering for the child list. A project can accumulate many tasks, and including them all in a single detail response can grow without bounds. The filtering and pagination guide explains how to expose deliberate result bounds.

You can also retrieve projects and include tasks through Related records: match the project's _id against the task's project_id and choose the list option. That is useful for a small bounded result. A separate paginated child endpoint is easier to control when task counts grow.

Define what a missing project means. A child query returning an empty array cannot tell the caller whether the project exists with no tasks or does not exist at all. If that distinction matters, look up and authorize the project first, then query its tasks. Return the error or empty-state contract your frontend is designed to handle.

Before saving a task, use Get Data to find the selected project. Add a Validate rule that requires an acceptable match before Save Data. A malformed ID, missing project or disallowed project must not reach the write. The validation guide explains command order and rejected-request checks.

For private data, obtain the signed-in customer in the backend and include its ownership condition in the project lookup. A caller-supplied project ID is a selection, not permission. Repeat the rule when changing a task's project, and ensure ordinary task reads and updates also enforce ownership. Follow the record-ownership guide for the surrounding access pattern.

Related-record inclusion is a lookup, not an authorization filter on every nested record. Keep related tables safe for the response you return. If a related record contains private fields, return a deliberately shaped object or use a separate lookup that selects only allowed values. Review the complete nested result, not just the visible task columns.

A project lookup followed by a task save does not automatically enforce a foreign-key constraint under concurrent deletion. Choose a deletion policy: prevent deletion while tasks exist, reassign tasks, or explicitly remove dependants with a supported coordinated workflow. Do not describe an ID field as an automatically enforced database constraint.

In Fields to return, select only task fields required by the client, including the matching field needed for the relationship. Then inspect the related project separately: choosing task fields should not be treated as proof that all nested project fields are safe.

Test four small cases: a task with a matching project, a project with several tasks, a project with no tasks and a task carrying a deliberately nonexistent reference. Observe the no-match value in the actual output and make the frontend handle it explicitly. Do not assume it will have the same shape as the one-record case.

For protected examples, add two synthetic customers and ensure one cannot request, attach to or edit the other's project. Test a known valid foreign ID as well as a nonexistent ID: failure only on nonexistent IDs does not prove authorization. The task-management guide provides the surrounding record lifecycle.

If a relationship is missing unexpectedly, compare the stored reference with the target record's _id, check the field types and confirm the correct workspace and tables. Index frequently queried reference fields once your model is established. Keep result limits deliberate when several related lists are involved.

Publish the reviewed endpoint in the intended workspace. Use the API client to check the actual URL, method, status and response body. Repeat valid, empty, missing-reference and unauthorized requests through the published API; a draft relationship preview alone does not verify a released client contract.

In the frontend, render the task title independently from the related project label. Show a clear fallback when a project is unavailable rather than crashing on a missing nested object. For a larger project detail screen, load the paginated task endpoint separately and retain a retry state.

The React frontend guide covers a defensive browser connection. The dashboard guide adds counts and totals without making the interface calculate them from one page of records.

Start with one clear relationship and a small selected response. Add more related tables only when their access rules, output size and no-match behavior remain understandable. This read pattern is part of Twidget's backend workflow tools.