SQL Account Integration #

INTERNAL - DO NOT FORWARD

Classification: Restricted

SQL Account is the dominant SME accounting package in Malaysia (eStream Software), also sold in Singapore. It is a desktop Delphi application over Firebird with an HTTP API (“REST API 2.0”), served either by SQL’s hosted cloud or by an API service the customer runs on their own machine. tofu authenticates with a provider-issued key pair and signs every request with AWS Signature v4 — there is no OAuth flow, no dev portal and no scopes.

Everything below is what the code does; where SQL’s own documentation disagrees, the code follows what the live API was observed to do.

Connector module: libs/rust/bonsai-integration/src/accounting/sql_account/.

For the API-level reference — activation bodies, per-object table, account-type mapping, status-code map — see Integration APIs → SQL Account.

Support matrix #

Publish is covered in full under Publish; everything below the matrix applies to both directions unless it says otherwise.

Two trait methods are deliberately not implemented and return the connector’s not_yet_implemented error: fetch_payment and fetch_invoice_histories (SQL exposes no payment or document-history endpoint). fetch_organizations and fetch_location answer through the shared accounting::utils::empty helper. If a call returns “SQL Account: not yet implemented”, check it against that short list before treating it as a bug.

can_update_contacts is false — there is no general contact update for this provider, only create and TIN sync.

Bank statement publish is deferred by decision, not by omission — the reason, and why not to re-research it, is under Bank statement is deferred.

Is the customer hosted or on-premise? #

This is the first triage question for any SQL Account ticket, because the two modes have completely different failure surfaces.

There is no vendor tunnel and no agent-initiated relay. If the on-premise customer’s machine or internet is down, the integration is down, and there is nothing to fix on our side. The connector is built around this: a transport error maps to Unexpected, and only Disconnected / Unauthorized flip a row to UNAUTHORIZED. An unreachable on-premise tenant is never marked as having bad credentials.

⚠️ We have only ever tested the hosted mode. Our sandbox is hosted and no on-premise key has ever been seen, so the on-premise path — the mode with the highest support burden — is documented from the vendor’s setup guide rather than from observation. Treat an on-premise ticket as new ground and capture what you learn here.

Connecting #

🚨 Generating an API key locks that user out of the SQL Account desktop app.

“Once an API Secret Key is generated for a user, that user will no longer be able to login on SQL Account directly.” — setup-configuration

So the customer must create a dedicated API user first, or they lock a human out of their own accounting system. That user’s module rights determine what the key can touch, so grant AR, AP, GL / cash book, stock and maintenance rights. 🚨 The secret key is shown once and cannot be retrieved — a lost key means revoke and regenerate.

Path in the desktop app: Tools > Maintain User... > (the dedicated API user) > More > API Secret Key > Generate API Secret Key. The same dialog offers Test Connection, Download Postman Collection, and a copy button that yields a correctly-signed sample curl — the fastest way to validate any change to the signer.

The customer then pastes two values into tofu’s connect form:

tofu then runs two calls — POST /api/v1/integrations/api-key and PUT /api/v1/integrations/{integrationId} — with no OAuth step and no org-picker call. Bodies are in the API reference.

The connector is gated behind the sql_account_integration canary feature flag. Without it the provider does not appear in the integration picker. See Feature Flags.

Where the credentials live #

The signer needs both halves of the key pair on every request, so they are split by sensitivity rather than stored together:

A reconnect may re-key the same company but must not move the integration to another tenant. Both the connect handler and activate assert that.

There is no token lifecycle: no expiry, no refresh, no rotation, no revoke endpoint. expires_at is i64::MAX. Disconnect clears the stored secret; the key itself dies only when the customer regenerates or deletes it in their own installation.

Publish #

tofu publishes five document types. Nothing reaches a customer’s books by accident: a key has no scopes, so publish needed no reconnect when it shipped, and auto-publish starts OFF on first connect (should_disable_auto_publish_on_first_connect) — the customer turns it on deliberately.

