Skip to content

specbook.md

Latest commit

 

History

History
288 lines (230 loc) · 25.9 KB

specbook.md

File metadata and controls

288 lines (230 loc) · 25.9 KB

SpecBook: 3Books - A Bespoke Accounting System

Specifications Are All You Need (SAAYN)

A Specification-Centric Model for Automated Software Synthesis via AI

Table of Contents

  1. Chapter 1: Chart of Accounts
    Defines the foundational Account entity for financial tracking, enabling standard and custom reports like the Monthly Performance Report (MPR).
  2. Chapter 2: Transactions
    Establishes the Transaction model for double-entry bookkeeping, integrating booked and forecasted data from Track3 CRM.
  3. Chapter 3: Invoicing and Receivables
    Details Customer, Invoice, Quote, and InvoiceLineItem entities for managing sales and accounts receivable, with CRM and auth integration.
  4. Chapter 4: Expense and Bill Management
    Specifies Vendor, Bill, Expense, and Receipt entities for accounts payable, including forecasted expenses and receipt OCR.
  5. Chapter 5: User Roles and Authentication (TBD)
    Placeholder for defining user permissions and integration with FIRM authentication system.
  6. Chapter 6: Audit Trail (TBD)
    Placeholder for logging all changes to ensure compliance and data integrity.
  7. Chapter 7: Reporting and MPR (TBD)
    Placeholder for detailed specs on P&L, Balance Sheet, and MPR with forecast toggling.
  8. Chapter 8: Deployment and Maintenance
    Defines single-tenant deployment on Linux laptops/VMs, with apt-based updates and backup strategies.

Chapter 1: Chart of Accounts (Revised)

Purpose

To define the foundational data structure for the Chart of Accounts (COA) that will serve as the backbone for the 3Books accounting system. This chapter establishes the core financial accounts that all transactions will reference, ensuring the system can support the full range of standard financial reports, our bespoke Monthly Performance Report (MPR), and a wide variety of operational, audit, and customer reports as outlined in our discussions.

Data Model: Account

This specification outlines the data fields and constraints for the Account entity, which serves as the fundamental building block for all financial records in the system. Its structure is designed to provide the granularity needed for both simple user dashboards and detailed CPA-level reporting.

  • account_id: A unique, system-generated identifier for each account. This will serve as the primary key (UUID, Go: github.com/google/uuid).
  • account_name: A user-defined, human-readable name of the account (e.g., "Sales Revenue," "Checking Account"). This field must be unique to prevent data ambiguity (string, max 255 chars).
  • account_number: An optional, user-defined number for the account (e.g., 4000, 5120). This is a common requirement for CPAs and provides a mechanism for logical grouping (string, max 20 chars, nullable).
  • account_type: A mandatory classification that defines the account's category from a predefined enum list. Possible values are Asset, Liability, Equity, Revenue, and Expense. This is essential for generating the Balance Sheet and P&L (enum in Go, string in DB).
  • account_subtype: A mandatory, more granular classification that provides detail within the main type (e.g., Current Asset, Cost of Goods Sold, Payroll Expenses). This is crucial for detailed reporting and for the CPA's needs (enum in Go, string in DB).
  • description: An optional field for a detailed explanation of the account's purpose (string, max 1000 chars, nullable).
  • is_active: A boolean flag indicating if the account is currently in use. Inactive accounts are retained for historical data integrity but are hidden from the user interface for new transactions (boolean, default true).
  • created_at: A system-generated timestamp for when the account was first created (timestamp with time zone).
  • updated_at: A system-generated timestamp for the last modification of the account (timestamp with time zone).

Functional Requirements

  • CRUD Operations with Permissions: The system must provide an interface for users with appropriate permissions (as defined in a future User Roles chapter) to Create, Read, Update, and Delete accounts. Deletion should be a "soft delete," meaning an account is marked as inactive rather than permanently removed, to preserve historical data (API endpoints: POST /accounts, GET /accounts, PUT /accounts/:id, DELETE /accounts/:id sets is_active=false).
  • Default COA Generation: Upon initial company setup, the system shall pre-populate a standard Chart of Accounts relevant to a small business in the United States. This default list will be fully editable to allow for customization (Go function to seed DB; Next.js UI for editing).
  • Validation Rules:
    • account_name must be a non-empty string and unique within the company's books (DB unique constraint).
    • account_type and account_subtype must be selected from a predefined, system-managed list (DB check constraint or enum).
    • An account with existing, associated transactions cannot be deleted. The system must prompt the user to make the account inactive instead (Go validation checks transactions table).
  • Audit Trail: All changes to an account's data (account_name, account_type, is_active, etc.) must be recorded in an audit trail (as defined in a future Audit Trail chapter) to maintain data integrity and compliance (Go middleware to log changes).

