Skip to content

A local-first personal finance app

11.1 KB
Raw Blame
import * as api from '@actual-app/api';
import type { Command } from 'commander';

import { withConnection } from '#connection';
import { readJsonInput } from '#input';
import { printOutput } from '#output';
import { isRecord, parseIntFlag } from '#utils';

/**
 * Parse order-by strings like "date:desc,amount:asc,id" into
 * AQL orderBy format: [{ date: 'desc' }, { amount: 'asc' }, 'id']
 */
export function parseOrderBy(
  input: string,
): Array<string | Record<string, string>> {
  return input.split(',').map(part => {
    const trimmed = part.trim();
    if (!trimmed) {
      throw new Error('--order-by contains an empty field');
    }
    const colonIndex = trimmed.indexOf(':');
    if (colonIndex === -1) {
      return trimmed;
    }
    const field = trimmed.slice(0, colonIndex).trim();
    if (!field) {
      throw new Error(
        `Invalid order field in "${trimmed}". Field name cannot be empty.`,
      );
    }
    const direction = trimmed.slice(colonIndex + 1);
    if (direction !== 'asc' && direction !== 'desc') {
      throw new Error(
        `Invalid order direction "${direction}" for field "${field}". Expected "asc" or "desc".`,
      );
    }
    return { [field]: direction };
  });
}

// TODO: Import schema from API once it exposes table/field metadata
const TABLE_SCHEMA: Record<
  string,
  Record<string, { type: string; ref?: string }>
> = {
  transactions: {
    id: { type: 'id' },
    account: { type: 'id', ref: 'accounts' },
    date: { type: 'date' },
    amount: { type: 'integer' },
    payee: { type: 'id', ref: 'payees' },
    category: { type: 'id', ref: 'categories' },
    notes: { type: 'string' },
    imported_id: { type: 'string' },
    transfer_id: { type: 'id' },
    cleared: { type: 'boolean' },
    reconciled: { type: 'boolean' },
    starting_balance_flag: { type: 'boolean' },
    imported_payee: { type: 'string' },
    is_parent: { type: 'boolean' },
    is_child: { type: 'boolean' },
    parent_id: { type: 'id' },
    sort_order: { type: 'float' },
    schedule: { type: 'id', ref: 'schedules' },
    'account.name': { type: 'string', ref: 'accounts' },
    'payee.name': { type: 'string', ref: 'payees' },
    'category.name': { type: 'string', ref: 'categories' },
    'category.group.name': { type: 'string', ref: 'category_groups' },
  },
  accounts: {
    id: { type: 'id' },
    name: { type: 'string' },
    offbudget: { type: 'boolean' },
    closed: { type: 'boolean' },
    sort_order: { type: 'float' },
  },
  categories: {
    id: { type: 'id' },
    name: { type: 'string' },
    is_income: { type: 'boolean' },
    group_id: { type: 'id', ref: 'category_groups' },
    sort_order: { type: 'float' },
    hidden: { type: 'boolean' },
    'group.name': { type: 'string', ref: 'category_groups' },
  },
  payees: {
    id: { type: 'id' },
    name: { type: 'string' },
    transfer_acct: { type: 'id', ref: 'accounts' },
  },
  rules: {
    id: { type: 'id' },
    stage: { type: 'string' },
    conditions_op: { type: 'string' },
    conditions: { type: 'json' },
    actions: { type: 'json' },
  },
  schedules: {
    id: { type: 'id' },
    name: { type: 'string' },
    rule: { type: 'id', ref: 'rules' },
    next_date: { type: 'date' },
    completed: { type: 'boolean' },
  },
};

const AVAILABLE_TABLES = Object.keys(TABLE_SCHEMA).join(', ');

const LAST_DEFAULT_SELECT = [
  'date',
  'account.name',
  'payee.name',
  'category.name',
  'amount',
  'notes',
];