Write the stock family, read the ledger family. /purchaseinvoice and /supplierinvoice are the same document under the same dockey — the first is the source document we write, the second the AP-ledger projection that carries paymentamt and outstanding. Status is always read from the ledger projection, never from the write endpoint, and outstanding is detail-only: it is absent from list responses. Direct expenses are the exception, being their own document; their external id is type-prefixed (pv:<dockey>) because /paymentvoucher also returns OR receipts and cash-sales-derived rows.

SQL numbers every published document itself (docno is omitted, not sent blank — an explicit "" is rejected with “Please enter an unique Document No.”), so the customer’s numbering format is respected and tofu never collides with it.

Republish is delete + create, and a paid document is refused #

SQL will happily accept a PUT on a document that has already been knocked off, but it refuses to DELETE one. Since a republish is delete + create (should_delete_before_publish), a paid or applied document has no safe path: updating in place would leave the payment knocking off a stale amount. So the connector refuses outright rather than updating, and it does so before writing anything — the ledger projection is read first and paymentamt / outstanding (or unappliedamt and sdsknockoff[] for a credit note) checked, so the user gets DOCUMENT_PAID_CANNOT_MODIFY / CREDIT_NOTE_APPLIED_CANNOT_MODIFY instead of SQL’s own message. The fix is the customer’s: remove the payment or knock-off in SQL Account, then republish.

Last write wins — there is no concurrency protection #

updatecount is mandatory on PUT (omitting it returns 500 "Field (updatecount) not found.") but its value is ignored — 0, 1 and 99 were all accepted against the same document. SQL’s documented optimistic-concurrency error therefore never fires. A republish silently overwrites an edit a bookkeeper made in the SQL Account desktop app in the meantime, and neither side is told. Record this when triaging “SQL Account lost my change”; it is a property of the provider, not a bug we can fix, and it is one reason auto-publish starts off.

Credit notes publish UNAPPLIED #

Probed 2026-10-19: POST /salescreditnote carrying sdsknockoff[] that names an unpaid invoice returns 200 and silently drops the knock-off — unappliedamt comes back equal to docamt and no knock-off rows exist. The structure that works on POST /supplierpayment does not work here.

So original_invoice_number and original_invoice_date cannot be carried: a published credit note sits unapplied in SQL Account and the customer applies it to the invoice there. This is user-visible and is stated in the customer-facing guide. Do not re-attempt the linking without new evidence from eStream.

Attachments are not supported #

eStream support confirmed the REST API cannot receive attachment bytes. The attachments field that appears on 112 objects is a masked blob/memo column, not a file API. The capability is off (.without_publish_attachments()), so accounting-sync never attempts the upload, and a direct attempt answers IntegrationError::Unimplemented. The source document stays in tofu. This question is closed — the one thing SQL will render is its own document as a PDF, which is not our scan.

Bank statement is deferred #

Not an omission and not a gap in the API. One paymentvoucher (PV) or receiptvoucher (OR) per transaction would work, but a published statement line is a PV in the same cash book as a published direct expense, with nothing distinguishing the two — so the same real movement can post twice and no read-back can tell the duplicates apart. The feature was dropped on that overlap. The CSV template (ENG-8455) remains the path for bank statements, and SqlAccount is deliberately absent from the BankStatement arm of supports_publish_for_type in both Rust and the webapp.

e-Invoice (LHDN MyInvois) guard #

submissiontype defaults from the contact record, so a published document could be filed with the Malaysian revenue authority as a side effect — after which irbm_uuid is set, the document is immutable, and our own delete-and-recreate republish breaks. Every publish of every type therefore sends submissiontype: 0 explicitly, and read-back asserts irbm_uuid is null and irbm_status is 0, raising DOCUMENT_SUBMITTED_EINVOICE when either is set.

⚠️ Only the first of the plan’s three guards shipped. DOCUMENT_SUBMITTED_EINVOICE is an ordinary BadRequest that fails the one publish: it does not halt auto-publish for the entity. The auto-publish skip for contacts whose submissiontype is non-zero was not built — 3.2.6 maps the field onto AccountingContact (is_configured_for_e_invoice), but nothing reads it. The manual-publish confirm dialog is likewise absent. So the unattended case the plan called the concentrated risk is currently unguarded, and the detection is after the fact rather than preventive. Whether an explicit document-level 0 really overrides a contact default is unprobeable by us (activating MyInvois needs a Malaysian registered entity), which is why the guard is belt and braces rather than a single check.