Integration Points

  • Data Migration: The system must support importing a Chart of Accounts from other systems or spreadsheets (e.g., .csv or .xlsx). This functionality should include mapping tools to match imported data to our account_type and account_subtype fields (Next.js form + Go CSV parser).
  • Transaction Feeds: The COA is the primary destination for transactions from integrated sources. Bank feeds, Stripe payments, and other integrations must use the accounts defined in this chapter to categorize transactions (Go API routes to map external data).

Chapter 2: Transactions (Revised)

Purpose

To define the foundational data model for all financial transactions within the 3Books system. This chapter establishes the core entity that records every debit and credit, linking to the Chart of Accounts. This structure is designed to support the generation of all financial reports, including the P&L, Balance Sheet, Statement of Cash Flows, and the custom Monthly Performance Report (MPR), which will now be enhanced with forecasted data from the Track3 CRM.

Data Model: Transaction

This specification outlines the data fields and constraints for the Transaction entity, which serves as the central ledger for all financial activity. It now includes fields to differentiate between booked transactions and forecasted data from the CRM.

  • transaction_id: A unique, system-generated identifier for each transaction. This is the primary key (UUID).
  • transaction_date: The date on which the transaction occurred or is expected to occur (date).
  • event_month: The month and year to which the transaction belongs for the purposes of the MPR (date, truncated to month).
  • description: A human-readable description of the transaction (e.g., "Payment to Alta3.com for server hosting") (string, max 255 chars).
  • debit_account_id: A foreign key referencing the account_id from the Chart of Accounts that is debited (UUID, FK).
  • credit_account_id: A foreign key referencing the account_id from the Chart of Accounts that is credited (UUID, FK).
  • amount: The value of the transaction. This must be a positive number (decimal(19,4)).
  • transaction_type: An enum field to classify the origin of the transaction. Possible values include:
    • BOOKED: A finalized, auditable financial transaction.
    • TRACK3_FORECAST: A forecasted transaction originating from the Track3 CRM (enum in Go, string in DB).
  • confidence_level: A numeric value (0-100) that indicates the probability of a TRACK3_FORECAST transaction being realized. This field is optional and only applies to forecasted transactions (integer, nullable).
  • created_at: A system-generated timestamp for when the transaction was entered into the system (timestamp with time zone).
  • updated_at: A system-generated timestamp for the last modification of the transaction (timestamp with time zone).

Functional Requirements

  • CRUD Operations with Permissions: The system must allow users with appropriate permissions to create, read, update, and delete transactions. Deleting a transaction should be a soft delete, with a record maintained in the audit trail (API endpoints: POST /transactions, GET /transactions, etc.).
  • Double-Entry Validation: All BOOKED transactions must adhere to double-entry accounting principles, with a debit and a credit of equal value. TRACK3_FORECAST transactions are exempt from this requirement as they are for reporting purposes only (Go validation logic).
  • Categorization: All transactions, whether manual or imported, must be categorized by linking them to the appropriate account_ids from the COA (Go service layer).
  • Monthly Performance Report (MPR) Logic: The system must use the event_month field to generate the MPR. The user interface for the MPR must include an option to toggle the inclusion of TRACK3_FORECAST transactions, allowing the CEO to see a standard report or a "best guess" forecast (Next.js toggle component).

Integration Points

  • Track3 CRM: The system will integrate with Track3's API to pull potential orders and create TRACK3_FORECAST transactions. These forecast transactions will automatically be assigned an event_month and confidence_level based on the data received from Track3. The system will perform regular, automated updates to reflect changes in the CRM (Go background job).
  • Bank Feeds & Payment Gateways: Transactions from integrated bank feeds and payment processors will continue to be automatically created in this table, classified as BOOKED transactions (Go API handlers).
  • Audit Trail: All creation, modification, and deletion of transactions must be logged in the audit trail. This is especially critical for BOOKED transactions to maintain a complete history for compliance and verification (Go middleware).

Chapter 3: Invoicing and Receivables

Purpose

