# Skill: Financial Reporting (IFRS)

**Primary Agent:** Friedrich (Finance)
**Secondary:** Alfred (COO oversight)
**Standard:** IFRS (International Financial Reporting Standards)
**Data Source:** `financial_transactions` table in Supabase PostgreSQL

---

## Chart of Accounts

| Code | Category | Description |
|------|----------|-------------|
| 1000 | Assets | Cash, receivables, prepaid services |
| 2000 | Liabilities | Accounts payable, accrued expenses |
| 3000 | Equity | Owner's equity, retained earnings |
| 4000 | Revenue | Client invoices, subscription revenue |
| 5000 | COGS | Direct service delivery costs |
| 6000 | OpEx | SaaS tools, AI credits, infrastructure, marketing |

## Database Schema

Table: `financial_transactions`

```sql
CREATE TABLE financial_transactions (
  id            serial PRIMARY KEY,
  date          date NOT NULL,
  type          text NOT NULL CHECK (type IN ('payable', 'receivable')),
  category      text NOT NULL,
  account_code  text,
  vendor_client text NOT NULL,
  description   text,
  amount        numeric(12,2) NOT NULL,
  currency      text NOT NULL DEFAULT 'USD',
  status        text NOT NULL DEFAULT 'pending' CHECK (status IN ('pending', 'paid', 'overdue', 'cancelled')),
  invoice_ref   text,
  due_date      date,
  paid_date     date,
  notes         text,
  created_at    timestamptz DEFAULT now(),
  updated_at    timestamptz DEFAULT now()
);
```

## Accounts Payable (Dependencies)

Track ALL recurring costs as `type = 'payable'`:

| Vendor | Category | Account Code | Frequency | Notes |
|--------|----------|--------------|-----------|-------|
| OpenRouter | AI Credits | 6000-AI | Monthly | Track via `/api/v1/credits` |
| Supabase | Database | 6000-INFRA | Monthly | Free tier (monitor usage) |
| Vercel | Hosting | 6000-INFRA | Monthly | Free tier (monitor builds) |
| Cloudflare | DNS/CDN | 6000-INFRA | Monthly | Free tier |
| Crustdata | Enrichment | 6000-DATA | Per-use | $0.025/credit, 1000 free |
| Google Places | API | 6000-DATA | Per-use | Track via billing console |
| Stripe | Payment Processing | 5000-PAY | Per-txn | 2.9% + $0.30 |
| PayPal | Payment Processing | 5000-PAY | Per-txn | Variable rates |
| Telnyx | Telephony | 6000-COMM | Monthly | Per-minute/message |
| Apollo | Enrichment | 6000-DATA | Monthly | Free tier |
| GitHub | Code Hosting | 6000-INFRA | Monthly | Free tier |

## Accounts Receivable (Clients/Assets)

Track ALL revenue as `type = 'receivable'`:

| Source | Category | Account Code | Model |
|--------|----------|--------------|-------|
| Lead packages | Revenue | 4000-LEADS | Per-lead or subscription |
| Setup fees | Revenue | 4000-SETUP | One-time |
| Consulting | Revenue | 4000-CONSULT | Hourly |

## SQL Queries

### Insert a transaction

```sql
INSERT INTO financial_transactions (date, type, category, account_code, vendor_client, description, amount, currency, status, invoice_ref, due_date)
VALUES ('2026-02-24', 'payable', 'AI Credits', '6000-AI', 'OpenRouter', 'February AI usage', 12.50, 'USD', 'pending', NULL, '2026-03-15');
```

### Monthly P&L

```sql
SELECT
  type,
  category,
  SUM(amount) AS total
FROM financial_transactions
WHERE date >= date_trunc('month', CURRENT_DATE)
  AND status <> 'cancelled'
GROUP BY type, category
ORDER BY type, category;
```

### Cash flow summary

```sql
SELECT
  COALESCE(SUM(CASE WHEN type = 'receivable' AND status = 'paid' THEN amount ELSE 0 END), 0) AS cash_in,
  COALESCE(SUM(CASE WHEN type = 'payable' AND status = 'paid' THEN amount ELSE 0 END), 0) AS cash_out,
  COALESCE(SUM(CASE WHEN type = 'receivable' AND status = 'paid' THEN amount ELSE 0 END), 0)
  - COALESCE(SUM(CASE WHEN type = 'payable' AND status = 'paid' THEN amount ELSE 0 END), 0) AS net_cash
FROM financial_transactions
WHERE date >= date_trunc('month', CURRENT_DATE);
```

### Overdue payables

```sql
SELECT vendor_client, amount, due_date, description
FROM financial_transactions
WHERE type = 'payable' AND status = 'pending' AND due_date < CURRENT_DATE
ORDER BY due_date;
```

### Outstanding receivables

```sql
SELECT vendor_client, amount, due_date, invoice_ref
FROM financial_transactions
WHERE type = 'receivable' AND status IN ('pending', 'overdue')
ORDER BY due_date;
```

## Monthly Close Checklist

1. **Reconcile API costs** — Pull actual usage from OpenRouter, Crustdata, Google Places APIs
2. **Record all payables** — Ensure every dependency cost is logged with correct amount
3. **Record all receivables** — Log client invoices with due dates
4. **Update statuses** — Mark paid items, flag overdue items
5. **Generate P&L** — Run monthly P&L query, compare to prior month
6. **Generate cash flow** — Net cash in vs out
7. **Report to Alfred** — Summary in LeadsPanther Team Telegram group
8. **Flag anomalies** — Any cost increase >20% month-over-month gets escalated to Clayton

## Cost Monitoring Commands

```powershell
# Check OpenRouter credits (via curl)
$headers = @{ Authorization = "Bearer $env:OPENROUTER_API_KEY" }
Invoke-RestMethod -Uri "https://openrouter.ai/api/v1/credits" -Headers $headers

# Check Crustdata credit balance (track usage from enrichment logs)
# Credits consumed are logged by enrich:crustdata-company and enrich:crustdata-person

# Query financial summary from DB
$LE = "C:/Users/Administrator/.openclaw/workspace/leadspanther-lead-engine"
npm --prefix $LE run query:pipeline -- --mode financial
```

## IFRS Compliance Notes

- Revenue recognized when service delivered (lead package sent), not when invoiced
- All amounts in USD unless explicitly stated
- Transactions must have accurate dates — no backdating
- Cancelled transactions retained with `status = 'cancelled'` for audit trail
- All inter-company transactions (if applicable) at arm's length