OpenSparrow v4.0 adds a JSONB column type, public read-only table sharing, and many-to-many editing directly on the grid — plus a grid performance refactor: in-place cell updates, a side-fetch cache, and server-side sort and filters for large tables.
JSONB columns, validated before save
The schema editor offers jsonb alongside the existing types (includes/admin/schema.php). JSON entered into such a column is validated client- and server-side before it reaches SQL (validate_jsonb_column in includes/api_helpers.php); the value is bound with a ::jsonb cast through pg_query_params. Workflow form template fields understand the new type too. No migration — adding the column to an existing table is the usual schema-editor ALTER TABLE, done on demand per table.
Public read-only table sharing
System → Sharing exposes one table through a public link — anyone with the link views the table grid without logging in, like sharing a spreadsheet on OneDrive. The page is public/share.php, the data endpoint public/api/share.php; config lives in spw_config.shared_tables.
Security details that matter:
- The token is never stored in plaintext — only
token_hash(viasecret_hash), and lookups usehash_equalsfor constant-time comparison (SharedTokenManager) - Hidden, system, and owner-restricted tables cannot be shared; the shared schema is stripped of subtables, many-to-many relations and images
-
m2m_optionsgatesother_table, so a share link cannot be pointed at an unrelated table; stale entries are pruned automatically on save - The page sends
X-Robots-Tag: noindex, nofollow
The grid, faster
Four changes shipped together (includes/frontapi/list.php, public/assets/js/grid/):
-
Row cap: full-table fetches carry an explicit cap —
MAX_LIST_ROWS(default 1000, overridable per table viainitial_limitin Schema → Table Properties). No loader can request an unbounded set. - Side-fetch cache: the four auxiliary loaders (comment counts, subtable counts, m2m rows, image rows) fetch once per page signature and re-apply from their stores on re-render — two renders in a row don't refetch.
-
In-place cell updates: saving a cell mutates the local dataset and re-renders the grid — no full
loadTableround-trip. - Hybrid client/server view: if the response is truncated, the grid switches to server-side search, sort, and column filters — one combined request per change; "Load more" chunks continue the server ordering.
On top of that, the grid supports multi-column sorting (up to three rules with priority icons), and virtual columns gained conditional icons.
Many-to-many editing on the grid
M2M relations are now edited in a popover picker right on the grid, backed by two new API actions: m2m_sync (writes, audited like any record change) and m2m_options (reads, access-gated). The popover shares the same 1-second hover delay as the image preview.
Backup retention, calendar week view, config autosave
-
Backup Tables → Global Settings gained retention: keep the newest N backups, drop backups older than X days (both optional), a list of stored backups, and on-demand cleanup. Retention is applied automatically after each backup run (
backup_apply_retention). - The calendar got a month/week view toggle — the week view renders an hourly grid with an all-day bar, and events move in both.
- The admin "Save config" button is gone: configuration autosaves with optimistic versioning. If another admin saved in the meantime, you get a conflict notice instead of a silent overwrite.
- Schema and Views editors were restyled to the common section-card layout.
Following this series?
OpenSparrow v3.9 — touch drag & drop for board and calendar, and a demo-free setup wizard
Top comments (0)