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.
| Contract | Value |
|---|---|
| Needs | A Caramel installation set up as in Your first application; the recipe creates ledger, its App::Account tenant and its Ledger domain (SweetTalk) |
| You will have | A 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 |
| Status | Run end to end by scripts/check page-sums |
| Proved by | frappe check · frappe corretto · frappe lint |
Where the change goes
app/domain/ledger.crJournal, the sums and groupsdb/migrations/Tables from the diffspec/support/ledger.crPosts or drafts a journalspec/domain/ledger_spec.crThe sum and the groupsDecide before you generate
- Declare the sum on the aggregate that holds the rows
sumsandgroupsare declared besidefindsandcounts, with the samewhere:andsatisfying:, andof:names the attribute they add up: one of the aggregate’s own by symbol, or one of its entities’ asLine.amount. Each runs oneSELECTover the live rows of the bound tenant, so no SQL and no tenant predicate is written by hand. The attribute is a whole number, aFloat64, or a value that wraps a whole number or a decimal and has no bound a sum could break:Centshere has none, because a credit is negative. - Keep a projection for a sum read on every request
A
sumsadds 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 bywhenever(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.
- Create the application
Terminal · after machine setup frappe new ledger cd ledgerExpect: Prints
Created ledger., thenledger is configured at https://ledger.caramel. - Make Account the tenant
Terminal · inside the application frappe make tenancy AccountExpect: Prints
Generated PATHfor each file it writes or edits,app/models/account.cramong them, then aNext:block that listsfrappe migrateandOpen /accounts/new to sign up the first tenant. - Apply the tenant’s migration
Terminal · inside the application frappe migrateExpect: Prints
Applied 1 migrations., thenThe database matches the declared schema. - Generate the domain
Terminal · inside the application frappe make domain Ledger Books --means="what each heading was posted in each period"Expect: Prints
Generatedfor each of the four files it writes,app/domain/ledger.crandspec/domain/ledger_spec.cramong them, then aNext:block that listsWrite the domain in app/domain/ledger.cr.,frappe db diff --name create_ledgerandfrappe 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.
# 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
endEach 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.
| Condition | Outcome |
|---|---|
Journal.posted_in(period) for a period no journal was posted to | returns Cents.new(0), the sum of no rows |
Journal.by_heading in a tenant with no posted journal | returns [] |
A sum past Int64 | raises ArgumentError from to_i64 |
sums of a value with an upper bound, or a lower bound other than 0 | fails compilation |
groups that adds up Line.amount and groups by :period, an attribute of the journal | fails compilation: narrow with where: instead |
A finds block that sums, counts or groups its rows, or names SugarORM | fails compilation |
- Derive the migration
Terminal · inside the application frappe db diff --name create_ledgerExpect: Prints
Wroteand the path of one migration ending in_create_ledger.cr. It createsbooks_journalsandbooks_journal_lines, each with anaccount_id. - Apply the migration
Terminal · inside the application frappe migrateExpect: Prints
Applied 1 migrations., thenThe 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.
# 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
endrequire "../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- Run the domain spec
Terminal · inside the application frappe corretto spec/domain/ledger_spec.crExpect: Prints
[w1] 3 examples, 0 failures, 0 errors, 0 pending, thenCorretto: 1 of 1 workers passed.
Prove it
Prove the sums
frappe check
frappe corretto
frappe lintPassing: 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.
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.