I gave Anka's authentication role permission to bypass every row-level security policy in the database. I was trying to fix a much smaller problem: the auth library couldn't see the membership rows it needed. The broad permission worked, and my tests stayed green.
When I went back through it, I found that none of those tests connected as the auth role. I'd spent time testing company isolation without testing the connection that could ignore it. That was an uncomfortable thing to discover in an app built around keeping records trustworthy.
Anka is an invoicing project for Sri Lankan businesses. It uses Next.js, Postgres, Better Auth, Drizzle and React PDF. The parts I spent most time on were the records themselves: what happens after an invoice is issued, how its number is allocated, and whether a later edit can change what it used to say.
Keeping an issued invoice intact
An issued invoice can't be edited. A correction creates a credit note with its own serial, a reference to the original and a reason. The application role has no delete permission on the business tables, and triggers reject changes to issued invoices and their lines.
At issue time, the invoice also gets a snapshot of the supplier and customer details. Renaming a customer later changes the customer record, but the old document still prints the name it was issued with. The relationship remains available for filtering the register.
Serial allocation, PDF rendering and the database inserts happen in one transaction. If rendering fails, there shouldn't be a completed invoice record with no document behind it.
Getting the numbering right
I used a counter row for each company, project and series. A normal Postgres sequence wouldn't give me the rollback behaviour I wanted: taking a number and then failing would leave that number unused.
The counter advances through an upsert, holding the row lock until the transaction finishes:
insert into serial_counter (company_id, project_code, series, next_value)values (${companyId}, ${projectCode}, ${series}, greatest(${opening}, 0) + 1)on conflict (company_id, project_code, series)do update set next_value = serial_counter.next_value + 1returning next_value
The tests deliberately fail a transaction and check that the next allocation still returns 1. Another test makes 20 concurrent allocations and checks for exactly 1 through 20. Those tell me something more useful than seeing a few invoices numbered correctly in the browser.
The numbering continues across months. Credit notes have their own series, and project codes become fixed once they're printed on a document. These are small constraints, but changing them later would make the register harder to explain.
Money and dates
Amounts are stored as integer cents using BigInt. Prices arrive as strings and are parsed on the server; quantities use thousandths. Formatting happens when the amount is displayed or exported. The CSV formatter leaves out the visual grouping used in the PDF.
Dates are stored as YYYY-MM-DD calendar dates for Asia/Colombo. I format the components directly because an invoice date doesn't need a timezone conversion just to appear on a page.
Fixing the auth role
The auth library needed access to member before the connection had an actor or company set. I replaced the broad BYPASSRLS permission with a policy on that table for that role. The new tests connect using the auth role's own connection string and check what it can do. Two fail when the policy is removed, which is the evidence the original tests were missing.
I found a separate permissions problem too. In the version I used, organization creation was enabled by default. A signed-in viewer could create a company and become its owner, opening a route to account creation that I hadn't intended.
That didn't give the new company access to another company's records, but it did allow more account and company creation than it should. I split platform administration from membership roles. Being allowed to administer Anka is a different decision from being allowed to edit one company's invoices.
What still needs checking
Business queries go through withTenant, which establishes the actor and company in a transaction using a live membership row. The isolation tests use the restricted app role. They also check the database catalogue for business tables missing forced row-level security, so a newly added table can fail the suite.
Before using this for real invoices, I'd still want independent tax and document review. I also haven't measured PDF rendering under realistic concurrency. Keeping the render inside the transaction means a slow PDF holds the serial lock longer.
I'm happy with the decision to store the issued document and protect the record in the database. The auth-role mistake was a reminder to test each role through its actual connection, even when the application seems to work.