function buildQueryFromFile(
  parsed: Record<string, unknown>,
  fallbackTable: string | undefined,
) {
  const table = typeof parsed.table === 'string' ? parsed.table : fallbackTable;
  if (!table) {
    throw new Error(
      '--table is required when the input file lacks a "table" field',
    );
  }
  let queryObj = api.q(table);
  if (Array.isArray(parsed.select)) queryObj = queryObj.select(parsed.select);
  if (isRecord(parsed.filter)) queryObj = queryObj.filter(parsed.filter);
  if (Array.isArray(parsed.orderBy)) {
    queryObj = queryObj.orderBy(parsed.orderBy);
  }
  if (typeof parsed.limit === 'number') queryObj = queryObj.limit(parsed.limit);
  if (typeof parsed.offset === 'number') {
    queryObj = queryObj.offset(parsed.offset);
  }
  if (Array.isArray(parsed.groupBy)) {
    queryObj = queryObj.groupBy(parsed.groupBy);
  }
  return queryObj;
}

function buildQueryFromFlags(cmdOpts: Record<string, string | undefined>) {
  const last = cmdOpts.last ? parseIntFlag(cmdOpts.last, '--last') : undefined;

  if (last !== undefined) {
    if (cmdOpts.table && cmdOpts.table !== 'transactions') {
      throw new Error(
        '--last implies --table transactions. Cannot use with --table ' +
          cmdOpts.table,
      );
    }
    if (cmdOpts.limit) {
      throw new Error('--last and --limit are mutually exclusive');
    }
  }

  const table =
    cmdOpts.table ?? (last !== undefined ? 'transactions' : undefined);
  if (!table) {
    throw new Error('--table is required (or use --file or --last)');
  }

  if (!(table in TABLE_SCHEMA)) {
    throw new Error(
      `Unknown table "${table}". Available tables: ${AVAILABLE_TABLES}`,
    );
  }

  if (cmdOpts.where && cmdOpts.filter) {
    throw new Error('--where and --filter are mutually exclusive');
  }

  if (cmdOpts.count && cmdOpts.select) {
    throw new Error('--count and --select are mutually exclusive');
  }

  let queryObj = api.q(table);

  if (cmdOpts.count) {
    queryObj = queryObj.calculate({ $count: '*' });
  } else if (cmdOpts.select) {
    queryObj = queryObj.select(cmdOpts.select.split(','));
  } else if (last !== undefined) {
    queryObj = queryObj.select(LAST_DEFAULT_SELECT);
  }

  const filterStr = cmdOpts.filter ?? cmdOpts.where;
  if (filterStr) {
    queryObj = queryObj.filter(JSON.parse(filterStr));
  }

  const orderByStr =
    cmdOpts.orderBy ??
    (last !== undefined && !cmdOpts.count ? 'date:desc' : undefined);
  if (orderByStr) {
    queryObj = queryObj.orderBy(parseOrderBy(orderByStr));
  }

  const limitVal =
    last ??
    (cmdOpts.limit ? parseIntFlag(cmdOpts.limit, '--limit') : undefined);
  if (limitVal !== undefined) {
    queryObj = queryObj.limit(limitVal);
  }

  if (cmdOpts.offset) {
    queryObj = queryObj.offset(parseIntFlag(cmdOpts.offset, '--offset'));
  }

  if (cmdOpts.groupBy) {
    queryObj = queryObj.groupBy(cmdOpts.groupBy.split(','));
  }

  return queryObj;
}

