The WordPress plugins: a shop, two portals, and what the ledger must offer them
WordPress runs about two-fifths of the web and WooCommerce is one of the two largest shop platforms by count of stores. Every one of those stores must get its orders into a ledger, and today that means a paid connector to QuickBooks or Xero. A free connector to a free ledger, listed in the wordpress.org repository, is a different offer. This chapter says what the repository gives and demands, sets out nine plugins in order of value, and — the part to work on first — lists the limitations in SQL-Ledger as it stands that any of them would meet on day one.
Would a free plugin in the wordpress.org repository put SQL-Ledger in front of the WooCommerce stores that today pay for a connector to QuickBooks or Xero, and what would it meet in our code on its first day? The repository offers reach, a public install count and search for the firm’s own name, in return for PHP under the GPL, a review, a support forum and a maintainer. Nine plugins are ranked; four fold into one, SQL-Ledger Connect. Twelve limitations stand in the way, verified against the code: four are one piece of work, the JSON API with tokens, events and external references, and three are old repairs, numbers assigned at posting, unique customers, and money held as numeric rather than float. Build the API, then the connector.
11.1What the repository gives, and what it demands
Three things are on offer. Reach where the merchants are: the WooCommerce store owner who has never heard of SQL-Ledger searches the plugin directory for “accounting” and finds connectors at $20 to $100 a month; a free one to an open-source ledger stands out in that list. A trust signal that cannot be bought: the plugin page shows active installs, ratings, the WordPress version it was tested with and the date of the last update — the same proof of life a public repository gives, in front of a non-technical audience. And search, mostly for the firm’s own name: plugin pages rank, but “woocommerce accounting integration” is crowded; what the page will own is “sql-ledger woocommerce” and the long tail. Realistic exposure is dozens of installs a month, not thousands, and each install is a self-selected lead for hosting.
The demands are real and permanent. The code is PHP under the GPL, a second
codebase in a second language. The wordpress.org review takes days to weeks the first time, and has at times taken months, and asks for a readme.txt that documents every external service the
plugin calls — the SQL-Ledger API will be one — and forbids sending
data anywhere without the user’s consent. The plugin’s name may not begin with “WooCommerce” and may not contain “WordPress” at all (“WP” is the accepted substitute); SQL-Ledger Connect for WooCommerce is allowed. There is a public support forum that expects answers,
and WooCommerce changes underneath every year — high-performance order
storage, the block checkout — so the plugin needs a maintainer, not an
author. Distribution is by Subversion, which is its own small penance. And the
SQL-Ledger name is a mark of its author’s company; the ISLNA seat makes the
conversation easy, but have it before publishing under it.
A WordPress plugin speaks HTTP. SQL-Ledger has no HTTP interface: every screen is a CGI form, every posting runs inside a request that also renders HTML. Chapter 6 item 1, the token-authenticated JSON API with webhooks, must exist before any plugin is written; otherwise the plugin becomes a second implementation of posting logic in PHP, which is how connectors rot. Build the API once and every plugin below is a thin client — and so is every other integration in chapter 6.
11.2The nine plugins
In order of value. The last column is the audience each one reaches that the others do not.
| # | Plugin | What it does | Who it reaches |
|---|---|---|---|
| 1 | WooCommerce sync | Orders become sales orders or invoices; customers and products sync both ways; stock on hand flows back to the store; payouts from Stripe or PayPal through Woo post as receipts net of fees; refunds become credit notes. | Every WooCommerce store. The one that sells hosting; ship it first, free. |
| 2 | Customer portal | A block or shortcode: the customer logs into the WordPress site and sees open invoices, statements, PDFs, and a pay-now button (chapter 1 item 2). | Every service firm with a WordPress site; QuickBooks users pay third parties for this. |
| 3 | Vendor portal | Suppliers see purchase orders, acknowledge them, upload their invoice against a PO into the receipt inbox (chapter 1 item 4), and see payment status. | Manufacturing and distribution, where SQL-Ledger already wins (chapter 5); little competition. |
| 4 | Quote request and order form | A form block creates a quotation and the customer record; the reply returns as a PDF quotation; acceptance turns it into an order. | Firms whose site is a brochure, not a cart; the on-ramp. |
| 5 | Live catalogue block | Price and stock for a part from the ledger, on any page, with an enquiry button. | Trade suppliers with price lists on the web. |
| 6 | Subscriptions bridge | WooCommerce Subscriptions or a membership plugin drives recurring invoices in the ledger, so the ledger is the source of truth for renewals and dunning. | Membership sites, SaaS run on WordPress, clubs. |
| 7 | Time and project portal | Staff or clients log time against SQL-Ledger projects; it feeds the unbilled time of chapter 2 item 2 and the profitability page of chapter 3. | Agencies and consultancies on WordPress. |
| 8 | Forms bridge | Gravity Forms, WPForms and Contact Form 7 submissions create customers, leads or quotation requests. | What people use Zapier for today; cheap once the API exists. |
| 9 | Donations for non-profits | GiveWP and similar post each gift as a receipt against the donor as customer, with the acknowledgement letter from a template. | Charities: SQL-Ledger ships the UCOA and Form 990 charts, which no WordPress accounting plugin does. |
Fold 1, 2, 4 and 8 into one plugin — SQL-Ledger Connect: sync, portal, forms — so there is a single install count to grow, one support forum to tend and one review to pass. Keep 3, the vendor portal, as a second plugin for the industrial niche, and 6, 7, 9 as later modules of the first. Everything works against a self-hosted SQL-Ledger as well as Ledger123 hosting; the hosted-only extras (bank feeds, backups, the AI intake) stay on the panel, which is the free-tier line of chapter 10 drawn again.
11.3The limitations in SQL-Ledger today
This is the section to work from. Each limitation is something a plugin above would hit, stated as it is in the code now, with what the fix is and which plugin needs it first. Twelve items; the first four are the foundation.
| # | Limitation, as the code stands | Which plugins it blocks | The fix |
|---|---|---|---|
| Foundation | |||
| 1 | No HTTP API. Every operation is a CGI action reached by posting a form to a script; the response is HTML. There is no way to create a customer or an invoice from outside without impersonating a browser. | All nine. | Chapter 6 item 1: SL/API.pm on the application server, calling the same SL::* methods as the screens — once the closed-period check moves out of the screens into those methods, as backlog item 45 warns, or the API posts into closed periods. JSON in, JSON out. |
| 2 | No events. Nothing in the ledger announces that an invoice was posted, a payment received or stock changed; a plugin would have to poll every table on a timer. | 1, 2, 3, 6 (a paid invoice must update the portal and the store at once). | Webhooks in the API: a webhook table of URL and event, fired from the posting methods after commit, with retries and a log. |
| 3 | No credential but a login. Access is a member row with a password and an acs exclusion list; there is no token that can be issued to a plugin, scoped to a role, and revoked without changing a person’s password. | All nine. | API tokens tied to a login and a role, in the registry database; the roles of chapter 4 apply to a token as to a person; the read-only accountant role becomes the portal’s role. |
| 4 | No external identity on records. customer, vendor, parts, oe and ar carry their own numbers (customernumber, ordnumber, ponumber) but no column for “the id this record has in WooCommerce”. A sync cannot tell an update from a duplicate. | 1, 5, 6, 8. | An extref table (table name, record id, system, external id, unique on system and external id) so any record can carry any number of foreign keys, and the API accepts upsert by external id. |
| Identity and matching | |||
| 5 | Customers are not unique by anything. customer.email is a free text column with no unique index — sql/Pg-indices.sql puts unique indexes on the chart, employee logins, GIFI, projects, parts groups and languages, and on nothing a plugin writes — and the screens will create the same customer twice without comment. A store sends the same shopper on every order. | 1, 2, 4, 8, 9. | A case-insensitive unique index on customer and vendor email where not null, a merge tool for the duplicates that already exist, and the API matching on email, then customer number, then external id. |
| 6 | Customers cannot log in. A customer is a record, not an account; the storefront keeps its own shopper accounts in its own SQLite tables. The portal needs to prove that the person on the WordPress site is customer 4711. | 2, 3, 7. | Either the API issues a portal token per customer, stored on the customer record and exchanged by the plugin for a WordPress login, or the storefront’s account table moves into the ledger as a portaluser table used by both. The second is right and also fixes the storefront. |
| 7 | Documents cannot be rendered without a screen. A PDF or HTML invoice is produced by bin/mozilla/io.pl print_form, which reads the form the user is looking at and writes to the browser or the spool; there is no function that takes an invoice id and returns the document. | 2, 3, 4, 9. | A render_document(type, id, format, language) in SL/Form.pm or a new SL/Doc.pm that loads the record, fills the template and returns bytes; the screens call it too. Then GET /invoice/4711.pdf in the API. |
| Posting behaviour | |||
| 8 | Numbering and posting are entangled with printing. An invoice number is assigned at printing or e-mailing as well as at posting (io.pl print_form and IS::post_invoice each call update_defaults), and neither path checks that the number is unused, so a sync can create a numbered invoice that is not in the ledger, or post one with a number already used. This is the fifth most-requested fix in the archive, with sixteen mentions, behind only reconciliation, the webshop, installation and upgrades: “gapless invoice numbers assigned at posting; print and e-mail should always post”. | 1, 6. | Assign the number inside the posting transaction from a per-type sequence; printing an unposted document is a proforma with no number. Backlog and archive agree. |
| 9 | No idempotency. Post the same request twice — a store retrying after a timeout — and the ledger has two invoices. There is nothing in the posting path that recognises a repeat. | 1, 6, 8, 9. | An idempotency key on every API write, stored with the resulting id for 24 hours; a repeat returns the original result. |
| 10 | Amounts are floating-point. Monetary and quantity columns are float (backlog item 43), so a Woo order total of 119.99 can post as 119.98999 and the store and the ledger disagree by a cent on a refund. | 1, 6, 9. | Convert the money and quantity columns to numeric; until then the API rounds to the dataset’s precision on the way in and compares with a tolerance on the way out. |
| Data the plugins need that the ledger lacks | |||
| 11 | No stock reservation and no variants. An open sales order does not reserve stock, so two stores can sell the last unit; and a part has one description, one SKU and no attributes, so a T-shirt in four sizes is four parts or a kit. The storefront treats os28004-CAM-BLK as a variant of os28004 by naming convention alone, which the ledger knows nothing about. | 1, 5. | Allocated quantity on the part from open orders (Supply/Demand already computes it; expose it as available in the API), and a partvariant table of parent part, attributes and SKU, used by the storefront and the sync alike. |
| 12 | Web content is only partly escaped on output (backlog item 49): Form::escape_html exists and most screens use it, but not all — the notes field on the AR screen is emitted raw — and an order from a store carries text the merchant did not write: product names, notes, addresses. A customer note of </textarea><script> renders there. | All that write, before any goes live. | Finish the escape_html sweep across every user-supplied value in the UI layer; it is item 49 for a reason, and a public write path makes it urgent. Chapter 9’s rule — content is data, not instruction — applies here too. |
Four of the twelve are one piece of work — the API with tokens, events and external references — and the archive’s feature requests, the backlog and this chapter all point at the same three underneath it: gapless numbering at posting, unique customers, and numeric money. Fix those and the plugins become thin; skip them and every plugin carries a workaround for each, in PHP, forever.
11.4The first plugin, specified
SQL-Ledger Connect for WooCommerce, version one, in the order it should be built.
- Settings page: the API URL, a token, a test button, the mapping of Woo payment gateways to SQL-Ledger payment accounts, the tax mapping, and the choice of “order becomes a sales order” or “order becomes an invoice”.
- Product sync, ledger to store: parts flagged sell online (a flag the ledger lacks; the storefront keeps it in its own
shop_itemtable, so it moves toparts) appear as Woo products with price, description, image (parts already carryimageanddrawing) and available stock; a webhook on stock change updates the store within a minute. - Order sync, store to ledger: on order paid, upsert the customer by email and external id, create the sales order or invoice with lines matched by SKU, post the payment against the gateway’s account with the fee to bank charges, and write the ledger’s number back onto the Woo order as a note. Idempotent on the Woo order id.
- Refunds: a Woo refund becomes a credit note and a payment reversal.
- Customer portal block:
[sqlledger_portal]shows the logged-in customer’s open invoices and statements, with PDF download through the rendering function of limitation 7 and the pay-now link of chapter 1. - Forms bridge: a hook that any form plugin can call to create a customer or a quotation request.
- Diagnostics: a log page of every call and webhook with its result, because the support forum will ask.
11.5Exposure, honestly measured
What to expect, so that the plugin is judged fairly. In the first year: a few hundred active installs if the sync works and is reviewed well; perhaps a tenth of those trying Ledger123 hosting rather than self-hosting; a plugin page that ranks first for the firm’s own name plus “woocommerce” and on the third page for the generic phrase. The larger returns are indirect: the install count on the trust page, the support-forum threads that are indexed as answers, and the fact that a merchant who installs a connector has already decided to change ledgers. Two alternatives, for the record: the Shopify App Store reaches fewer stores with more revenue each, and Intuit now ships its own Shopify connector; Automattic’s WooCommerce Marketplace is curated, with a seller review. Do wordpress.org first; it is free and the audience is largest.
One free plugin — sync, portal and forms — in the wordpress.org repository puts SQL-Ledger in front of every WooCommerce store and lends the firm the trust of a public install count; it needs the API first, and under the API three old repairs the archive has asked for since 2009: numbers at posting, unique customers, and money that is not a float.
11.6Sources
The limitations are read from the code and schema in this repository; the repository rules from wordpress.org’s plugin guidelines as generally published (naming, GPL, external-service disclosure, Subversion distribution). Feature-request counts are from the mined ledger123 archive. Verify the current guideline text at the time of submission.
- WordPress.org, Detailed plugin guidelines and Plugin developer handbook — developer.wordpress.org/plugins/wordpress-org/detailed-plugin-guidelines/
- WooCommerce developer documentation: high-performance order storage, the block checkout, REST API — developer.woocommerce.com
- Ledger123, SQL-Ledger 3.2.12 improvement backlog (
doc/todo.md), items 42, 43, 45, 49; Interchange feature requests (doc/features.md); the archive’s feature-request table. - This repository:
sql/Pg-tables.sql,sql/Pg-indices.sql,SL/CT.pm,SL/IS.pm,bin/mozilla/io.pl,interchange.pl.