To define the data models and workflows for managing customer information, sales, and accounts receivable. This chapter establishes the entities for Customers, Invoices, and Quotes, linking them to the core Transaction model to ensure all sales activities are accurately recorded and tracked. The system will support customizable invoicing, automated reminders, and seamless integration with our CRM (Track3) and authentication system (FIRM).

Data Models

Customer

This entity holds all customer-related information and links to our bespoke authentication system.

  • customer_id: A unique identifier for the customer (primary key, UUID).
  • firm_user_id: A foreign key linking to the user's ID in the FIRM authentication system. This ensures the customer profile is tied to a secure user account (UUID, FK).
  • company_name: The name of the customer's business (string, max 255 chars).
  • contact_name: The primary contact person's name (string, max 100 chars).
  • email: The primary email address for communication and invoicing (string, max 255 chars).
  • phone: The customer's primary phone number (string, max 20 chars, nullable).
  • billing_address: The customer's billing address (text).
  • shipping_address: An optional shipping address (text, nullable).
  • payment_terms: A predefined set of payment terms (e.g., "Net 30," "Due on Receipt") (string, max 50 chars).
  • customer_group: An optional field for categorizing customers (string, max 100 chars, nullable).
  • created_at: Timestamp of creation (timestamp with time zone).
  • updated_at: Timestamp of last update (timestamp with time zone).
Invoice

This entity represents a formal request for payment for goods or services rendered. It is the primary mechanism for tracking accounts receivable.

  • invoice_id: A unique identifier for the invoice (UUID, PK).
  • customer_id: A foreign key linking to the Customer entity (UUID, FK).
  • invoice_number: A unique, sequential number for the invoice (string, max 20 chars, unique).
  • invoice_date: The date the invoice was issued (date).
  • due_date: The date the payment is due (date).
  • status: An enum for tracking the invoice lifecycle (e.g., Draft, Sent, Viewed, Overdue, Paid, Partially Paid) (enum in Go, string in DB).
  • total_amount: The final total of the invoice, including taxes and discounts (decimal(19,4)).
  • tax_amount: The total tax amount on the invoice (decimal(19,4)).
  • discount_amount: The total discount amount applied (decimal(19,4)).
  • transaction_id: A foreign key linking to a receivable Transaction entry (UUID, FK, nullable until paid).
  • notes: An optional field for internal or customer-facing notes (text, nullable).
  • template_id: A foreign key for the invoice template used (UUID, FK, nullable).
  • created_at: Timestamp of creation (timestamp with time zone).
  • updated_at: Timestamp of last update (timestamp with time zone).
InvoiceLineItem

A sub-entity of an invoice that details a single item or service.

  • line_item_id: Unique identifier (UUID, PK).
  • invoice_id: Foreign key linking to the parent Invoice (UUID, FK).
  • description: A description of the item or service (string, max 255 chars).
  • quantity: The number of units (integer).
  • unit_price: The price per unit (decimal(19,4)).
  • subtotal: The quantity multiplied by the unit price (decimal(19,4)).
  • tax_rate: The tax percentage applied to this line item (decimal(5,2), nullable).
Quote

This entity represents a proposal or estimate.

  • quote_id: Unique identifier (UUID, PK).
  • customer_id: Foreign key linking to the Customer entity (UUID, FK).
  • quote_date: The date the quote was issued (date).
  • expiry_date: The date the quote is no longer valid (date).
  • status: An enum for tracking the quote lifecycle (e.g., Draft, Sent, Accepted, Rejected) (enum in Go, string in DB).
  • total_amount: The estimated total amount (decimal(19,4)).
  • created_at: Timestamp of creation (timestamp with time zone).
  • updated_at: Timestamp of last update (timestamp with time zone).

Functional Requirements

  • Invoice/Quote Management: Allow users to create, edit, send, and track the status of invoices and quotes. The system must support the generation of quotes that can be seamlessly converted into a finalized Invoice (Next.js forms, Go API endpoints).
  • Customization: Enable the use of templates for invoices and estimates, with the ability to add and apply discounts and taxes at both the line item and invoice levels (Next.js template editor).
  • Payment Matching: The system must be able to automatically match incoming payments to specific invoices, updating the Invoice status and recording a corresponding Transaction entry (Go service with bank feed logic).
  • Automated Reminders: Implement an automated system to send payment reminders for invoices that are overdue or approaching their due date (Go cron job, Next.js notification UI).
  • Job Costing: The system must track costs associated with a quote or project, a key feature for project-based billing (Go logic to link quotes to expenses).

