Skip to content
caramelcookbook · 0.23.0
Browse documentation
Cookbook / Domain

Sum and group a context’s rows.

Declare a period’s total and a total per heading on the aggregate with sums and groups, each one statement that keeps the tenant predicate and leaves removed rows out.

Proved by scripts/check page-sumsTarget: 0.23.0
ContractValue
NeedsA Caramel installation set up as in Your first application; the recipe creates ledger, its App::Account tenant and its Ledger domain (SweetTalk)
You will haveA Journal aggregate whose lines are posted to one period, one sums that adds a period’s posted lines up, two groups that total them per heading, and a domain spec for each
StatusRun end to end by scripts/check page-sums
Proved byfrappe check · frappe corretto · frappe lint

Where the change goes

app/domain/ledger.crJournal, the sums and groups
db/migrations/Tables from the diff
spec/support/ledger.crPosts or drafts a journal
spec/domain/ledger_spec.crThe sum and the groups

Decide before you generate

  • Declare the sum on the aggregate that holds the rows

    sums and groups are declared beside finds and counts, with the same where: and satisfying:, and of: names the attribute they add up: one of the aggregate’s own by symbol, or one of its entities’ as Line.amount. Each runs one SELECT over the live rows of the bound tenant, so no SQL and no tenant predicate is written by hand. The attribute is a whole number, a Float64, or a value that wraps a whole number or a decimal and has no bound a sum could break: Cents here has none, because a credit is negative.

  • Keep a projection for a sum read on every request

    A sums adds its rows up when it is called. A total the application shows on every request, or one grouped by a key the attributes do not hold, is a projection keyed by what it groups by and kept current by whenever (SweetTalk, React).

Create the application, its tenant and its domain

The application is ledger. Its books belong to an account, so the tenant comes first, and frappe make domain then writes tenant App::Account into the domain it generates.

  1. Create the application
    Terminal · after machine setup
    frappe new ledger
    cd ledger

    Expect: Prints Created ledger., then ledger is configured at https://ledger.caramel.

  2. Make Account the tenant
    Terminal · inside the application
    frappe make tenancy Account

    Expect: Prints Generated PATH for each file it writes or edits, app/models/account.cr among them, then a Next: block that lists frappe migrate and Open /accounts/new to sign up the first tenant.

  3. Apply the tenant’s migration
    Terminal · inside the application
    frappe migrate

    Expect: Prints Applied 1 migrations., then The database matches the declared schema.

  4. Generate the domain
    Terminal · inside the application
    frappe make domain Ledger Books --means="what each heading was posted in each period"

    Expect: Prints Generated for each of the four files it writes, app/domain/ledger.cr and spec/domain/ledger_spec.cr among them, then a Next: block that lists Write the domain in app/domain/ledger.cr., frappe db diff --name create_ledger and frappe migrate.

Declare the journal, its sums and its groups

A Journal is the aggregate: lines added under headings, then posted once. The three queries at its end are the reports. posted_in adds up Line.amount over the posted journals of one period and returns a Cents; by_heading_in adds the same lines up once per heading; by_heading does so over every period. Each keeps where: on the journal, period and state, and groups by an attribute of the entity it adds up. Replace the generated app/domain/ledger.cr with this file.

app/domain/ledger.cr
# The Ledger domain, in SweetTalk (ADRs 0030 and 0031). Its words, such as
# context, value, aggregate, event and sums, exist only inside this module.
module App::Ledger
  include SweetTalk
  tenant App::Account

  context Books, subdomain: :core, means: "what each heading was posted in each period" do
    value Cents, Int64, means: "an amount in minor units; a credit is negative"
    value Heading, String, length: 1..40, means: "what an amount is posted under, such as sales"
    value Period, String, matches: /\A\d{4}-(0[1-9]|1[0-2])\z/,
      means: "a calendar month written YYYY-MM"

    aggregate Journal, means: "the lines posted together to one period" do
      has period : Period
      has lines : Array(Line)
      moves open >> posted

      entity Line, means: "one amount under one heading" do
        has heading : Heading
        has amount : Cents
      end

      event Posted, period : Period, lines : Array(Line),
        means: "the journal's lines reached the books"

      factory open(period : Period) do
      end

      to add(heading : Heading, amount : Cents) do
        refuse "A posted journal takes no more lines." if posted?
        @lines << Line.new(heading, amount)
      end

      to post do
        refuse "The journal is already posted." if posted?
        refuse "An empty journal cannot be posted." if lines.empty?
        posted!
        emit Posted, period: period, lines: lines
      end

      sums posted_in(period : Period), of: Line.amount, where: {period: period, state: :posted}
      groups by_heading_in(period : Period), of: Line.amount, by: Line.heading,
        where: {period: period, state: :posted}
      groups by_heading, of: Line.amount, by: Line.heading, where: {state: :posted}
    end
  end
end

Each query runs one statement: a sum over books_journal_lines whose journal_id is in a subquery over books_journals with the where: conditions, the tenant’s and the removed rows’ exclusion, so a drafted journal and another tenant’s lines are never added. PostgreSQL returns the sum as text, and Cents.new builds the value from it; a sum no row matches is Cents.new(0). A groups returns {by: Heading, total: Cents} rows ordered by the stored heading, so cash comes before sales.

