return the start and finish times of each sync process

[nathandtam]

intro

HI DEV! in this sprint, you will modify our local app's syncing process so that each time we click sync, we return timestamps for started_at (when the sync attempt started) and finished_at (when the sync attempt finished).

steps

part 1: create a local sync_runs table.

it should have four columns: id, started_at, finished_at, and status. each row should represent one sync attempt -- here's an example of what it should look like.

id	started_at	finished_at	status
1	2026-02-26T19:02:11.432Z	2026-02-26T19:02:24.987Z	success

Important

set the id as the primary key and make it so that started_at and status are set to "NOT NULL".

this should be done in the createSchema function of apps/local/app/api/sync/route.ts. follow the format of the other tables created (groups, lessons, and files).

part 2: track the run

• find our GET handler (also in apps/local/app/api/sync/route.ts).
• after opening the database, insert a new row in our sync_runs table with started_at as the current time and status as "running" to represent the new sync attempt.
• once the sync completes, update the same row with the finished_at timestamp and set status as "success". if an error happens, set status as "failed".

part 3: update the response

• construct a new JSON response to include the run ID, the time started, and the time finished. this can be found in the same GET handler function.

Tip

we'll need to change the way we use our try/catch block. currently, we close the database inside the try and then send out the response. but now, think about how we can update our sync_run row after the success or failure. then, we can use a finally block to close the database afterwards.

resources

• creating tables in sqlite
• inserting in sqlite
• try/catch/finally

goals:

when we run http://localhost:3000/api/sync, our current response just shows one of these two messages:

{"message":"Data synchronized successfully"}
{ "error": "Error synchronizing data" }

but now, our new response should look something like this:

{
  "run_id": 12,
  "status": "success",
  "started_at": "2026-02-26T04:12:53.120Z",
  "finished_at": "2026-02-26T04:13:10.991Z"
}