Integration Points

  • Track3 (CRM): The system must expose an API to receive quote and order data from Track3. This data will be used to automatically generate Quotes and, upon a customer's acceptance, convert them into Invoices (Go API handlers).
  • FIRM (Authentication): Customer profiles must be linked to our bespoke FIRM authentication system. This ensures a consistent user identity across both systems and supports a unified user experience (Go middleware for auth).
  • External API: A secure, well-documented RESTful API will be exposed to allow other systems to programmatically create and manage invoices and customers, supporting the broader ecosystem you envisioned (OpenAPI spec in Go).
  • E-commerce: A future integration point will connect to e-commerce platforms to automatically generate invoices from online sales (TBD in future chapter).

Chapter 4: Expense and Bill Management (Revised)

Purpose

To define the data models and workflows for managing expenses, vendor bills, and accounts payable. This chapter establishes the entities for Vendors, Bills, and Expenses, linking them to the core Transaction model. The system will support receipt capture, automated transaction matching, approval workflows, and now, the integration of projected expenses from the Track3 CRM to provide a more complete forecast.

Data Models

Vendor

This entity holds all vendor-related information, serving as the counterpart to the Customer entity.

  • vendor_id: A unique identifier for the vendor (primary key, UUID).
  • company_name: The name of the vendor's business (string, max 255 chars).
  • contact_name: The primary contact person's name (string, max 100 chars).
  • email: The primary email address for communication and billing (string, max 255 chars).
  • phone: The vendor's primary phone number (string, max 20 chars, nullable).
  • billing_address: The vendor's billing address (text).
  • payment_terms: A predefined set of payment terms (e.g., "Net 30") (string, max 50 chars).
  • created_at: Timestamp of creation (timestamp with time zone).
  • updated_at: Timestamp of last update (timestamp with time zone).
Bill

This entity represents a formal bill received from a vendor that needs to be paid.

  • bill_id: A unique identifier for the bill (UUID, PK).
  • vendor_id: A foreign key linking to the Vendor entity (UUID, FK).
  • bill_date: The date the bill was issued or is projected to be issued (date).
  • due_date: The date the bill is due (date).
  • status: An enum for tracking the bill's lifecycle (e.g., Draft, Received, Approved, Scheduled, Paid) (enum in Go, string in DB).
  • total_amount: The final total amount of the bill (decimal(19,4)).
  • transaction_id: A foreign key linking to a payable Transaction entry (UUID, FK, nullable until paid).
  • source_type: An enum to classify the bill's origin. Possible values include:
    • BOOKED: A finalized, auditable bill.
    • TRACK3_FORECAST: A projected bill from the Track3 CRM (enum in Go, string in DB).
  • confidence_level: A numeric value (0-100) that indicates the probability of a TRACK3_FORECAST bill being realized (integer, nullable).
  • created_at: Timestamp of creation (timestamp with time zone).
  • updated_at: Timestamp of last update (timestamp with time zone).
Expense

This entity represents a direct expenditure.

  • expense_id: A unique identifier for the expense (UUID, PK).
  • expense_date: The date the expense occurred (date).
  • vendor_id: An optional foreign key linking to a Vendor entity (UUID, FK, nullable).
  • description: A human-readable description of the expense (string, max 255 chars).
  • amount: The value of the expense (decimal(19,4)).
  • transaction_id: A foreign key linking to a Transaction entry (UUID, FK, nullable).
  • source_type: An enum to classify the expense's origin. Possible values are BOOKED and TRACK3_FORECAST (enum in Go, string in DB).
  • confidence_level: A numeric value indicating the probability of a TRACK3_FORECAST expense being realized (integer, nullable).
  • created_at: Timestamp of creation (timestamp with time zone).
  • updated_at: Timestamp of last update (timestamp with time zone).
Receipt

This entity stores information about a captured receipt image.

  • receipt_id: A unique identifier (UUID, PK).
  • related_entity_id: A foreign key that links the receipt to either a Bill or an Expense (UUID, FK).
  • file_url: The URL or file path where the receipt image is stored (string, max 255 chars).
  • upload_date: Timestamp of when the receipt was uploaded (timestamp with time zone).
  • created_at: Timestamp of creation (timestamp with time zone).