Publishing to an on-premise tenant #

The same reachability caveat as reads, and it bites harder here because a publish is a user action waiting on a reply. The tenant is typically an office desktop running the SQL API Service, so “unreachable” is a routine state — the PC is off, the ISP dropped, the port forward changed. A transport error is SERVER_UNREACHABLE and leaves the integration connected; it is never a credentials failure and must not be triaged as one. Tell the customer to check their own machine before anyone regenerates a key.

What is not sent #

Attachments (above); a document number (SQL numbers it); a credit note’s link to its original invoice (above); itemcode on lines — every line is coded by GL account, so items are read-side only; withholding tax; and a due date on the stock family, which has no duedate field (SQL derives it from the terms code).

The traps that bite #

1. 🚨 A browser User-Agent is mandatory. SQL’s edge silently 404s any request whose UA is not browser-like, returning an HTML page rather than a 403 — on writes as well as reads. reqwest’s default UA is 404’d exactly like Python’s. The connector pins a Chrome UA on the HTTP client, not per call, so activate and heartbeat carry it too. The official Postman collection hides this behind a pre-request script, so testing only in Postman will never reveal it.
2. 🚨 The status codes are inverted. 500 is normal validation (never retry it); 400 is either an empty collection or a not-found, told apart by the message; an HTML 404 is the WAF, not a missing document. Full table in the API reference.
3. 🚨 Validation is silent truncation, not rejection. An 11-character contact code returns 200 and persists 10. A header total that disagrees with its lines is accepted. A dimension code that exists in no master list is stored verbatim. docno has no length limit at all. SQL will not tell us when input is invalid — it quietly mangles or accepts it, so every field-length, total-consistency and reference rule belongs in the tofu validator, and publish must read back and compare.
4. Everything numeric is a string, in both directions, with no fixed decimal places on responses ("5010", "15487.5" — not "5010.00"), while requests use 2dp. Decode string → Decimal, never f64.
5. No date-range filtering, and a wrong filter name is silently ignored — it returns the whole collection with a 200. Every filtered read needs a test asserting the filter narrowed the result. Reads page the whole ledger 50 rows at a time with a client-side cutoff.
6. updatecount is mandatory on PUT but its value is ignored, so writes are last-write-wins with no protection against clobbering a concurrent desktop edit.
7. "----" is SQL’s universal “none” sentinel. It is the base-currency row in GET /currency, and the default row of every tag dimension and of locations. Filter it out of user-facing lists or the review form offers a currency literally named ----.
8. specialacctype ' ' is two literal spaces, not empty and not null.
9. A contact is a GL account. Every customer and supplier appears in GET /account as a sub-ledger row (specialacctype DR / CR), sharing one identifier space with the chart of accounts — which is also why a contact code is capped at 10 characters.
10. /paymentvoucher mixes directions — doctype PV (money out) and OR (official receipt, money in). Reads must filter, or receipts land in tofu as expenses.
11. /supplierinvoice mixes document types — PI-* bills and CP-* cash purchases, told apart by fromdoctype. External ids are type-prefixed.
12. List responses omit fields the detail returns — outstanding on documents, sdsbranch[] addresses on contacts, uom / refcost / refprice / barcode on items.
13. 🚨 GET /profile carries no currency field at all. The base currency is the isocode of the "----" row in GET /currency. This is the single most-missed fact about this provider.
14. 🚨 e-Invoice coupling. submissiontype defaults from the contact record, so a published document may be filed with the Malaysian revenue authority (LHDN MyInvois) as a side effect. Publish sends submissiontype: 0 (None) explicitly and lets the customer submit themselves.

Error keys #

The connector-layer keys live in accounting/sql_account/model/error.rs and mirror the sqlAccount object in config/i18n/en/error.json; a parity test checks both directions, so neither side can grow a key the other lacks. Validator rules are separate and carry their own invoiceValidation / directExpenseValidation keys.

