読み込み中
豆知識
書類と銀行取引をどうつなぐかのバックエンド仕様です。月次の操作（ウィザード → 合算支払 → 役員報酬は別チェーン → 実振込のあと銀行）は 月次精算の書類の作り方とつなぎ方 を先に読んでください。経費の合算支払は expense_to_payment だけです。給与明細を経費チェーンに挟まないでください。
This document outlines the backend API requirements to support comprehensive document-transaction linkage for Japanese small business accounting.
┌─────────────────────────────────────────────────────────────────────────────┐
│ DOCUMENT-TRANSACTION RELATIONSHIPS │
├─────────────────────────────────────────────────────────────────────────────┤
│ │
│ BANK TRANSACTION DOCUMENTS │
│ ┌──────────────┐ │
│ │ 銀行取引明細 │───────┬───────────► 領収書 (Receipt) │
│ │ │ │ ├── 会社経費 │
│ │ - 入金/出金 │ │ └── amount, date, vendor │
│ │ - 日付 │ │ │
│ │ - 金額 │ ├───────────► 請求書 (Invoice) │
│ │ - 摘要 │ │ ├── 売上請求 (revenue) │
│ └──────────────┘ │ └── 仕入請求 (expense) │
│ │ │
│ ├───────────► 経費申請書 (Expense Claim) │
│ │ ├── has_many: 領収書 │
│ │ ├── belongs_to: 従業員 │
│ │ └── 立替精算の振込 │
│ │ │
│ ├───────────► 給与明細書 (Payslip) │
│ │ ├── belongs_to: 従業員 │
│ │ └── linked via 支払明細書 │
│ │ │
│ ├───────────► 支払明細書 (Payment Statement) │
│ │ ├── has_many: 給与明細書 │
│ │ └── batch payment record │
│ │ │
│ ├───────────► クレジットカード明細 │
│ │ ├── has_many: 個別利用明細 │
│ │ └── 引落時に消込 │
│ │ │
│ └───────────► 税金関連書類 │
│ ├── 納付書 │
│ └── 領収証書 │
│ │
└─────────────────────────────────────────────────────────────────────────────┘
/api/reconciliation/linkCreate a link between a bank transaction and one or more documents.
Request Body:
interface LinkDocumentRequest {
bankTransactionId: string
links: Array<{
documentId: string
documentType: DocumentType
linkageType: LinkageType
amount?: number // Partial linking support
note?: string
}>
createJournalEntry: boolean
journalOptions?: {
useAiSuggestion: boolean
accountOverrides?: AccountOverride[]
}
}
type LinkageType =
| "direct_expense" // 会社経費
| "reimbursement" // 立替精算
| "payroll" // 給与支払
| "cc_payment" // カード引落
| "invoice_payment" // 請求書支払
| "tax_payment" // 税金支払
| "revenue" // 売上入金
| "loan_payment" // ローン返済
| "transfer" // 振替
type DocumentType =
| "receipt" // 領収書
| "invoice" // 請求書
| "claims" // 経費申請書
| "payroll" // 給与明細書
| "payment_statement" // 支払明細書
| "credit_card" // クレジットカード明細
| "tax" // 税金関連
| "quote" // 見積書
| "purchase_order" // 発注書
| "delivery_note" // 納品書
Response:
interface LinkDocumentResponse {
success: boolean
linkId: string
journalEntryId?: string
journalEntry?: {
date: string
description: string
entries: JournalEntryLine[]
}
warnings?: string[]
}
/api/documents/search-for-matchingSearch for documents that could match a bank transaction.
Query Parameters:
interface DocumentSearchParams {
amount: number // Transaction amount
amountTolerance?: number // Default: 0.05 (5%)
date: string // Transaction date YYYY-MM-DD
dateRangeDays?: number // Default: 7
description?: string // For vendor name matching
documentTypes?: string[] // Filter by document types
excludeLinked?: boolean // Exclude already linked docs
limit?: number // Default: 20
}
Response:
interface DocumentSearchResponse {
documents: Array<{
id: string
documentName: string
documentType: DocumentType
amount?: number
date?: string
vendorName?: string
employeeName?: string
status: string
matchScore: number // 0-100
matchReason: string
existingLinks?: Array<{
bankTransactionId: string
linkageType: LinkageType
}>
}>
totalCount: number
}
/api/expense-claims/:id/receiptsGet all receipts linked to an expense claim.
Response:
interface ExpenseClaimReceiptsResponse {
expenseClaim: {
id: string
employeeId: string
employeeName: string
totalAmount: number
status: string
submittedAt: string
approvedAt?: string
paidAt?: string
bankTransactionId?: string // Settlement transaction
}
receipts: Array<{
id: string
documentName: string
amount: number
date: string
vendorName?: string
category?: string
taxAmount?: number
imageUrl?: string
}>
summary: {
totalReceipts: number
totalAmount: number
byCategory: Record<string, number>
}
}
/api/payroll/payment-statements/:idGet payment statement with linked payslips.
Response:
interface PaymentStatementResponse {
paymentStatement: {
id: string
paymentDate: string
totalAmount: number
employeeCount: number
status: string
bankTransactionId?: string
}
payslips: Array<{
id: string
employeeId: string
employeeName: string
grossAmount: number
netAmount: number
deductions: Array<{
type: string
amount: number
}>
period: {
year: number
month: number
}
}>
}
/api/payroll/payment-statements/:id/link-transactionLink a payment statement (and all its payslips) to a bank transaction.
/api/credit-cards/:cardId/statementsGet credit card statements for reconciliation.
Response:
interface CreditCardStatementsResponse {
card: {
id: string
name: string
lastFourDigits: string
billingCycleDay: number
}
statements: Array<{
id: string
statementDate: string
dueDate: string
totalAmount: number
status: "pending" | "paid"
bankTransactionId?: string // Payment transaction
transactions: Array<{
id: string
date: string
description: string
amount: number
category?: string
receiptId?: string // Linked receipt
}>
}>
}
/api/credit-cards/statements/:id/link-paymentLink credit card statement to bank transaction (引落).
/api/reconciliation/bulk-linkLink multiple documents to a single bank transaction (e.g., multiple receipts to one payment).
Request:
interface BulkLinkRequest {
bankTransactionId: string
links: Array<{
documentId: string
documentType: DocumentType
amount?: number // For partial allocation
}>
linkageType: LinkageType
createJournalEntry: boolean
}
/api/ai/analyze-transactionAI analysis of bank transaction to suggest linkage type and matching documents.
Request:
interface AnalyzeTransactionRequest {
description: string
amount: number
isDeposit: boolean
date: string
}
Response:
interface AnalyzeTransactionResponse {
suggestedLinkageType: LinkageType
confidence: number
reasoning: string
suggestedDocuments: Array<{
documentId: string
documentType: DocumentType
matchScore: number
matchReason: string
}>
alternativeLinkageTypes: Array<{
type: LinkageType
confidence: number
}>
}
-- Document-Transaction Links (supports many-to-many)
CREATE TABLE document_transaction_links (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
user_id UUID NOT NULL REFERENCES auth.users(id),
company_id UUID REFERENCES companies(id),
-- Transaction side
bank_transaction_id UUID NOT NULL REFERENCES bank_statements(id),
-- Document side
document_id UUID NOT NULL,
document_type VARCHAR(50) NOT NULL, -- receipt, claims, invoice, etc.
-- Link metadata
linkage_type VARCHAR(30) NOT NULL, -- direct_expense, reimbursement, etc.
allocated_amount DECIMAL(15,2), -- For partial linking
-- AI/matching info
match_method VARCHAR(30) NOT NULL, -- manual, ai_suggested, rule_based
match_confidence DECIMAL(3,2),
ai_reasoning TEXT,
-- Journal entry reference
journal_entry_id UUID REFERENCES journal_entries(id),
-- Audit
created_by UUID REFERENCES auth.users(id),
created_at TIMESTAMPTZ DEFAULT NOW(),
updated_at TIMESTAMPTZ DEFAULT NOW(),
UNIQUE(bank_transaction_id, document_id)
);
-- Expense claim receipts (many-to-many)
CREATE TABLE expense_claim_receipts (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
expense_claim_id UUID NOT NULL REFERENCES documents(id),
receipt_id UUID NOT NULL REFERENCES documents(id),
-- Allocation
allocated_amount DECIMAL(15,2),
category VARCHAR(50),
created_at TIMESTAMPTZ DEFAULT NOW(),
UNIQUE(expense_claim_id, receipt_id)
);
-- Payroll batch payments
CREATE TABLE payment_statements (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
user_id UUID NOT NULL REFERENCES auth.users(id),
company_id UUID REFERENCES companies(id),
payment_date DATE NOT NULL,
total_amount DECIMAL(15,2) NOT NULL,
employee_count INTEGER NOT NULL,
-- Link to bank transaction
bank_transaction_id UUID REFERENCES bank_statements(id),
status VARCHAR(20) DEFAULT 'pending',
created_at TIMESTAMPTZ DEFAULT NOW(),
updated_at TIMESTAMPTZ DEFAULT NOW()
);
-- Payslips linked to payment statements
CREATE TABLE payslips (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
payment_statement_id UUID REFERENCES payment_statements(id),
employee_id UUID NOT NULL,
period_year INTEGER NOT NULL,
period_month INTEGER NOT NULL,
gross_amount DECIMAL(15,2) NOT NULL,
net_amount DECIMAL(15,2) NOT NULL,
-- Deductions stored as JSONB
deductions JSONB DEFAULT '[]',
created_at TIMESTAMPTZ DEFAULT NOW()
);
-- Credit card statements
CREATE TABLE credit_card_statements (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
credit_card_id UUID NOT NULL,
user_id UUID NOT NULL REFERENCES auth.users(id),
statement_date DATE NOT NULL,
due_date DATE NOT NULL,
total_amount DECIMAL(15,2) NOT NULL,
-- Link to bank transaction (引落)
bank_transaction_id UUID REFERENCES bank_statements(id),
status VARCHAR(20) DEFAULT 'pending',
created_at TIMESTAMPTZ DEFAULT NOW()
);
-- Credit card transactions
CREATE TABLE credit_card_transactions (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
statement_id UUID NOT NULL REFERENCES credit_card_statements(id),
transaction_date DATE NOT NULL,
description VARCHAR(500) NOT NULL,
amount DECIMAL(15,2) NOT NULL,
category VARCHAR(50),
-- Link to receipt
receipt_id UUID REFERENCES documents(id),
created_at TIMESTAMPTZ DEFAULT NOW()
);
-- Indexes
CREATE INDEX idx_doc_tx_links_bank_tx ON document_transaction_links(bank_transaction_id);
CREATE INDEX idx_doc_tx_links_document ON document_transaction_links(document_id, document_type);
CREATE INDEX idx_doc_tx_links_user ON document_transaction_links(user_id);
CREATE INDEX idx_expense_claim_receipts_claim ON expense_claim_receipts(expense_claim_id);
CREATE INDEX idx_payslips_statement ON payslips(payment_statement_id);
CREATE INDEX idx_cc_transactions_statement ON credit_card_transactions(statement_id);
User selects bank transaction: "Visaデビット利用 Amazon -¥12,857"
↓
AI suggests: "direct_expense" (confidence: 85%)
↓
System shows matching receipts (by amount ±5%, date ±7 days)
↓
User selects or uploads receipt
↓
POST /api/reconciliation/link
{
bankTransactionId: "tx-123",
links: [{ documentId: "receipt-456", documentType: "receipt" }],
linkageType: "direct_expense",
createJournalEntry: true
}
↓
System creates journal entry:
借方: 消耗品費 12,857
貸方: 普通預金 12,857
User selects bank transaction: "振込 デモユーザー（経費精算）-¥24,911"
↓
AI suggests: "reimbursement" (confidence: 95%)
↓
System shows matching expense claims
↓
User selects expense claim (which has 5 receipts linked)
↓
POST /api/reconciliation/link
{
bankTransactionId: "tx-789",
links: [{ documentId: "claim-101", documentType: "claims" }],
linkageType: "reimbursement",
createJournalEntry: true
}
↓
System creates journal entry:
借方: 立替金 24,911
貸方: 普通預金 24,911
(立替金 was created when claim was approved)
User selects bank transaction: "振込 サクラモトケンジ -¥168,491"
↓
AI suggests: "payroll" (confidence: 90%)
↓
System shows payment statements for the period
↓
User selects payment statement (contains 1 payslip)
↓
POST /api/payroll/payment-statements/:id/link-transaction
{
bankTransactionId: "tx-555"
}
↓
System creates journal entry (or links to existing payroll entry)
New Endpoints:
POST /api/reconciliation/link - Flexible document linkingGET /api/documents/search-for-matching - Smart document searchGET /api/expense-claims/:id/receipts - Expense claim detailsPOST /api/reconciliation/bulk-link - Multi-document linkingPOST /api/ai/analyze-transaction - AI transaction analysisDatabase Changes:
document_transaction_links tableexpense_claim_receipts tablepayment_statements and payslips tablescredit_card_statements and credit_card_transactions tablesBusiness Logic:
reconciliation_links support many-to-many document linking?Partial. Current structure only links to expense_reports, not generic documents.
bank_transaction_id, journal_entry_id, expense_report_iddocument_id (generic), document_type, linkage_typeDecision: Enhance document_transaction_links table for flexible linking.
No. Current expense_reports links to expense_report_items → journal_entries, but no direct receipt attachment.
Action Required: Create expense_claim_receipts table.
pay_slips exists with employee info, amounts, deductions, links to journal_entries and documents.
BUT no payment_statements batch concept - each payslip is individual.
Action Required: Add batch payment support for multi-employee salary transfers.
Separate but limited. credit_card_transactions exists with:
credit_card_statements (monthly billing cycle)Yes. Already partially supported via bank_amount_applied, journal_amount_applied, adjustment_amount.
Enhanced allocated_amount per document is cleaner.
| Scenario | Description | Current Support |
|---|---|---|
| Split payments (分割払い) | One invoice paid across multiple transactions | ❌ Missing |
| Advance payment (前払金) | Pay vendor before receiving goods | ❌ Need linkage_type |
| Deposit received (前受金) | Customer pays before delivery | ❌ Need linkage_type |
| Foreign currency | USD invoice, JPY payment with exchange diff | Partial |
| Withholding tax (源泉徴収) | Link payment to 源泉徴収票 | ❌ Missing |
| Fixed asset purchase | Link invoice → fixed_asset register | ❌ No link table |
| Loan payment (借入金) | Split principal vs interest | ❌ Missing |
| Refunds/returns (返金) | Negative link or reversal | ❌ Missing |
| Recurring/subscription | Auto-link monthly same vendor | ❌ No rule table |
| Year-end adjustment (年末調整) | Link adjustments to documents | ❌ Missing |
| Consumption tax filing (消費税申告) | Link to tax return documents | ❌ Missing |
| Inter-company transfer | Between user's companies | ❌ No company_id |
type LinkageType =
| "direct_expense" // 会社経費
| "reimbursement" // 立替精算
| "payroll" // 給与支払
| "cc_payment" // カード引落
| "invoice_payment" // 請求書支払（買掛）
| "invoice_receipt" // 請求書入金（売掛） ← NEW
| "tax_payment" // 税金支払
| "revenue" // 売上入金
| "loan_payment" // ローン返済
| "loan_receipt" // ローン借入 ← NEW
| "transfer" // 振替
| "advance_payment" // 前払金 ← NEW
| "deposit_received" // 前受金 ← NEW
| "refund_issued" // 返金（支払）← NEW
| "refund_received" // 返金（受取）← NEW
| "withholding_tax" // 源泉徴収 ← NEW
| "fixed_asset" // 固定資産購入 ← NEW
| "other" // その他
Proposed UX:
┌─────────────────────────────────────────┐
│ 請求書 #INV-2026-001 │
│ 合計: ¥300,000 │
│ ████████░░ 67% 支払済 │
│ │
│ 支払履歴: │
│ ├─ 1/10 ¥100,000 (1回目) │
│ ├─ 1/25 ¥100,000 (2回目) │
│ └─ 残り ¥100,000 (3回目予定) │
└─────────────────────────────────────────┘
Yes, auto-suggest with smart banner:
┌─────────────────────────────────────────┐
│ 💡 この取引は毎月発生しています │
│ "AWS" ¥12,000前後 (過去6回) │
│ │
│ [自動ルールを作成] [今回だけ適用] [×] │
└─────────────────────────────────────────┘
Guided workflow:
┌─────────────────────────────────────────┐
│ 入金: Amazon +¥5,280 │
│ │
│ ⚠️ Amazonは通常支払先です │
│ │
│ この入金の種類は？ │
│ ┌─────────┐ ┌─────────┐ ┌─────────┐ │
│ │ 🔄 返金 │ │ 💰 売上 │ │ ❓ 不明 │ │
│ │(推奨) │ │ │ │ │ │
│ └─────────┘ └─────────┘ └─────────┘ │
└─────────────────────────────────────────┘
Company-scoped is preferred:
company_id for data isolationYes, needed for compliance:
interface LinkAuditLog {
id: string
linkId: string
action: "created" | "modified" | "unlinked"
userId: string
timestamp: Date
previousState?: object
newState?: object
reason?: string // User can add note when unlinking
}
Both supported:
| Operation | Use Case | Priority |
|---|---|---|
| Multi TX → One Doc | Split payment (3 payments to 1 invoice) | HIGH |
| One TX → Multi Doc | Batch salary (1 transfer for 5 employees) | HIGH |
| Multi TX → Multi Doc | Month-end bulk matching | MEDIUM |
UI Approach:
| Phase | What | Priority | Frontend Ready? |
|---|---|---|---|
| 1 | Enhance document_transaction_links with full linkage_type support |
HIGH | ✅ Yes |
| 2 | Add expense_claim_receipts table |
HIGH | ✅ Yes |
| 3 | Add POST /api/reconciliation/link flexible endpoint |
HIGH | ✅ Yes |
| 4 | Add GET /api/documents/search-for-matching |
HIGH | ✅ Yes |
| 5 | Add credit_card_statements table |
MEDIUM | 🔄 Partial |
| 6 | Add payment_statements batch payroll |
MEDIUM | 🔄 Partial |
| 7 | Add POST /api/ai/analyze-transaction |
MEDIUM | ✅ Yes |
| 8 | Add split payment support | LOW | 🔄 Design needed |
| 9 | Add recurring rules | LOW | 🔄 Design needed |
-- Track partial payments for invoices
CREATE TABLE invoice_payments (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
invoice_id UUID NOT NULL REFERENCES documents(id),
bank_transaction_id UUID NOT NULL REFERENCES bank_statements(id),
payment_amount DECIMAL(15,2) NOT NULL,
payment_number INTEGER NOT NULL, -- e.g., 2
total_payments INTEGER, -- e.g., 3 (if known)
created_at TIMESTAMPTZ DEFAULT NOW()
);
-- View: Invoice with payment status
CREATE VIEW invoice_payment_status AS
SELECT
d.id as invoice_id,
d.total_amount,
COALESCE(SUM(ip.payment_amount), 0) as paid_amount,
d.total_amount - COALESCE(SUM(ip.payment_amount), 0) as remaining_amount,
COUNT(ip.id) as payment_count,
CASE
WHEN SUM(ip.payment_amount) >= d.total_amount THEN 'paid'
WHEN SUM(ip.payment_amount) > 0 THEN 'partial'
ELSE 'unpaid'
END as payment_status
FROM documents d
LEFT JOIN invoice_payments ip ON ip.invoice_id = d.id
WHERE d.document_type = 'invoice'
GROUP BY d.id;
CREATE TABLE recurring_link_rules (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
user_id UUID NOT NULL REFERENCES auth.users(id),
company_id UUID REFERENCES companies(id),
-- Matching criteria
vendor_pattern VARCHAR(255),
amount_min DECIMAL(15,2),
amount_max DECIMAL(15,2),
frequency VARCHAR(20), -- monthly, weekly, quarterly
-- Auto-link configuration
linkage_type VARCHAR(30) NOT NULL,
default_account_code VARCHAR(20),
default_tax_category VARCHAR(50),
auto_create_journal BOOLEAN DEFAULT false,
-- Stats
times_applied INTEGER DEFAULT 0,
last_applied_at TIMESTAMPTZ,
is_active BOOLEAN DEFAULT true,
created_at TIMESTAMPTZ DEFAULT NOW(),
updated_at TIMESTAMPTZ DEFAULT NOW()
);
CREATE INDEX idx_recurring_rules_user ON recurring_link_rules(user_id, is_active);
CREATE TABLE link_audit_logs (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
link_id UUID NOT NULL, -- References document_transaction_links
link_type VARCHAR(50) NOT NULL, -- 'document_transaction', 'expense_receipt', etc.
action VARCHAR(20) NOT NULL, -- created, modified, unlinked
user_id UUID NOT NULL REFERENCES auth.users(id),
previous_state JSONB,
new_state JSONB,
reason TEXT, -- Optional user note
created_at TIMESTAMPTZ DEFAULT NOW()
);
CREATE INDEX idx_link_audit_link ON link_audit_logs(link_id);
CREATE INDEX idx_link_audit_user ON link_audit_logs(user_id);
読み込み中
豆知識