Functional Requirements

  • Bill and Expense Management: The system must allow users to create, edit, and track the status of both BOOKED bills and expenses. Users must be able to schedule future payments for bills (Next.js forms, Go API endpoints).
  • Receipt Capture and OCR: The system will support the upload of receipt images via a mobile app or web interface. It will integrate with an OCR service to automatically extract key data and attempt to match the receipt to an existing BOOKED expense or bill record (Go API with OCR library integration).
  • Approval Workflows: The system must allow for the creation of simple approval workflows, where a BOOKED bill must be approved by a designated user before it can be scheduled for payment (Go workflow logic, Next.js UI).
  • Automated Matching: The system will use data from bank feeds to automatically match transactions to existing BOOKED bills and expenses, updating their status to Paid (Go service with bank feed integration).
  • Forecasting Logic: The system must include a mechanism to view and filter TRACK3_FORECAST bills and expenses. These will be included in the Monthly Performance Report to provide a forward-looking view, but they will not affect standard financial statements or formal accounting (Next.js toggle component).

Integration Points

  • Track3 CRM: The system will pull projected expenses from Track3's API, creating TRACK3_FORECAST records. These will be automatically assigned a confidence_level and an expected date (Go background job).
  • Receipt Capture: The system will integrate with a dedicated service for mobile-first receipt capture, providing an API to upload images and receive extracted data (Go API endpoint).
  • Bank Feeds: Transactions imported from integrated bank feeds will be used to automatically reconcile and mark BOOKED bills and expenses as paid (Go service).
  • Audit Trail: All creation, modification, and deletion of vendors, bills, and expenses must be logged in the audit trail (Go middleware).

Chapter 8: Deployment and Maintenance

Purpose

To define the deployment and maintenance strategy for the 3Books system, ensuring it runs efficiently as a single-tenant application on a pair of Linux laptops (primary + backup) or VMs, with updates managed via a simple bash script and robust backup for data integrity.

Deployment Requirements

  • Environment: Ubuntu 24.04+ (or Debian-based) on two laptops (e.g., 4-core, 8-16 GB RAM, 500 GB SSD). The primary runs the application and PostgreSQL, while the secondary mirrors the data for failover.
  • Components:
    • Go Backend: A single binary (3books) built with go build from the source, serving REST APIs via Gin on port 8080.
    • Next.js Frontend: The static assets generated by npm run build will be served directly by the Go backend. This eliminates the need for a separate Node.js service.
    • PostgreSQL: A local instance (port 5432) is required with a projected storage of ~100 GB for 20 million rows.
  • Packaging: The system will be distributed as a GitHub repository. The repository will contain the full source code and a comprehensive install.sh bash script.
  • Installation: The installation process is executed by running the install.sh script, which will handle all dependencies, build the source code, and set up the system.
  • Systemd Services: The install.sh script will create and enable the following systemd services:
    • 3books.service: Runs the combined Go binary, which serves both the backend API and the static frontend assets.
    • postgresql.service: The standard database service.
  • Networking: Local access (localhost or LAN via VPN). An optional Caddy reverse proxy can be configured for secure external access, for instance via a tunnel like Tailscale.

Maintenance Requirements

  • Updates: Users will pull the latest code from the GitHub repository (git pull). The install.sh script will be re-run to perform necessary updates, including database migrations handled by the Go binary at startup using Goose.
  • Backups: An hourly rsync of the PostgreSQL data directory (/var/lib/postgresql) to the secondary laptop is required for near real-time mirroring. A full daily pg_dump will be created and stored in a /backups/ directory.
  • Failover: In the event of primary laptop failure, the secondary can be promoted to the primary role via a manual pg_ctl promote command.
  • Monitoring: Basic health checks will include a pg_isready check and an HTTP ping to the /health endpoint exposed by the Go backend. Logs will be written to journald for easy debugging.
  • Compute Fit: The system is specified to handle up to 1,000 transactions per day, with the primary laptop's resources (CPU, RAM) remaining well below 10% utilization during normal operation.

Integration Points

  • GitHub: The core source of the system is a GitHub repository.
  • Bash Script: This script is the single point of entry for installation and updates.
  • Backup Sync: Rsync over SSH or Tailscale for secure data transfer between the primary and secondary laptops.
  • External APIs: The Go backend will communicate with external services like Track3 and FIRM via HTTPS. No local firewall changes are needed for internal network use.