Build a user data API from a business table

Build a personal saved-links feature with database migrations, layered writes, user isolation, and cursor pagination.

Published Sep 20, 2026

This article adds a saved-links API for the workspace. Signed-in users can submit a title and URL, then retrieve their saved links in pages. The API does not accept a user ID or read other users' data.

You will build two endpoints:

Method and path	Purpose
`POST /api/saved-links`	Create a saved link
`GET /api/saved-links?beforeId=123`	Retrieve a page of saved links, omitting `beforeId` on the first request

The API built here can be verified independently. You will need to connect the workspace's list and form to these endpoints later. Editing, deletion, and automatic retrieval of web page information are outside the scope of this implementation.

1. Define the files and data flow

Create the following files:

schema/migrations/0001-db-saved-links.sql
src/core/db/saved-link/index.ts
src/core/repositories/saved-link/index.ts
src/core/services/saved-link/types.ts
src/core/services/saved-link/service.ts
src/api/saved-links/index.ts

The call sequence is API → Service → Repository → DB. SQL stays in the DB layer. The repository converts storage fields into business objects, the service handles creation rules and pagination results, and the API handles authentication, parameter validation, and response formatting.

2. Create the table with a migration

Create schema/migrations/0001-db-saved-links.sql. If a migration numbered 0001 already exists, use the next available number. Keep -db- in the filename so that it matches the main database's migration rule.

CREATE TABLE saved_link (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    user_id INTEGER NOT NULL,
    title TEXT NOT NULL,
    url TEXT NOT NULL,
    created_at INTEGER NOT NULL
);

The created_at field stores Unix time in milliseconds, and user_id corresponds to the current account ID. This example does not add a foreign key or change the existing account deletion flow. If your product allows account deletion, integrate saved-link cleanup into that flow before launch.

Run the local migrations:

npm run db:migrate:local

The template already has a migration mechanism. The main database baseline is schema/db-init.sql, and subsequent changes go in schema/migrations. Do not also add the same table creation statement to the baseline. Otherwise, a new environment would attempt to create the same table again when it applies migrations after the baseline.

After verification, you can run npm run db:migrate:remote for the remote environment. The existing deploy:update flow also runs migrations. Adding a business table does not require a database reset.

3. Define the business objects

Create src/core/services/saved-link/types.ts:

export type SavedLink = {
    id: number;
    title: string;
    url: string;
    createdAt: Date;
};

export type CreateSavedLinkInput = {
    title: string;
    url: string;
};

The business object uses createdAt and Date so that database field names with underscores do not spread to other layers. Pass the user ID separately as the owner of the operation, rather than including it in the creation parameters that the browser can supply.

4. Implement the DB layer

Create src/core/db/saved-link/index.ts:

import type { WorkerCtx } from '@/ctx';

export type SavedLinkRow = {
    id: number;
    title: string;
    url: string;
    created_at: number;
};

export default class DBSavedLinkDao {
    private constructor(private readonly db: D1Database) {}

    static withCtx(ctx: WorkerCtx): DBSavedLinkDao {
        return new DBSavedLinkDao(ctx.env.DB);
    }

    async insert(input: {
        userId: number;
        title: string;
        url: string;
        createdAt: number;
    }): Promise<SavedLinkRow> {
        const row = await this.db.prepare(`
            INSERT INTO saved_link (user_id, title, url, created_at)
            VALUES (?, ?, ?, ?)
            RETURNING id, title, url, created_at
        `).bind(
            input.userId, input.title, input.url, input.createdAt,
        ).first<SavedLinkRow>();

        if (!row) throw new Error('Saved link insert returned no row');
        return row;
    }

    async listForUser(
        userId: number,
        limit: number,
        beforeId?: number,
    ): Promise<SavedLinkRow[]> {
        const statement = beforeId === undefined
            ? this.db.prepare(`
                SELECT id, title, url, created_at FROM saved_link
                WHERE user_id = ? ORDER BY id DESC LIMIT ?
            `).bind(userId, limit)
            : this.db.prepare(`
                SELECT id, title, url, created_at FROM saved_link
                WHERE user_id = ? AND id < ? ORDER BY id DESC LIMIT ?
            `).bind(userId, beforeId, limit);

        const result = await statement.all<SavedLinkRow>();
        return result.results;
    }
}

