[AI] Accept API tokens in sync-server authentication

packages/sync-server/migrations/1777334400000-create-api-tokens.js

@@ -0,0 +1,52 @@
+import { getAccountDb } from '../src/account-db';
+
+export const up = async function () {
+  const accountDb = getAccountDb();
+
+  accountDb.transaction(() => {
+    accountDb.exec(
+      `
+      CREATE TABLE api_tokens (
+        id TEXT PRIMARY KEY,
+        user_id TEXT NOT NULL,
+        name TEXT NOT NULL,
+        token_hash TEXT NOT NULL,
+        token_prefix TEXT NOT NULL,
+        created_at INTEGER NOT NULL,
+        last_used_at INTEGER,
+        expires_at INTEGER NOT NULL,
+        enabled INTEGER DEFAULT 1,
+        FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
+      );
+
+      CREATE TABLE api_token_budgets (
+        token_id TEXT NOT NULL,
+        file_id TEXT NOT NULL,
+        PRIMARY KEY (token_id, file_id),
+        FOREIGN KEY (token_id) REFERENCES api_tokens(id) ON DELETE CASCADE,
+        FOREIGN KEY (file_id) REFERENCES files(id) ON DELETE CASCADE
+      );
+
+      CREATE INDEX idx_api_tokens_user_id ON api_tokens(user_id);
+      CREATE INDEX idx_api_tokens_prefix ON api_tokens(token_prefix);
+      CREATE INDEX idx_api_token_budgets_token_id ON api_token_budgets(token_id);
+      `,
+    );
+  });
+};
+
+export const down = async function () {
+  const accountDb = getAccountDb();
+
+  accountDb.transaction(() => {
+    accountDb.exec(
+      `
+      DROP INDEX IF EXISTS idx_api_token_budgets_token_id;
+      DROP INDEX IF EXISTS idx_api_tokens_prefix;
+      DROP INDEX IF EXISTS idx_api_tokens_user_id;
+      DROP TABLE IF EXISTS api_token_budgets;
+      DROP TABLE IF EXISTS api_tokens;
+      `,
+    );
+  });
+};

packages/sync-server/src/app-account.js

@@ -127,8 +127,8 @@ app.post('/login', authRateLimiter, async (req, res) => {
   res.send({ status: 'ok', data: { token } });
 });
 
