Ekwo OS — open-source accounting as Postgres migrations, installable on your Supabase project

[fpbordes]

Ekwo OS is an open-source (AGPL-3.0) accounting core written as Postgres migrations: the schema, the posting rules, VAT and the reports. There is no application server. On Supabase, the REST API and row level security are the interface; on any Postgres, the same SQL runs through a direct connection.

What is in main today (v0.5.0):

• 124 migrations: 61 tables and about 200 SQL functions, with row level security on every table.
• Balancing, period locks and the immutability of posted entries are enforced in the database, not by a client.
• 44 country packs — Estonia, Japan, Senegal and the United States, for example — each one versioned data with a golden year of books. The aim is 150 countries or zones; a pack is a pull request, and contributions are welcome.
• 12 file-format libraries under MIT (FEC, Factur-X, Peppol UBL, camt.053…).
• An MCP server, listed in the official MCP registry as ai.ekwo/mcp, that acts as the signed-in user and refuses a service_role key.
• 3,788 tests on PGlite, plus an end-to-end run against a hosted Supabase project for each release.

Install: npx ekwo-os init on an empty project.

Not done yet: no web interface (CLI and MCP only), and importing books from other software is in progress. If you want to try it on your own books when that lands: https://ekwo.ai

Feedback on the schema is welcome.
https://github.com/Ekwo-ai/ekwo-os

[https-shubhamsahu]

Read through the migrations. Keeping every rule in functions and triggers so service_role can't walk around it is a good call, and the fresh-session test from 20260918140000 catches a NULL bug most schemas never find. Some notes from the audit-trail side, since that's where an auditor will push:

1. The purge exception is a flag, not the function. audit_log_is_append_only() lets any DELETE through when ekwo.audit_purge = 'on', and the date check plus the audit_log_purged record only exist inside purge_audit_log(). So the owner on a direct connection can set local ekwo.audit_purge = 'on' and delete any rows, with no cutoff and no trace (or just alter table audit_log disable trigger ...). That's normal Postgres, but the comment "table owner included" promises more than a trigger can deliver. "Clients can't rewrite history" is the accurate claim.

2. ekwo.* settings can be set by any session. Custom placeholder GUCs have no privileges, so any role can SET ekwo.installing = 'on' / ekwo.year_end_entry = 'on'. PostgREST can't reach that, so the Supabase path is fine. In the "any Postgres, direct connection" mode, though, a second login role (a reporting user, a BI tool, a restricted bookkeeper role) has auth.uid() null and no ekwo.api_key, so one SET makes is_installer() true for it. Two ways to close it:

• gate the installer on membership too: pg_has_role(session_user, 'ekwo_installer', 'member')
• ekwo.year_end_entry is harder, since close_fiscal_year() / opening_balance() are invoker functions and the guard can't tell who set the flag. One option: make them security definer and have entries_guard_kind() also require current_user to be the owning role. Inside a definer function current_user is the owner, and triggers fired from there see that; a direct caller sees their own role. The trade-off is that they then run past RLS, so their own capability checks become the only gate.

3. If you want tamper-evidence and not only tamper-resistance, hash-chain audit_log.

• Add prev_hash / row_hash bytea. In audit_record(), take a per-company lock (pg_advisory_xact_lock(hashtextextended(coalesce(company_id::text, ''), 0)), or for update on a one-row-per-company head table), read the last hash and store sha256(prev_hash || canonical_bytes). Without the lock, two concurrent transactions read the same head and fork the chain.
• Build canonical_bytes from an explicit column list, not the whole row, so adding a column later doesn't change old hashes. Leave id out: your archive import redraws identity values, so a chain over id would break on every restore. You've already solved the jsonb trailing-zero problem for values_sha256, and the same canonical form works here.
• Add verify_audit_chain(company_id), and put the head hash somewhere the operator can't rewrite: the fiscal-year close output, the archive manifest plus a copy kept off the box. Anyone who edits the rows can recompute a chain; what they can't do is match a head that already left the database.
• sha256(bytea) has been in core since PG 11, so it runs on PGlite without pgcrypto.