const RUN_EXAMPLES = `
Examples:
  # Show last 5 transactions (shortcut)
  actual query run --last 5

  # Transactions ordered by date descending
  actual query run --table transactions --select "date,amount,payee.name" --order-by "date:desc" --limit 10

  # Filter with JSON (negative amounts = expenses)
  actual query run --table transactions --filter '{"amount":{"$lt":0}}' --limit 5

  # Count transactions
  actual query run --table transactions --count

  # Group by category (use --file for aggregate expressions)
  echo '{"table":"transactions","groupBy":["category.name"],"select":["category.name",{"amount":{"$sum":"$amount"}}]}' | actual query run --file -

  # Pagination
  actual query run --table transactions --order-by "date:desc" --limit 10 --offset 20

  # Use --where (alias for --filter)
  actual query run --table transactions --where '{"payee.name":"Grocery Store"}' --limit 5

  # Read query from a JSON file
  actual query run --file query.json

  # Pipe query from stdin
  echo '{"table":"transactions","limit":5}' | actual query run --file -

Available tables: ${AVAILABLE_TABLES}
Use "actual query tables" and "actual query fields <table>" for schema info.

Common filter operators: $eq, $ne, $lt, $lte, $gt, $gte, $like, $and, $or
See ActualQL docs for full reference: https://actualbudget.org/docs/api/actual-ql/

Tips:
  - Amounts are stored as integer cents (e.g. 166500 = 1665.00).
    Table and CSV output auto-formats these as decimals; JSON keeps raw cents.
  - Filter "is_parent": false to avoid double-counting split transactions.
  - Fetch all data in a single query with a date range instead of running
    one query per month — rapid sequential requests may cause auth failures.
  - date.month, date.year etc. are not supported as fields in AQL.
    To group by month, fetch raw transactions with a date range filter
    and aggregate locally (e.g. in a script).`;

export function registerQueryCommand(program: Command) {
  const query = program
    .command('query')
    .description('Run AQL (Actual Query Language) queries');

  query
    .command('run')
    .description('Execute an AQL query')
    .option(
      '--table <table>',
      'Table to query (use "actual query tables" to list available tables)',
    )
    .option('--select <fields>', 'Comma-separated fields to select')
    .option('--filter <json>', 'Filter as JSON (e.g. \'{"amount":{"$lt":0}}\')')
    .option(
      '--where <json>',
      'Alias for --filter (cannot be used together with --filter)',
    )
    .option(
      '--order-by <fields>',
      'Fields with optional direction: field1:desc,field2 (default: asc)',
    )
    .option('--limit <n>', 'Limit number of results')
    .option('--offset <n>', 'Skip first N results (for pagination)')
    .option(
      '--last <n>',
      'Show last N transactions (implies --table transactions, --order-by date:desc)',
    )
    .option('--count', 'Count matching rows instead of returning them')
    .option(
      '--group-by <fields>',
      'Comma-separated fields to group by (use with aggregate selects)',
    )
    .option(
      '--file <path>',
      'Read full query object from JSON file (use - for stdin)',
    )
    .addHelpText('after', RUN_EXAMPLES)
    .action(async cmdOpts => {
      const opts = program.opts();
      await withConnection(
        opts,
        async () => {
          const parsed = cmdOpts.file ? readJsonInput(cmdOpts) : undefined;
          if (parsed !== undefined && !isRecord(parsed)) {
            throw new Error('Query file must contain a JSON object');
          }
          const queryObj = parsed
            ? buildQueryFromFile(parsed, cmdOpts.table)
            : buildQueryFromFlags(cmdOpts);

          const result = await api.aqlQuery(queryObj);

          if (!isRecord(result) || !('data' in result)) {
            throw new Error('Query result missing data');
          }

          if (cmdOpts.count) {
            printOutput({ count: result.data }, opts.format);
          } else {
            printOutput(result.data, opts.format);
          }
        },
        { mutates: false },
      );
    });

  query
    .command('tables')
    .description('List available tables for querying')
    .action(() => {
      const opts = program.opts();
      const tables = Object.keys(TABLE_SCHEMA).map(name => ({ name }));
      printOutput(tables, opts.format);
    });

  query
    .command('fields <table>')
    .description('List fields for a given table')
    .action((table: string) => {
      const opts = program.opts();
      const schema = TABLE_SCHEMA[table];
      if (!schema) {
        throw new Error(
          `Unknown table "${table}". Available tables: ${Object.keys(TABLE_SCHEMA).join(', ')}`,
        );
      }
      const fields = Object.entries(schema).map(([name, info]) => ({
        name,
        type: info.type,
        ...(info.ref ? { ref: info.ref } : {}),
      }));
      printOutput(fields, opts.format);
    });
}