OpenSparrow v4.0: JSONB columns, public table sharing

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 (via secret_hash), and lookups use hash_equals for 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_options gates other_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 via initial_limit in 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 loadTable round-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

Websites

https://opensparrow.org