[fpbordes]

This was the most useful thing anyone has done for the schema. All three
points landed; two are fixed, and the third is the one I want to talk about.

1. The purge exception. You are right that the claim was wider than a
trigger can keep, and that "clients can't rewrite history" is the accurate
one. That sentence is now the promise everywhere — the comment on the table,
its guard, its policy, the decision record, the tests, and the note the MCP
server hands an agent on every read of the trail, which had been telling
agents that nothing removes a row.

Within that, the purge moved into the table rather than staying in the
function. ekwo.audit_purge now carries the cutoff as a date instead of
'on': the row guard lets a row go only if it is older than that date, the
date is well-formed and not in the future, and the role deleting is a member
of the table's owner. A separate statement trigger with a transition table
writes audit_log_purged after every delete — cutoff, row count,
session_user — so a purge done by hand leaves the same line the function
would have left, and disabling the guard by name leaves the recorder on.
truncate is refused. ekwo doctor now requires the guard by name and
reports any trigger of the trail that has been disabled, since that is the
one limit no trigger can cover.

Two tests pin the limit rather than hide it: the owner disables the guard and
the purge is still recorded; the owner disables everything and there is no
trace. They exist so "owner included" cannot come back.

2. The settings. Fixed, and I took one of your two routes and refused the
other — the second for a reason I would like your read on.

is_installer() now also asks pg_has_role(session_user, <owner of public.companies>, 'MEMBER'). I gated on the owner rather than on a role of
our own: a dedicated role has to be created per cluster and granted to
whoever migrates, and on a hosted Postgres the migrating login cannot always
grant itself one — while the owner exists on every installation and is the
one role the exemption cannot widen, since it can already disable every
trigger. session_user, not current_user, because half the callers are
definer functions.

Your second login is now a test. Before the fix it got is_installer() = true and issued itself a machine key carrying every capability of a company
through create_api_key(), which is definer. That is the test that failed
first and passes now.

On ekwo.year_end_entry I did not make the three year-end functions
security definer. The guards now honour the flag only for the installer or
for a caller holding the capability of the act — year_end.close, or
entries.post for an opening balance — and otherwise answer exactly what the
capability guard of the act would have answered. It closes the audited case
(a login with no identity gains nothing from the flag) without putting three
functions that read and write a whole fiscal year of a company past row level
security, where their own checks would become the only gate. What your route
closes in addition — someone who does hold year_end.close writing a
closing entry by hand instead of through close_fiscal_year() — is reachable
only from a direct connection, and that brings me to the thing your notes
made me see.

The bigger hole, which is not fixed. On a direct connection auth.uid()
reads request.jwt.claims, which is a setting like any other. A login
holding the grants of authenticated can declare itself any member and get
their rights; member ids are not secret. A test proves it. My fix closes the
installer exemption and the year-end flags for a login without an identity;
it does not close impersonation. Over the API this does not exist —
PostgREST sets the claims from a signed token — so it is the self-hosted
path, and closing it properly (having auth.uid() return null unless
session_user is the authenticator or the owner) touches the CLI and the
self-hosted MCP server, both of which set that claim themselves. So it is
written down in SECURITY.md as something the schema does not hold against,
next to the database owner, rather than patched at the edge. If you see a way
to close it that does not turn every direct-connection tool into a special
case, I would take it.

3. The hash chain. Not started, on purpose, and your framing is why: it
buys tamper-evidence only if the head has already left the database. Whoever
can edit the rows can recompute a chain; what they cannot do is match a head
that is somewhere else. So it is one feature with the publication of the
head, not before — the fiscal-year close output and the archive manifest are
the two places it belongs, exactly as you said.

Three things I found while costing it, in case they are useful to you:

• The per-company lock is the decision. Held until commit, it serialises
  every transaction that touches a company's configuration or posts a
  document on that company's chain. That is a load question before it is a
  design one, and I would rather measure it than guess — tests/load posts
  168,059 ledger lines, so there is somewhere to measure it.
• The purge cuts the start of the chain. The audit_log_purged row would
  have to carry the hash of the last purged row as the new anchor, or
  verification fails after every retention run.
• The archive would have to verify the hashes, not recompute them —
  otherwise an import launders any edit. And import_company() writes
  audit_log with the triggers off, so who writes prev_hash on an import
  is an open question rather than a detail.

sha256(bytea) in core since 11 is exactly why this is doable on PGlite, so
thank you for that too.

All of it is on main: the two migrations are
20260929094126_a_setting_nobody_owns.sql and
20260929103358_a_trail_no_client_rewrites.sql, and the whole change is
Ekwo-ai/ekwo-os#37. The changelog credits you by this
handle, because SECURITY.md promises credit unless the reporter would rather
not — tell me if you would prefer a name, or nothing at all, and I will change
it.

[https-shubhamsahu]

Thanks for going this far with it, and for writing the limit into SECURITY.md instead of leaving it unsaid. On the impersonation hole, here's the shape I'd try. I haven't run it against your tests, so treat it as a sketch.

auth.uid() answers whatever the session declares, and you can't redefine it because Supabase owns auth. Your policies and guards don't have to call it directly, though. Put one function in front of it and let that function decide whether the declaration counts, based on session_user, which a session can't set:

create or replace function ekwo.uid() returns uuid
language sql stable security definer set search_path = '' as $$
  select case
    -- PostgREST: the claims come from a token it verified
    when session_user = 'authenticator' then auth.uid()
    -- the owner (CLI, migrations): it can already disable every trigger,
    -- so letting it declare a member widens nothing
    when pg_has_role(session_user,
           (select c.relowner from pg_catalog.pg_class c
             where c.oid = 'public.companies'::regclass), 'MEMBER')
      then auth.uid()
    -- any other login: only the identity the owner granted it, else null
    else (select li.user_id from ekwo.login_identity li
           where li.login = session_user)
  end
$$;

ekwo.login_identity (login name primary key, user_id uuid) is writable by the owner only. Why I think this avoids a special case per tool:

• The CLI keeps doing exactly what it does now (set the claims, set local role authenticated). session_user stays the login it connected as, and on the self-hosted path that's the owner.
• The self-hosted MCP server already acts as one fixed user (actAsUserId). If its dbUrl is a separate login, that's one login_identity row naming the same user, and whatever it declares on top can't move it anywhere else.
• Your reporting/BI login gets no row, so ekwo.uid() is null and every policy fails closed. With a row it's that one member. That's what SECURITY.md currently tells people to approximate with grants.

Costs and caveats:

• It's a sweep. I count about 127 auth.uid() calls in the migrations, so it would be one migration that recreates the policies and guards. Write it as (select ekwo.uid()) so it runs once per statement, and add a test that fails on any bare auth.uid() in a policy or guard so it can't creep back. auth.jwt() and auth.role() need the same treatment wherever a guard reads them.
• Don't hard-code 'authenticator' if self-hosted PostgREST might log in under another name. Keep the list in an owner-only table, not a setting, for the same reason as your point 2.
• On Supabase, other services may also set claims and switch to authenticated under their own logins (I believe Storage does, for its object policies). If any of your policies get evaluated through one of them, that login belongs on the trusted list. Check which session_user actually shows up before you flip it on.

If a non-owner tool ever has to act for arbitrary members, the other route is a signed claim: the tool presents a token HMAC'd with a secret that lives in an owner-only table, and a definer function verifies it. HMAC is just two sha256() calls over padded keys, so it works on PGlite without pgcrypto too. It's more moving parts, though, and the session_user gate covers the cases you described.

[https-shubhamsahu]

Crediting me by my GitHub handle is great, thanks for asking. And thanks for turning the fixes around so quickly!