-app.post('/change-password', (req, res) => {
-  const session = validateSession(req, res);
+app.post('/change-password', async (req, res) => {
+  const session = await validateSession(req, res);
   if (!session) return;
 
   if (!isAdmin(session.user_id)) {
@@ -159,8 +159,8 @@ app.post('/change-password', (req, res) => {
   res.send({ status: 'ok', data: {} });
 });
 
-app.post('/server-prefs', (req, res) => {
-  const session = validateSession(req, res);
+app.post('/server-prefs', async (req, res) => {
+  const session = await validateSession(req, res);
   if (!session) return;
 
   if (!isAdmin(session.user_id)) {
@@ -184,8 +184,8 @@ app.post('/server-prefs', (req, res) => {
   res.send({ status: 'ok', data: {} });
 });
 
-app.get('/validate', (req, res) => {
-  const session = validateSession(req, res);
+app.get('/validate', async (req, res) => {
+  const session = await validateSession(req, res);
   if (session) {
     const user = getUserInfo(session.user_id);
     if (!user) {

packages/sync-server/src/app-admin.js

@@ -214,9 +214,9 @@ app.get('/access', validateSessionMiddleware, (req, res) => {
   res.json(accesses);
 });
 
-app.post('/access', (req, res) => {
+app.post('/access', async (req, res) => {
   const userAccess = req.body || {};
-  const session = validateSession(req, res);
+  const session = await validateSession(req, res);
 
   if (!session) return;
 
@@ -269,9 +269,9 @@ app.post('/access', (req, res) => {
   res.status(200).send({ status: 'ok', data: {} });
 });
 
-app.delete('/access', (req, res) => {
+app.delete('/access', async (req, res) => {
   const fileId = req.query.fileId;
-  const session = validateSession(req, res);
+  const session = await validateSession(req, res);
   if (!session) return;
 
   const { granted } = UserService.checkFilePermission(

packages/sync-server/src/services/api-token-service.ts

@@ -0,0 +1,370 @@
+import crypto from 'crypto';
+
+import * as bcrypt from 'bcrypt';
+
+import { getAccountDb } from '#account-db';
+import { TOKEN_EXPIRATION_NEVER } from '#util/validate-user';
+
+// ============================================
+// Database Row Types (internal use)
+// ============================================
+
+/** Raw row from api_tokens table */
+type ApiTokenRow = {
+  id: string;
+  user_id: string;
+  name: string;
+  token_hash: string;
+  token_prefix: string;
+  created_at: number;
+  last_used_at: number | null;
+  expires_at: number;
+  enabled: number; // SQLite stores booleans as 0/1
+};
+
+/** Raw row from api_token_budgets table */
+type ApiTokenBudgetRow = {
+  token_id: string;
+  file_id: string;
+};
+
+/** Database wrapper type */
+type WrappedDatabase = {
+  all<T = unknown>(sql: string, params?: unknown[]): T[];
+  first<T = unknown>(sql: string, params?: unknown[]): T | null;
+  mutate(
+    sql: string,
+    params?: unknown[],
+  ): { changes: number; insertId: number | bigint };
+  transaction<T>(fn: () => T): T;
+};
+
+// ============================================
+// Public API Types (exported)
+// ============================================
+
+/** Result returned when creating a new token */
+export type CreateTokenResult = {
+  id: string;
+  token: string; // Only exposed at creation time
+  prefix: string;
+  name: string;
+  budgetIds: string[];
+  createdAt: number;
+  expiresAt: number;
+};
+
+/** Result returned when validating a token successfully */
+export type ValidateTokenResult = {
+  userId: string;
+  tokenId: string;
+  budgetIds: string[];
+};
+
+/** Token information returned when listing tokens */
+export type TokenListItem = {
+  id: string;
+  name: string;
+  prefix: string;
+  createdAt: number;
+  lastUsedAt: number | null;
+  expiresAt: number;
+  enabled: boolean;
+  budgetIds: string[];
+};
+
+/** API Token Service interface */
+export type ApiTokenService = {
+  createToken(
+    userId: string,
+    name: string,
+    budgetIds?: string[],
+    expiresAt?: number | null,
+  ): Promise<CreateTokenResult>;
+  validateToken(token: string): Promise<ValidateTokenResult | null>;
+  listTokens(userId: string): TokenListItem[];
+  revokeToken(tokenId: string, userId: string): boolean;
+  setTokenEnabled(tokenId: string, userId: string, enabled: boolean): boolean;
+  getTokenBudgets(tokenId: string): string[];
+};
+
+// ============================================
+// Constants
+// ============================================
+
+const TOKEN_PREFIX = 'act_' as const;
+const TOKEN_LENGTH = 32 as const;
+// base64 produces 4 chars per 3 bytes, then ~3% are filtered out as non-alphanumeric
+// 32 chars * (3 bytes / 4 chars) * 1.34 safety margin = 32 bytes → 43 base64 chars → <1e-8 failure rate
+const TOKEN_RANDOM_BYTES = Math.ceil(((TOKEN_LENGTH * 3) / 4) * 1.34);
+const BCRYPT_ROUNDS = 12 as const;
+
+// ============================================
+// Helper Functions
+// ============================================
+
+/**
+ * Generate a secure random API token
+ * Format: act_<32 random chars>
+ */
+function generateToken(): string {
+  let randomPart = '';
+
+  // Loop until we have enough alphanumeric characters (extremely rare to need multiple iterations)
+  while (randomPart.length < TOKEN_LENGTH) {
+    const bytes = crypto.randomBytes(TOKEN_RANDOM_BYTES);
+    randomPart += bytes.toString('base64url').replace(/[^a-zA-Z0-9]/g, '');
+  }
+
+  return `${TOKEN_PREFIX}${randomPart.slice(0, TOKEN_LENGTH)}`;
+}
+
+/**
+ * Extract the prefix portion of a token for lookup
+ * @param token - The full token
+ * @returns The prefix (first 12 chars: "act_" + 8 chars)
+ */
+function extractPrefix(token: string): string {
+  return token.slice(0, 12);
+}
+
+/**
+ * Hash a token using bcrypt
+ * @param token - The token to hash
+ * @returns The bcrypt hash
+ */
+async function hashToken(token: string): Promise<string> {
+  return bcrypt.hash(token, BCRYPT_ROUNDS);
+}
+
+/**
+ * Verify a token against a hash
+ * @param token - The token to verify
+ * @param hash - The bcrypt hash to compare against
+ * @returns True if the token matches
+ */
+async function verifyToken(token: string, hash: string): Promise<boolean> {
+  return bcrypt.compare(token, hash);
+}
+
+// ============================================
+// Service Implementation
+// ============================================
+
+export const apiTokenService: ApiTokenService = {
+  /**
+   * Create a new API token for a user
+   * @param userId - The user ID
+   * @param name - A friendly name for the token
+   * @param budgetIds - Array of budget/file IDs to scope the token to (empty = all)
+   * @param expiresAt - Unix timestamp for expiration, or null for never
+   */
+  async createToken(
+    userId: string,
+    name: string,
+    budgetIds: string[] = [],
+    expiresAt: number | null = null,
+  ): Promise<CreateTokenResult> {
+    const accountDb = getAccountDb() as WrappedDatabase;
+    const token = generateToken();
+    const tokenHash = await hashToken(token);
+    const tokenPrefix = extractPrefix(token);
+    const tokenId = crypto.randomUUID();
+    const createdAt = Math.floor(Date.now() / 1000);
+    const expiration = expiresAt ?? TOKEN_EXPIRATION_NEVER;
+
+    accountDb.transaction(() => {
+      accountDb.mutate(
+        `INSERT INTO api_tokens (id, user_id, name, token_hash, token_prefix, created_at, expires_at, enabled)
+         VALUES (?, ?, ?, ?, ?, ?, ?, 1)`,
+        [tokenId, userId, name, tokenHash, tokenPrefix, createdAt, expiration],
+      );
+
+      // Add budget scopes if specified
+      for (const budgetId of budgetIds) {
+        accountDb.mutate(
+          `INSERT INTO api_token_budgets (token_id, file_id) VALUES (?, ?)`,
+          [tokenId, budgetId],
+        );
+      }
+    });
+
+    return {
+      id: tokenId,
+      token, // Only returned on creation
+      prefix: tokenPrefix,
+      name,
+      budgetIds,
+      createdAt,
+      expiresAt: expiration,
+    };
+  },
+
+  /**
+   * Validate an API token and return the associated user context
+   * @param token - The full API token
+   */
+  async validateToken(token: string): Promise<ValidateTokenResult | null> {
+    if (!token || !token.startsWith(TOKEN_PREFIX)) {
+      return null;
+    }
+
+    const accountDb = getAccountDb() as WrappedDatabase;
+    const prefix = extractPrefix(token);
+
+    const tokenRow = accountDb.first<ApiTokenRow>(
+      `SELECT id, user_id, token_hash, expires_at, enabled, last_used_at
+       FROM api_tokens
+       WHERE token_prefix = ?`,
+      [prefix],
+    );
+
+    if (!tokenRow || !tokenRow.enabled) {
+      return null;
+    }
+
+    const now = Math.floor(Date.now() / 1000);
+    if (
+      tokenRow.expires_at !== TOKEN_EXPIRATION_NEVER &&
+      tokenRow.expires_at < now
+    ) {
+      return null;
+    }
+
+    if (!(await verifyToken(token, tokenRow.token_hash))) {
+      return null;
+    }
+
+    const shouldUpdateLastUsed =
+      !tokenRow.last_used_at || now - tokenRow.last_used_at > 60;
+    if (shouldUpdateLastUsed) {
+      accountDb.mutate(`UPDATE api_tokens SET last_used_at = ? WHERE id = ?`, [
+        now,
+        tokenRow.id,
+      ]);
+    }
+
+    const budgetRows = accountDb.all<ApiTokenBudgetRow>(
+      `SELECT file_id FROM api_token_budgets WHERE token_id = ?`,
+      [tokenRow.id],
+    );
+
+    return {
+      userId: tokenRow.user_id,
+      tokenId: tokenRow.id,
+      budgetIds: budgetRows.map(row => row.file_id),
+    };
+  },
+
+  /**
+   * List all API tokens for a user (without revealing token values)
+   * @param userId - The user ID
+   */
+  listTokens(userId: string): TokenListItem[] {
+    const accountDb = getAccountDb() as WrappedDatabase;
+
+    const tokens = accountDb.all<ApiTokenRow>(
+      `SELECT id, name, token_prefix, created_at, last_used_at, expires_at, enabled
+       FROM api_tokens
+       WHERE user_id = ?
+       ORDER BY created_at DESC`,
+      [userId],
+    );
+
+    // Early return if no tokens
+    if (tokens.length === 0) {
+      return [];
+    }
+
+    // Fetch all budgets for all tokens in a single query (avoids N+1)
+    const tokenIds = tokens.map(t => t.id);
+    const placeholders = tokenIds.map(() => '?').join(',');
+    const allBudgets = accountDb.all<ApiTokenBudgetRow>(
+      `SELECT token_id, file_id FROM api_token_budgets WHERE token_id IN (${placeholders})`,
+      tokenIds,
+    );
+
+    // Build a lookup map: token_id -> array of file_ids
+    const budgetsByToken = new Map<string, string[]>();
+    for (const budget of allBudgets) {
+      const existing = budgetsByToken.get(budget.token_id) || [];
+      existing.push(budget.file_id);
+      budgetsByToken.set(budget.token_id, existing);
+    }
+
+    return tokens.map(token => ({
+      id: token.id,
+      name: token.name,
+      prefix: token.token_prefix,
+      createdAt: token.created_at,
+      lastUsedAt: token.last_used_at,
+      expiresAt: token.expires_at,
+      enabled: Boolean(token.enabled),
+      budgetIds: budgetsByToken.get(token.id) || [],
+    }));
+  },
+
+  /**
+   * Revoke (delete) an API token
+   * @param tokenId - The token ID
+   * @param userId - The user ID (for authorization)
+   * @returns True if the token was deleted
+   */
+  revokeToken(tokenId: string, userId: string): boolean {
+    const accountDb = getAccountDb() as WrappedDatabase;
+
+    // Verify ownership
+    const token = accountDb.first<{ id: string }>(
+      `SELECT id FROM api_tokens WHERE id = ? AND user_id = ?`,
+      [tokenId, userId],
+    );
+
+    if (!token) {
+      return false;
+    }
+
+    accountDb.transaction(() => {
+      // Delete budget scopes first (cascade should handle this, but be explicit)
+      accountDb.mutate(`DELETE FROM api_token_budgets WHERE token_id = ?`, [
+        tokenId,
+      ]);
+      accountDb.mutate(`DELETE FROM api_tokens WHERE id = ?`, [tokenId]);
+    });
+
+    return true;
+  },
+
+  /**
+   * Enable or disable an API token
+   * @param tokenId - The token ID
+   * @param userId - The user ID (for authorization)
+   * @param enabled - Whether to enable or disable
+   * @returns True if the token was updated
+   */
+  setTokenEnabled(tokenId: string, userId: string, enabled: boolean): boolean {
+    const accountDb = getAccountDb() as WrappedDatabase;
+
+    const result = accountDb.mutate(
+      `UPDATE api_tokens SET enabled = ? WHERE id = ? AND user_id = ?`,
+      [enabled ? 1 : 0, tokenId, userId],
+    );
+
+    return result.changes > 0;
+  },
+
+  /**
+   * Get the budget IDs that a token has access to
+   * @param tokenId - The token ID
+   * @returns Array of budget/file IDs (empty means all user's budgets)
+   */
+  getTokenBudgets(tokenId: string): string[] {
+    const accountDb = getAccountDb() as WrappedDatabase;
+
+    const budgetRows = accountDb.all<ApiTokenBudgetRow>(
+      `SELECT file_id FROM api_token_budgets WHERE token_id = ?`,
+      [tokenId],
+    );
+
+    return budgetRows.map(row => row.file_id);
+  },
+};

packages/sync-server/src/util/validate-user.test.js

@@ -0,0 +1,225 @@
+import crypto from 'node:crypto';
+
+import { getAccountDb } from '#account-db';
+import { apiTokenService } from '#services/api-token-service';
+
+import { validateSession } from './validate-user';
+
+// Helper functions
+const createUser = (userId, userName) => {
+  getAccountDb().mutate(
+    'INSERT INTO users (id, user_name, display_name, enabled, owner, role) VALUES (?, ?, ?, ?, ?, ?)',
+    [userId, userName, `${userName} display`, 1, 0, 'BASIC'],
+  );
+};
+
+const deleteUser = userId => {
+  getAccountDb().mutate(
+    'DELETE FROM api_token_budgets WHERE token_id IN (SELECT id FROM api_tokens WHERE user_id = ?)',
+    [userId],
+  );
+  getAccountDb().mutate('DELETE FROM api_tokens WHERE user_id = ?', [userId]);
+  getAccountDb().mutate('DELETE FROM users WHERE id = ?', [userId]);
+};
+
+const createSession = (userId, sessionToken) => {
+  getAccountDb().mutate(
+    'INSERT INTO sessions (token, user_id, expires_at) VALUES (?, ?, ?)',
+    [sessionToken, userId, -1],
+  );
+};
+
+const deleteSession = sessionToken => {
+  getAccountDb().mutate('DELETE FROM sessions WHERE token = ?', [sessionToken]);
+};
+
+// Mock response helper
+const createMockRes = () => {
+  const res = {
+    statusCode: null,
+    body: null,
+    status(code) {
+      this.statusCode = code;
+      return this;
+    },
+    send(body) {
+      this.body = body;
+      return this;
+    },
+  };
+  return res;
+};
+
+describe('validateSession', () => {
+  describe('API token validation', () => {
+    let userId, apiToken;
+
+    beforeEach(async () => {
+      userId = crypto.randomUUID();
+      createUser(userId, 'testuser');
+      const result = await apiTokenService.createToken(userId, 'Test Token');
+      apiToken = result.token;
+    });
+
+    afterEach(() => {
+      deleteUser(userId);
+    });
+
+    it('should validate API token from body', async () => {
+      const req = {
+        body: { token: apiToken },
+        headers: {},
+      };
+      const res = createMockRes();
+
+      const result = await validateSession(req, res);
+
+      expect(result).not.toBeNull();
+      expect(result.user_id).toBe(userId);
+      expect(result.auth_method).toBe('api_token');
+    });
+
+    it('should validate API token from x-actual-token header', async () => {
+      const req = {
+        body: {},
+        headers: { 'x-actual-token': apiToken },
+      };
+      const res = createMockRes();
+
+      const result = await validateSession(req, res);
+
+      expect(result).not.toBeNull();
+      expect(result.user_id).toBe(userId);
+    });
+
+    it('should reject invalid API token', async () => {
+      const req = {
+        body: { token: 'act_invalidtoken12345678901234' },
+        headers: {},
+      };
+      const res = createMockRes();
+
+      const result = await validateSession(req, res);
+
+      expect(result).toBeNull();
+      expect(res.statusCode).toBe(401);
+      expect(res.body.details).toBe('invalid-api-token');
+    });
+
+    it('should prefer x-actual-token header over unsupported Authorization header', async () => {
+      // When x-actual-token and Authorization are both present,
+      // x-actual-token should be used
+      const req = {
+        body: {},
+        headers: {
+          'x-actual-token': apiToken,
+          authorization: 'Bearer invalid-token',
+        },
+      };
+      const res = createMockRes();
+
+      const result = await validateSession(req, res);
+
+      expect(result).not.toBeNull();
+      expect(result.user_id).toBe(userId);
+    });
+
+    it('should prefer body token over unsupported Authorization header', async () => {
+      // When body.token and Authorization are both present,
+      // body.token should be used
+      const req = {
+        body: { token: apiToken },
+        headers: {
+          authorization: 'Bearer invalid-token',
+        },
+      };
+      const res = createMockRes();
+
+      const result = await validateSession(req, res);
+
+      expect(result).not.toBeNull();
+      expect(result.user_id).toBe(userId);
+    });
+  });
+
+  describe('session token validation', () => {
+    let userId, sessionToken;
+
+    beforeEach(() => {
+      userId = crypto.randomUUID();
+      sessionToken = `session-${crypto.randomUUID()}`;
+      createUser(userId, 'testuser');
+      createSession(userId, sessionToken);
+    });
+
+    afterEach(() => {
+      deleteSession(sessionToken);
+      deleteUser(userId);
+    });
+
+    it('should validate session token from body', async () => {
+      const req = {
+        body: { token: sessionToken },
+        headers: {},
+      };
+      const res = createMockRes();
+
+      const result = await validateSession(req, res);
+
+      expect(result).not.toBeNull();
+      expect(result.user_id).toBe(userId);
+    });
+
+    it('should validate session token from x-actual-token header', async () => {
+      const req = {
+        body: {},
+        headers: { 'x-actual-token': sessionToken },
+      };
+      const res = createMockRes();
+
+      const result = await validateSession(req, res);
+
+      expect(result).not.toBeNull();
+      expect(result.user_id).toBe(userId);
+    });
+
+    it('should reject invalid session token', async () => {
+      const req = {
+        body: { token: 'invalid-token' },
+        headers: {},
+      };
+      const res = createMockRes();
+
+      const result = await validateSession(req, res);
+
+      expect(result).toBeNull();
+      expect(res.statusCode).toBe(401);
+      expect(res.body.details).toBe('token-not-found');
+    });
+
+    it('should reject expired session token', async () => {
+      // Create an expired session
+      const expiredToken = `expired-${crypto.randomUUID()}`;
+      getAccountDb().mutate(
+        'INSERT INTO sessions (token, user_id, expires_at) VALUES (?, ?, ?)',
+        [expiredToken, userId, 1], // Expired (timestamp 1 = 1970)
+      );
+
+      const req = {
+        body: { token: expiredToken },
+        headers: {},
+      };
+      const res = createMockRes();
+
+      const result = await validateSession(req, res);
+
+      expect(result).toBeNull();
+      expect(res.statusCode).toBe(401);
+      expect(res.body.reason).toBe('token-expired');
+
+      getAccountDb().mutate('DELETE FROM sessions WHERE token = ?', [
+        expiredToken,
+      ]);
+    });
+  });
+});

packages/sync-server/src/util/validate-user.ts

@@ -3,17 +3,24 @@ import ipaddr from 'ipaddr.js';
 
 import { getSession } from '#account-db';
 import { config } from '#load-config';
+import { apiTokenService } from '#services/api-token-service';
 
 export const TOKEN_EXPIRATION_NEVER = -1;
 const MS_PER_SECOND = 1000;
+const API_TOKEN_PREFIX = 'act_';
 
-export function validateSession(req: Request, res: Response) {
+export async function validateSession(req: Request, res: Response) {
   let { token } = req.body || {};
 
   if (!token) {
     token = req.headers['x-actual-token'];
   }
 
+  // Check if this is an API token
+  if (token && token.startsWith(API_TOKEN_PREFIX)) {
+    return await validateApiToken(token, res);
+  }
+
   const session = getSession(token);
 
   if (!session) {
@@ -41,6 +48,31 @@ export function validateSession(req: Request, res: Response) {
   return session;
 }
 
+/**
+ * Validate an API token and return session-like object
+ */
+async function validateApiToken(token: string, res: Response) {
+  const result = await apiTokenService.validateToken(token);
+
+  if (!result) {
+    res.status(401);
+    res.send({
+      status: 'error',
+      reason: 'unauthorized',
+      details: 'invalid-api-token',
+    });
+    return null;
+  }
+
+  // Return a session-like object for compatibility
+  return {
+    user_id: result.userId,
+    token_id: result.tokenId,
+    budget_ids: result.budgetIds,
+    auth_method: 'api_token',
+  };
+}
+
 export function validateAuthHeader(req: Request) {
   // fallback to trustedProxies when trustedAuthProxies not set
   const trustedAuthProxies: string[] =