One distinction is already real in the code and worth knowing: “your SQL Account server is unreachable” is not “your credentials are wrong.” A transport error maps to Unexpected and leaves the integration connected; only a 401 / 403 disconnects it.

Support runbook #

“The integration shows disconnected.” First ask hosted or on-premise. On-premise: is the SQL API Service running, is the PC on, is the public IP still theirs, is TCP 443 still forwarded? Hosted or on-premise: did anyone regenerate or delete the API secret key in Tools > Maintain User...? A regenerated key invalidates the stored one and the customer must reconnect. Remember the key is one-shot — they cannot read the old one back.

“The connection worked yesterday and now everything returns not found.” Suspect the User-Agent. An HTML 404 from SQL’s edge means the request never reached the API. This should not happen in production — the UA is pinned on the client — but it is the first thing to check after any change to the HTTP client or to an outbound proxy.

“Our chart of accounts is missing accounts.” Expected. tofu excludes _XX_ account-type header rows, specialacctype DR / CR (every customer and supplier, which are sub-ledger accounts in SQL), DC / CC control accounts, and any account with children — SQL itself refuses a posting to a parent with 500 "There is child account for account 400-000. Posting aborted.". In the sandbox that takes 130 rows down to 85 postable accounts.

“A contact won’t save” / “the contact code is wrong in SQL Account.” Contact code is silently truncated to 10 characters. SQL returns 200 and stores the truncation, so nothing surfaces as an error. Check the code length first.

A contact with no code is not refused: tofu omits the field and SQL assigns one from the book’s own template — a new “Z” supplier became 400-Z0001 (probed 2026-10-20). The assigned code comes back in the create response and is what tofu stores, because publish writes it into every document. Sending "" instead of omitting would be rejected, the same trap as docno.

“Amounts are a cent out.” Every amount is duplicated as a local* base-currency figure, and SQL does not reconcile a header total against its lines — it accepted docamt: "9999" against a single "100" line. tofu computes and keeps both sides consistent; a mismatch is ours, not SQL’s. Tax is line-by-line, and inclusive-vs-exclusive is per line via each line’s taxinclusive flag.

“Publish failed: this document has payments in SQL Account.” Working as designed. A republish is delete + create, and SQL refuses to delete a document that has been knocked off, so tofu refuses before writing rather than leaving a payment against a stale amount. The customer removes the payment or knock-off in SQL Account and publishes again. Same answer for a credit note that has been applied (unappliedamt < docamt).

“I published a credit note and it isn’t applied to the invoice.” Expected, and permanent until eStream changes the API. POST /salescreditnote accepts an sdsknockoff[] and silently discards it, so credit notes always land unapplied and the customer applies them in SQL Account. See Credit notes publish UNAPPLIED.

“Someone edited the document in SQL Account and our republish wiped it.” There is no protection against this. updatecount is required on a PUT but its value is ignored, so writes are last-write-wins and neither side is warned. It is one reason auto-publish ships off.

“The link doesn’t open my document.” Correct, and permanent. SQL Account is a desktop application, so there is no per-document URL anywhere. The webapp links to connect.sql.com.my, the cloud client’s company picker, flagged listing-only — the same treatment AutoCount gets — and shows the document number as text for the user to search on arrival. Use SQL’s own nouns when talking to the customer — “Supplier Invoice”, “Customer Invoice”, “Payment Voucher”.

An on-premise customer has no cloud client, so that link does nothing for them. Landing in the customer’s own company would need the client token out of the picker URL (base64 of <client no>\0c\0<database>); it is in no API response — /profile has no such field — so it would have to be collected at connect time and stored per tenant.

“Can we attach the scan to the document?” No, and this is settled: eStream support confirmed the REST API cannot receive attachment bytes. SQL’s attachments field is a masked blob/memo column, not a file API — every captured record had it null, and it appears on objects as meaningless as area and agent. The capability is off, so publish never tries; the source document stays in tofu.

Links #

• Integration APIs → SQL Account — activation bodies, per-object table, account-type mapping, status codes
• AutoCount Cloud Integration and Bukku Integration — the other two Malaysian API-key providers
• Feature Flags
• Vendor: API setup and configuration · on-premise setup · API FAQ