Both query paths include user_id = ?. Even if a user manually changes beforeId, they can only change the pagination position within their own data. Pass SQL parameters through bind instead of interpolating input into the query string.

The list is ordered by descending auto-incrementing ID, so new records do not shift the next page's position. This example starts with the primary key and no additional indexes. As data grows, assess the need for indexes based on actual query frequency and D1 read volume.

5. Convert data in the repository

Create src/core/repositories/saved-link/index.ts:

import type { WorkerCtx } from '@/ctx';
import DBSavedLinkDao, { type SavedLinkRow } from '@/core/db/saved-link';
import type {
    CreateSavedLinkInput,
    SavedLink,
} from '@/core/services/saved-link/types';

function toSavedLink(row: SavedLinkRow): SavedLink {
    return {
        id: row.id,
        title: row.title,
        url: row.url,
        createdAt: new Date(row.created_at),
    };
}

export const savedLinkRepository = {
    async create(
        ctx: WorkerCtx,
        userId: number,
        input: CreateSavedLinkInput,
        createdAt: Date,
    ): Promise<SavedLink> {
        const row = await DBSavedLinkDao.withCtx(ctx).insert({
            userId,
            title: input.title,
            url: input.url,
            createdAt: createdAt.getTime(),
        });
        return toSavedLink(row);
    },

    async listForUser(
        ctx: WorkerCtx,
        userId: number,
        limit: number,
        beforeId?: number,
    ): Promise<SavedLink[]> {
        const rows = await DBSavedLinkDao.withCtx(ctx).listForUser(
            userId, limit, beforeId,
        );
        return rows.map(toSavedLink);
    },
};

The business types use import type only to constrain the mapping results. The repository does not call the service. The new API should not import this repository directly.

6. Organize business operations in the service

Create src/core/services/saved-link/service.ts:

import type { WorkerCtx } from '@/ctx';
import { savedLinkRepository } from '@/core/repositories/saved-link';
import type { CreateSavedLinkInput } from './types';

const PAGE_SIZE = 20;

export const savedLinkService = {
    async create(
        ctx: WorkerCtx,
        userId: number,
        input: CreateSavedLinkInput,
    ) {
        return savedLinkRepository.create(ctx, userId, input, new Date());
    },

    async listForUser(ctx: WorkerCtx, userId: number, beforeId?: number) {
        const rows = await savedLinkRepository.listForUser(
            ctx, userId, PAGE_SIZE + 1, beforeId,
        );
        const items = rows.slice(0, PAGE_SIZE);
        const nextCursor = rows.length > PAGE_SIZE
            ? items[items.length - 1].id
            : null;

        return { items, nextCursor };
    },
};

Each query fetches one extra row solely to determine whether another page exists. The response returns at most 20 items. Stop when nextCursor is null. Otherwise, pass it as beforeId in the next request.

The server generates the creation timestamp. This example allows the same URL to be saved multiple times as a business rule. The creation operation also has no request deduplication. The frontend should disable the button while submitting. After a network timeout, refresh the list to check the result before taking further action, rather than automatically retrying unconditionally.

7. Add the API

Create src/api/saved-links/index.ts:

import { Hono } from 'hono';
import { bodyLimit } from 'hono/body-limit';
import { z } from 'zod';
import { resolveFetchWorkerCtx } from '@/ctx';
import { gResultCode } from '@/errors';
import type { Variables } from '@/types';
import { authenticatedGuard } from '@/core/services/auth/guards/authenticated';
import { savedLinkService } from '@/core/services/saved-link/service';
import type { SavedLink } from '@/core/services/saved-link/types';

const savedLinksApi = new Hono<{
    Bindings: CloudflareBindings;
    Variables: Variables;
}>();

const createSchema = z.object({
    title: z.string().trim().min(1).max(120),
    url: z.string().trim().max(2048).url().refine((value) => {
        if (!URL.canParse(value)) return false;
        const protocol = new URL(value).protocol;
        return protocol === 'http:' || protocol === 'https:';
    }),
}).strict();

const querySchema = z.object({
    beforeId: z.coerce.number().int().positive().max(Number.MAX_SAFE_INTEGER).optional(),
});

type SavedLinkDto = {
    id: number;
    title: string;
    url: string;
    createdAt: string;
};

