Files
rick 212f19ac19 Add v6-rewrite OpenSpec: proposal, design, specs, and tasks
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
2026-04-08 16:02:58 +02:00

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.json with 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