ConditionOutcome
Journal.posted_in(period) for a period no journal was posted toreturns Cents.new(0), the sum of no rows
Journal.by_heading in a tenant with no posted journalreturns []
A sum past Int64raises ArgumentError from to_i64
sums of a value with an upper bound, or a lower bound other than 0fails compilation
groups that adds up Line.amount and groups by :period, an attribute of the journalfails compilation: narrow with where: instead
A finds block that sums, counts or groups its rows, or names SugarORMfails compilation
  1. Derive the migration
    Terminal · inside the application
    frappe db diff --name create_ledger

    Expect: Prints Wrote and the path of one migration ending in _create_ledger.cr. It creates books_journals and books_journal_lines, each with an account_id.

  2. Apply the migration
    Terminal · inside the application
    frappe migrate

    Expect: Prints Applied 1 migrations., then The database matches the declared schema.

Specify the sums

The helpers open one journal of amounts by heading and post it, or leave it open as a draft. Nothing follows Posted, so no queue is drained. The examples run in a tenant through account(db, slug), the helper frappe make tenancy wrote, and the last one posts to two tenants and reads the groups from the second.

spec/support/ledger.cr
# Builders the ledger's specs share.
module LedgerSpec
  extend self

  def period(text : String) : App::Ledger::Books::Period
    App::Ledger::Books::Period.new(text)
  end

  def heading(text : String) : App::Ledger::Books::Heading
    App::Ledger::Books::Heading.new(text)
  end

  # Opens one journal of *amounts* by heading to *period*, and leaves it open.
  def draft(period : String, amounts : Hash(String, Int32)) : Nil
    journal = App::Ledger::Books::Journal.open(self.period(period))
    amounts.each do |heading, amount|
      journal.add(heading(heading), App::Ledger::Books::Cents.new(amount))
    end
  end

  # Opens one journal of *amounts* by heading to *period*, and posts it.
  def post(period : String, amounts : Hash(String, Int32)) : Nil
    journal = App::Ledger::Books::Journal.open(self.period(period))
    amounts.each do |heading, amount|
      journal.add(heading(heading), App::Ledger::Books::Cents.new(amount))
    end
    journal.post
  end
end
spec/domain/ledger_spec.cr · replaces the generated spec
require "../spec_helper"
require "../support/ledger"

describe App::Ledger do
  it "adds up a period's posted lines" do
    Corretto.session do |_client, db|
      Caramel::Tenancy.with(account(db, "acme")) do
        LedgerSpec.post("2026-10", {"sales" => -500, "cash" => 500})
        LedgerSpec.post("2026-11", {"sales" => -100, "cash" => 100, "fees" => 7})
        LedgerSpec.draft("2026-11", {"fees" => 50})
        App::Ledger::Books::Journal.posted_in(LedgerSpec.period("2026-10")).raw.should eq(0)
        App::Ledger::Books::Journal.posted_in(LedgerSpec.period("2026-11")).raw.should eq(7)
        App::Ledger::Books::Journal.posted_in(LedgerSpec.period("2026-12")).raw.should eq(0)
      end
    end
  end

  it "groups a period's posted lines by heading" do
    Corretto.session do |_client, db|
      Caramel::Tenancy.with(account(db, "acme")) do
        LedgerSpec.post("2026-10", {"sales" => -500, "cash" => 500})
        LedgerSpec.post("2026-11", {"sales" => -100, "cash" => 100, "fees" => 7})
        LedgerSpec.draft("2026-11", {"fees" => 50})
        rows = App::Ledger::Books::Journal.by_heading_in(LedgerSpec.period("2026-11"))
        rows.map { |row| {row[:by].raw, row[:total].raw} }
          .should eq([{"cash", 100}, {"fees", 7}, {"sales", -100}])
      end
    end
  end

  it "groups for the bound tenant only" do
    Corretto.session do |_client, db|
      Caramel::Tenancy.with(account(db, "acme")) do
        LedgerSpec.post("2026-10", {"sales" => -500, "cash" => 500})
        LedgerSpec.post("2026-11", {"sales" => -100, "cash" => 100})
      end
      Caramel::Tenancy.with(account(db, "globex")) do
        LedgerSpec.post("2026-10", {"sales" => -9, "cash" => 9})
        rows = App::Ledger::Books::Journal.by_heading
        rows.map { |row| {row[:by].raw, row[:total].raw} }.should eq([{"cash", 9}, {"sales", -9}])
      end
    end
  end
end
  1. Run the domain spec
    Terminal · inside the application
    frappe corretto spec/domain/ledger_spec.cr

    Expect: Prints [w1] 3 examples, 0 failures, 0 errors, 0 pending, then Corretto: 1 of 1 workers passed.

Prove it

Prove the sums

Terminal · inside the application
frappe check
frappe corretto
frappe lint

Passing: frappe check exits 0. frappe corretto runs the three ledger examples beside the generated account examples and ends with Corretto: 1 of 1 workers passed. frappe lint exits 0.

Proved Run end to end by a check.

scripts/check page-sums creates ledger, makes Account its tenant, migrates, generates the domain, copies these files at the steps above, derives and applies the migration, runs the domain spec and the proof, and the website build holds every snippet on this page to those files.