function toDto(link: SavedLink): SavedLinkDto {
    return {
        id: link.id,
        title: link.title,
        url: link.url,
        createdAt: link.createdAt.toISOString(),
    };
}

savedLinksApi.post('/', bodyLimit({ maxSize: 16 * 1024 }), async (c) => {
    const guard = authenticatedGuard(c);
    if (!guard.success) {
        return c.json(guard, guard.code === gResultCode.authLoginRequired ? 401 : 403);
    }

    const body: unknown = await c.req.json().catch(() => null);
    const parsed = createSchema.safeParse(body);
    if (!parsed.success) {
        return c.json({ success: false, code: gResultCode.badParams }, 400);
    }

    const link = await savedLinkService.create(
        resolveFetchWorkerCtx(c), guard.data.authContext.userId, parsed.data,
    );
    return c.json({ success: true, data: toDto(link) }, 201);
});

savedLinksApi.get('/', async (c) => {
    const guard = authenticatedGuard(c);
    if (!guard.success) {
        return c.json(guard, guard.code === gResultCode.authLoginRequired ? 401 : 403);
    }

    const parsed = querySchema.safeParse(c.req.query());
    if (!parsed.success) {
        return c.json({ success: false, code: gResultCode.badParams }, 400);
    }

    const page = await savedLinkService.listForUser(
        resolveFetchWorkerCtx(c), guard.data.authContext.userId, parsed.data.beforeId,
    );
    return c.json({
        success: true,
        data: { items: page.items.map(toDto), nextCursor: page.nextCursor },
    });
});

export default savedLinksApi;

The API maps Date values to ISO strings and returns only explicitly declared fields. Invalid parameters return 400, signed-out requests return 401, and unmet authentication requirements other than sign-in return 403. When a request exceeds the size limit, bodyLimit returns 413. Database exceptions go to the existing API error handler.

The url field allows only HTTP and HTTPS. This example stores the URL without making a server-side request to the target page. If you add page fetching later, define allowed destinations and redirect rules separately.

Import and register the subroute in src/api/routes.ts, before apiApp is mounted on the application:

import savedLinksApi from './saved-links';

// Place this alongside the existing apiApp.route(...) declarations.
apiApp.route('/saved-links', savedLinksApi);

The existing entry point applies the /api prefix to all API routes. Do not repeat /api/saved-links in the subroute, and do not disable global CSRF protection to simplify debugging.

8. Verify through the browser

Sign in to the local site, then run the following in the browser's developer console on that same site:

const created = await fetch('/api/saved-links', {
    method: 'POST',
    headers: { 'Content-Type': 'application/json' },
    credentials: 'same-origin',
    body: JSON.stringify({ title: 'Saavo', url: 'https://saavo.dev' }),
});
console.log(created.status, await created.json());

const firstPage = await fetch('/api/saved-links', {
    credentials: 'same-origin',
}).then((response) => response.json());
console.log(firstPage);

if (firstPage.success && firstPage.data.nextCursor !== null) {
    const nextPage = await fetch(
        `/api/saved-links?beforeId=${firstPage.data.nextCursor}`,
    ).then((response) => response.json());
    console.log(nextPage);
}

The creation request should return 201, and the list should contain the newly created record. These commands are for API acceptance checks. The production page should use the project's existing request wrapper and locale resources for success messages, validation failures, network errors, and other text.

Check these additional cases:

An empty title, an invalid URL, or a javascript: URL returns 400 without adding a database record.
A request body that includes an extra userId returns 400. The browser cannot specify the identity.
A signed-out request returns 401 without returning sign-in page HTML.
Create records with accounts A and B. Each account can see only its own saved links.
After creating more than 20 records, continue fetching with nextCursor until it is null. Existing records should have no duplicates or omissions.
Add a saved link while paginating. It appears when you fetch the first page again, while subsequent cursor-based requests continue from their previous position.
Running the local migrations again does not recreate the table.

Finally, run npm run lint, npm run typecheck, and npm run build. If you see no such table: saved_link, first confirm that the migration ran against the local database used by the current development server.

Next, you can add a list and form to the workspace, or follow the paid entitlements tutorial to require a purchase for these two endpoints. When adding edit or delete endpoints, make sure their queries also filter by both the current user and the record ID.