Complete specification for rewriting Swarmops v5 as using.cash v6: - Proposal: scope buckets (Working Rewrite / First Beyond Working / Maybe), non-goals (governance, geography, memberships permanently cut) - Design: 18 architectural decisions including preserved patterns (Basic/Logic, stored procs, credential split), new patterns (repository classes, chain abstraction, rate providers), and operational concerns (Galera awareness, continuation transactions, VAT booking, OOD denormalization) - Specs: 17 capability specifications with SHALL/MUST requirements and WHEN/THEN scenarios, covering accounting core, AP/AR, crypto treasury, exchange rates, organizations, users/auth, documents, comms, dashboard, end-of-month, backend worker, event bus, data migration, API layer, frontend app, and CI/packaging - Tasks: 145 implementation tasks grouped by subsystem, ordered by dependency (migration gates API/frontend work) Two rounds of adversarial review incorporated. Made-with: Cursor
20 KiB
20 KiB
1. Solution Scaffolding & CI
- 1.1 Create .NET 8 SDK-style solution with projects: UsingCash.Common, UsingCash.Basic, UsingCash.Database, UsingCash.Logic, UsingCash.Api, UsingCash.Backend, UsingCash.Migration, UsingCash.Tests
- 1.2 Set up project references: Common ← Basic ← Database ← Logic ← Api/Backend/Migration; Tests → Common, Basic, Database, Logic, Api, Migration
- 1.3 Add NuGet dependencies: MySqlConnector, NBitcoin, System.Text.Json; remove all checked-in DLLs from External Libraries
- 1.4 Scaffold React + TypeScript + Vite frontend project in
frontend/with MUI, react-router, react-i18next dependencies - 1.5 Create multi-stage Dockerfile (build .NET + build frontend → runtime image serving both)
- 1.6 Create Gitea Actions workflow: dotnet restore → build → analyze/lint → test → npm ci → npm run lint → npm run build → docker build → push image
- 1.7 Add
appsettings.json/appsettings.Development.jsonwith database config section preserving read/write/admin credential structure, SMTP config, storage root path, dev-mode flag
2. Database Schema — Core Tables
- 2.1 Design and write DDL for Organizations table (denormalized from ObjectOptionalData: Currency, FirstFiscalYear, ClosedThroughYear, VatEnabled, DefaultVatRatePercent, VatReportFrequencyMonths, EconomyEnabled, GovtRegistrationId, TaxAuthority, TaxAccount, TaxOcr, FinancialMailName, FinancialMailAddress, LogoLandscape, LogoSquare)
- 2.2 Design and write DDL for Users table (Name, Email, PasswordHash, Active, PreferredCulture, BankName, BankClearing, BankAccount, BitcoinPayoutAddress, NeverMail, ResetPasswordTicket)
- 2.3 Design and write DDL for Roles, UserRoles, RolePermissions tables
- 2.4 Design and write DDL for FinancialAccounts table (including OwnerUserId, NativeCurrencyId, BitcoinPublicAddress, BitcoinPrivateRoot, BitcoinPublicRoot, BankTransactionTag, EnabledForExpensing, EnabledForInvoicing, AutomationProfileId, OpenedYear, ClosedYear)
- 2.5 Design and write DDL for FinancialTransactions table (including OrganizationSequenceId, BlockchainHash, ImportSha256)
- 2.6 Design and write DDL for FinancialTransactionRows table (including ForeignCurrencyCode, ForeignCurrencyAmountCents, BitcoinDerivationPath)
- 2.7 Design and write DDL for FinancialTransactionDependencies table (for continuation chains and business object links)
- 2.8 Design and write DDL for FinancialTransactionTags, TagSets, TagTypes tables
- 2.9 Design and write DDL for FinancialAccountBudgets table (monthly/yearly)
3. Database Schema — AP/AR & Support Tables
- 3.1 Design and write DDL for ExpenseClaims table (including VatCents, status lifecycle columns)
- 3.2 Design and write DDL for InboundInvoices table (including Description, VatCents, ExactBitcoinAmountSatoshis, NativeCurrencyCode, NativeCurrencyAmountCents)
- 3.3 Design and write DDL for OutboundInvoices and OutboundInvoiceItems tables (including VatCents, CreditedWithInvoice, CreditsInvoice, ToUserId)
- 3.4 Design and write DDL for Payouts, PayoutItems, Payments, PaymentGroups, Refunds tables
- 3.5 Design and write DDL for Documents and FinancialAccountDocuments tables
- 3.6 Design and write DDL for Currencies, ExchangeRateSnapshots, ExchangeRateDatapoints tables
- 3.7 Design and write DDL for CryptoChains, HotWalletAddresses, HotWalletUtxos tables
- 3.8 Design and write DDL for MailQueue, MailTemplates, EventQueue, SystemSettings tables
4. Database Schema — Stored Procedures & Security
- 4.1 Write stored procedures for financial transaction writes: CreateFinancialTransaction (with OrganizationSequenceId generation), AddTransactionRow, SetTransactionTag, SetTransactionDependency
- 4.2 Write stored procedures for financial account writes: CreateFinancialAccount, SetAccountName, SetAccountClosed, SetAccountOwner, SetBudget
- 4.3 Write stored procedures for expense claim writes: CreateExpenseClaim, SetExpenseClaimStatus, SetExpenseClaimAttested, SetExpenseClaimValidated
- 4.4 Write stored procedures for invoice writes: CreateInboundInvoice, SetInboundInvoiceStatus, CreateOutboundInvoice, SetOutboundInvoicePaid
- 4.5 Write stored procedures for payout/payment writes: CreatePayout, SetPayoutExecuted, CreatePayment, CreatePaymentGroup
- 4.6 Write stored procedures for user/org/crypto/document/mail/event writes
- 4.7 Write SQL trigger on FinancialTransactionRows rejecting direct inserts/updates/deletes where the transaction date falls within a closed fiscal period
- 4.8 Create MySQL users for read (SELECT only), write (EXECUTE on write procedures), and admin (DDL) with appropriate GRANTs
- 4.9 Write combined schema initialization script (all tables, procedures, triggers, users)
5. Database Layer (UsingCash.Database)
- 5.1 Implement DatabaseConnection factory with GetForReading(), GetForWriting(), GetForAdmin() — Galera-aware: LAST_INSERT_ID always read on write connection
- 5.2 Implement RepositoryBase with shared helpers: GetCommand (stored proc), AddParameter, ExecuteScalar, ExecuteReader, ExecuteNonQuery
- 5.3 Implement FinancialRepository: read methods (GetAccount, GetTransaction, GetTransactionRows, GetRowForeignCurrency, GetAccountsForOrg, GetTransactionsForAccountInRange, GetUnbalancedTransactions, GetBudgets, GetTransactionDependency, GetTransactionFromDependency)
- 5.4 Implement FinancialRepository: write methods wrapping stored procedures (CreateTransaction, AddRow, SetTag, SetDependency, CreateAccount, SetAccountName, SetAccountClosed, SetBudget, AdvanceClosedThroughYear)
- 5.5 Implement ApArRepository: read/write for expense claims, inbound/outbound invoices, payouts, payments, refunds
- 5.6 Implement UserRepository: read/write for users, roles, role assignments, permission lookups, password hash/verify
- 5.7 Implement OrganizationRepository: read/write for organizations and all denormalized settings
- 5.8 Implement CryptoRepository: read/write for hot wallet addresses, UTXOs, blockchain transactions
- 5.9 Implement DocumentRepository: read/write for document metadata and financial account documents
- 5.10 Implement CurrencyRepository: read/write for currencies, exchange rate snapshots and datapoints (micro-BTC scaling aware)
- 5.11 Implement SupportRepository: mail queue, mail templates, event queue, system settings
6. Common & Basic Layers
- 6.1 Port enums to UsingCash.Common: FinancialAccountType, Permission, DocumentType, CryptoChain, ExpenseClaimStatus, InvoiceStatus, PayoutStatus, FinancialDependencyType — trimming governance/geography/membership enums
- 6.2 Port IHasIdentity interface and shared attributes/interfaces to UsingCash.Common
- 6.3 Port Basic types: BasicFinancialAccount (with OwnerUserId, NativeCurrencyId), BasicFinancialTransaction (with OrganizationSequenceId, BlockchainHash), BasicFinancialTransactionRow (with ForeignCurrencyCode, ForeignCurrencyAmountCents), BasicCurrency, BasicOrganization (with all denormalized settings), BasicUser, BasicDocument, BasicExpenseClaim, BasicInboundInvoice, BasicOutboundInvoice, BasicPayout, BasicHotWalletAddress, BasicHotWalletUtxo
- 6.4 Implement Money value type (Int64 Cents, Currency reference, display formatting per currency unit scale, ToCurrency using double intermediate arithmetic)
7. Logic Layer (UsingCash.Logic)
- 7.1 Implement SingularFactory and PluralBase<TPlural, TSingular, TBasic> with startup-time cache warming and fail-fast on missing mappings
- 7.2 Implement FinancialAccount (extends BasicFinancialAccount): balance computation, budget access, rename/close with closed-period enforcement, budget owner
- 7.3 Implement FinancialTransaction (extends BasicFinancialTransaction): AddRow with closed-period check AND continuation transaction creation/reuse, dependency tracking, ContinuedTransaction property, AddContinuedTransactionsToLookup, RecalculateTransaction across chain
- 7.4 Implement reporting logic: BalanceSheet, ProfitAndLoss, BudgetVsActual
- 7.5 Implement period closing logic: precondition checks, closing journal creation (Dec 31), advance ClosedThroughYear
- 7.6 Implement VAT booking in expense/invoice creation: split net amount to budget and VAT to VatInbound/VatOutbound accounts when VatEnabled and vatCents > 0
- 7.7 Implement ExpenseClaim lifecycle: create (with VAT split) → attest (self-attestation allowed) → validate → approve → payout, with closed-year date bumping
- 7.8 Implement InboundInvoice logic with VAT split, ledger transaction generation, attestation workflow
- 7.9 Implement OutboundInvoice logic with VAT split, ledger transaction generation, credit linking
- 7.10 Implement Payout logic: group approved items, execute (record ledger transactions), confirm
- 7.11 Implement Currency and ExchangeRateService: GetConversionRate via BTC hub with micro-BTC scaling and BCH exception, TakeSnapshot using IRateProvider chain
- 7.12 Implement BitPayRateProvider with proper JSON deserialization, micro-BTC scaling, BCH exception, response validation, and error handling
- 7.13 Implement IBlockchainProvider interface and BchBlockchainProvider (address generation, UTXO queries, tx building via NBitcoin)
- 7.14 Implement HotWallet logic: track addresses/UTXOs, detect deposits, build payout transactions, compute balances
- 7.15 Implement User and Authority: authentication (password hash/verify), role-based permission checks, session management (cookie-based)
- 7.16 Implement Organization logic: create, settings (all denormalized columns), switching, data scoping
- 7.17 Implement Document and IDocumentStorage with LocalFilesystemStorage (yyyy/mm/dd path structure, configurable root supporting legacy /var/lib/swarmops/upload/ path)
- 7.18 Implement DashboardTasks: generate tasks from pending attestations, unvalidated receipts, pending payouts
- 7.19 Implement MailService: enqueue with template rendering, placeholder substitution
- 7.20 Implement EventBus: enqueue typed events, dequeue and dispatch, retention/purge
8. Data Migration (UsingCash.Migration) — gates API/frontend work
- 8.1 Implement CLI tool structure: connection string args for v5 (read-only) and v6 (write), run/verify/audit modes
- 8.2 Implement ObjectOptionalData audit: read all v5 EAV rows, classify each key as keep/drop per D16 mapping, report any unmapped keys
- 8.3 Implement organization migration: read v5 Organizations + ObjectOptionalData → write v6 Organizations (flat, denormalized settings, drop ParentOrganizationId and geography)
- 8.4 Implement user migration: read v5 People + ObjectOptionalData → write v6 Users (name, email, bank details, bitcoin payout, preferred culture, require-password-reset flag)
- 8.5 Implement auth/role migration: map v5 position/access assignments to v6 role assignments; document the mapping
- 8.6 Implement financial account migration: read v5 FinancialAccounts + ObjectOptionalData → write v6 FinancialAccounts (denormalized, drop geography)
- 8.7 Implement financial transaction migration: read v5 FinancialTransactions → write v6 (preserve OrganizationSequenceId, BlockchainHash, ImportSha256)
- 8.8 Implement financial transaction row migration: read v5 FinancialTransactionRows + FinancialTransactionRowsNativeCurrency → write v6 rows with foreign currency columns
- 8.9 Implement continuation chain migration: read v5 FinancialTransactionDependencies → write v6 dependencies (preserve all types including FinancialTransaction chains)
- 8.10 Implement tag/budget migration: transaction tags, tag sets, tag types, budget allocations
- 8.11 Implement AP/AR migration: expense claims, inbound invoices (with Description, ExactBitcoinAmountSatoshis, native currency), outbound invoices (with credit links, ToUserId), payouts, payments, refunds
- 8.12 Implement currency and exchange rate migration: currencies, snapshots, datapoints (preserve micro-BTC scaling and BCH exception)
- 8.13 Implement crypto migration: hot wallet addresses, UTXOs
- 8.14 Implement document metadata migration: document records with storage paths pointing to existing filesystem
- 8.15 Implement aggregate verification: render balance sheets and P&L for all closed periods from both v5 and v6, diff output
- 8.16 Implement account-level verification: per-account-per-year transaction totals comparison
- 8.17 Implement transaction-level spot checks and per-org-per-year checksum validation
- 8.18 Implement idempotent re-run: truncate/replace v6 data on repeated migration runs
9. API Layer (UsingCash.Api)
- 9.1 Configure ASP.NET Core host: DI registration for repositories and logic services, session-cookie auth middleware, org-scoping middleware, error response middleware
- 9.2 Implement auth endpoints: POST /api/auth/login, POST /api/auth/logout, POST /api/auth/reset-password, GET /api/auth/me
- 9.3 Implement financial account endpoints: GET/POST /api/organizations/{orgId}/accounts, PUT /api/organizations/{orgId}/accounts/{id} (including budget owner assignment)
- 9.4 Implement ledger endpoints: GET /api/organizations/{orgId}/ledger, GET .../balance-sheet, GET .../profit-loss, GET .../budget-vs-actual
- 9.5 Implement transaction endpoints: GET/POST /api/organizations/{orgId}/transactions, GET .../transactions/{id} (showing continuation chains)
- 9.6 Implement expense claim endpoints: GET/POST /api/organizations/{orgId}/expense-claims (with VAT), POST .../attest, POST .../validate
- 9.7 Implement invoice endpoints: GET/POST for inbound-invoices (with VAT) and outbound-invoices (with VAT), attestation and payment actions
- 9.8 Implement payout endpoints: GET/POST /api/organizations/{orgId}/payouts, POST .../execute, POST .../confirm
- 9.9 Implement crypto endpoints: GET /api/organizations/{orgId}/crypto/treasury, GET .../addresses, POST .../payouts
- 9.10 Implement document endpoints: POST /api/organizations/{orgId}/documents (upload), GET .../documents/{id} (download)
- 9.11 Implement dashboard endpoints: GET /api/organizations/{orgId}/dashboard, GET .../tasks
- 9.12 Implement end-of-month endpoints: GET /api/organizations/{orgId}/end-of-month/checklist, POST .../upload-statement, POST .../close-year
- 9.13 Implement organization endpoints: GET/PUT /api/organizations/{orgId} (all denormalized settings), GET /api/organizations
- 9.14 Implement user management endpoints: GET/POST/PUT /api/users, POST /api/users/{id}/roles
- 9.15 Implement WebSocket endpoint for real-time notifications (deposit alerts, task changes, heartbeat status) with session-cookie auth on upgrade
- 9.16 Configure static file serving for React SPA with fallthrough to index.html
- 9.17 Implement dev-mode impersonation endpoint (disabled in production via configuration)
10. Frontend Application
- 10.1 Set up React project structure: routing, API client module, auth context (session cookies), org context, MUI theme, react-i18next with EN/SV/DE translation files
- 10.2 Implement reusable smart input components: monetary input (handles decimal commas/points, shows formatted confirmation) and date input (flexible format entry, shows interpreted date for verification)
- 10.3 Implement login page and auth flow (session cookie management, 401 redirect)
- 10.4 Implement app shell: sidebar navigation, top bar with org switcher and language selector, responsive layout
- 10.5 Implement dashboard page: task summary, YTD P&L, quick links, end-of-month status
- 10.6 Implement chart of accounts page: tree view with account types, create/edit/close/assign-owner actions
- 10.7 Implement inspect ledgers page: account selector, date range picker, transaction row table with running balance and continuation chain links
- 10.8 Implement balance sheet page: as-of date selector, asset/debt/equity breakdown
- 10.9 Implement profit & loss page: fiscal year selector, income/cost breakdown
- 10.10 Implement budget vs actual page: year selector, account-level budget/actual/variance table
- 10.11 Implement expense claim pages: submission form with smart monetary input for amount and VAT (when org VatEnabled), document upload, list view, attestation/validation actions
- 10.12 Implement inbound invoice pages: registration form with smart monetary input for amount and VAT, smart date input for issue/due dates, list view, attestation action
- 10.13 Implement outbound invoice pages: creation form with line items, VAT pre-filled from org default rate (editable for cross-border/exempt) using smart monetary input, list view, mark-paid action
- 10.14 Implement payout management page: group items, execute, confirm
- 10.15 Implement crypto treasury page: wallet balance, address list, UTXO details, fiat equivalent, deposit/payout history
- 10.16 Implement end-of-month page: monthly checklist, bank statement upload (manual review), year-end close link
- 10.17 Implement organization settings page: all denormalized settings (name, currency, default VAT rate, VAT config, tax IDs, logo)
- 10.18 Implement user management page: user list, create/edit/deactivate, role assignment
- 10.19 Implement WebSocket client for real-time notifications (deposit alerts, task updates)
- 10.20 Generate initial Swedish and German translation packs; human review for Swedish
11. Backend Worker (UsingCash.Backend)
- 11.1 Implement IHostedService-based worker with configurable task intervals and DI integration
- 11.2 Implement schema version check on startup with auto-migration using admin credentials
- 11.3 Implement exchange rate snapshot task (periodic, using IRateProvider chain with micro-BTC scaling)
- 11.4 Implement mail queue processing task (dequeue pending, deliver via SMTP, update status)
- 11.5 Implement blockchain monitoring task (query chain provider for new deposits, ingest to ledger, skip closed periods)
- 11.6 Implement heartbeat task (update timestamp in system settings)
- 11.7 Implement event bus consumption task (dequeue events, dispatch to handlers, purge old events)
- 11.8 Implement forex P&L recalculation task (weekly, per foreign-currency account)
- 11.9 Implement automated payout cycle task (every 5 minutes, process approved payouts)
12. Bootstrap & Deployment
- 12.1 Implement database seeding: first-run bootstrap that creates initial admin user, system administrator role, and first organization
- 12.2 Write deployment documentation: Docker run instructions, database setup, credential configuration, storage path setup
- 12.3 Verify end-to-end: fresh deployment → seed → migration from v5 → verify → serve production traffic
13. Testing
- 13.1 Write unit tests for Money type (storage, conversion with double arithmetic, display formatting across fiat and crypto)
- 13.2 Write unit tests for period closing logic (preconditions, closing journal, advancement, rejection cases)
- 13.3 Write unit tests for continuation transactions (create chain, recurse, recalculate across chain, edge cases)
- 13.4 Write unit tests for expense claim lifecycle (state transitions, self-attestation allowed, closed-year date bumping, VAT splits)
- 13.5 Write unit tests for exchange rate conversion (BTC hub model, micro-BTC scaling, BCH exception, cross-rate derivation, missing rate handling)
- 13.6 Write unit tests for RBAC (role assignment, permission check, missing permission rejection)
- 13.7 Write integration tests for FinancialRepository against a test MySQL database (including foreign currency rows and continuation dependencies)
- 13.8 Write integration tests for migration tool against v5 test fixtures (including ObjectOptionalData denormalization, continuation chains, foreign currency, VAT-split rows)
- 13.9 Write API integration tests for auth, ledger reads, expense claim workflow (with VAT), permission enforcement
- 13.10 Write SingularFactory tests verifying all Basic→Logic type mappings resolve at startup
- 13.11 Write migration verification tests: confirm aggregate, account-level, and transaction-level checks catch introduced discrepancies