DEV Community: Tomasz

OpenSparrow v4.5 adds a chat shortcut on user tooltips

Tomasz — Thu, 29 Oct 2026 18:05:33 +0000

OpenSparrow v4.5 adds a chat shortcut on user tooltips, a per‑page Click Statistics breakdown with date‑range filters, and a last‑edited‑by line in the Record History tab — plus a base64 logo upload that works on shared hosting with an unwritable temp dir.

A chat shortcut on every user tooltip

Every record owner and comment author tooltip now ends with a small chat button. User tooltips already went through one shared funnel (enableUserTooltips() in public/assets/js/util/user-tooltip.js), which fetches the user's contact card via api/owners.php?action=user_info and renders it through the record tooltip; the model now also carries a chat action when the target is not yourself (info.id !== window.CURRENT_USER_ID). Clicking the button dynamically imports chat-panel.js and calls openChatPanel(peer), so the messenger opens straight into that person's thread — even when no prior conversation exists (the empty thread renders and the first send creates the message). The shortcut respects the existing chat_enabled gate and simply does not appear for the self user.

Click Statistics: per‑page breakdown and date filtering

The Click Statistics admin panel gains a Pages tab: one tile per page script, each showing its icon and click count, aggregated from spw_clickstats with the same element/user filters as the Log and Top Elements tabs. The rollup is computed server‑side (GROUP BY c.page, COALESCE(c.page, '') for empty pages) and rendered as a responsive icon grid, with unknown pages labelled "Unknown page". The Log, Top Elements and Pages tabs all accept a new From/To date range, validated against a YYYY-MM-DD shape before it reaches SQL and applied as a half‑open window (created_at >= from and created_at < to + 1 day) so both bounds are inclusive.

Record History: who last edited the record

The Record History tab now shows a "Last edit" line under the owner section, answering "who edited this record and when" directly. It is read from the audit log (spw_users_log) through a new owners last_edit API action: the latest INSERT/UPDATE row for (target_table, record_id), joined to spw_users for the username and avatar. Mass edits and board/calendar moves log with record_id = null, so they correctly never appear — the line reflects edit/create-form saves only. The table and record are gated through the usual validatedTable() → require_table_access() path, so a user only sees last‑edit info for tables they can already access.

Branding logo upload that survives shared hosting

The branding logo upload moved off multipart. A shared host with an unwritable upload_tmp_dir previously failed every $_FILES upload with "No file received", because PHP writes the temp file before any userland code runs and upload_tmp_dir/sys_temp_dir are PHP_INI_SYSTEM (only settable in php.ini). The admin now reads the image with FileReader.readAsDataURL() and POSTs it as JSON base64 through php://input, so no temp directory is ever touched. The backend guards the payload length before decoding, re‑checks the decoded size against the 2 MB limit, validates the MIME via finfo::buffer(), and writes straight into public/assets/img/uploads. The $_FILES path stays as a fallback, and its error codes now map to distinct messages — including a dedicated "set upload_tmp_dir in the hosting panel" hint for UPLOAD_ERR_NO_TMP_DIR.

Also in this release

Demo contact fields — the CRM demo users are now seeded with first name, last name, email and phone, so the record‑history and user tooltips show full info right after install (reinstall from Admin → Demo to pick them up).
Audit‑log last‑edit — backed by the existing spw_users_log trail, no new table or migration is required.

Following this series?

OpenSparrow v4.4 adds one-to-one user chat, an HTTP cron trigger, and content-sized admin tables

Websites

https://opensparrow.org

OpenSparrow v4.4 adds one-to-one user chat

Tomasz — Tue, 20 Oct 2026 08:25:29 +0000

OpenSparrow v4.4 adds one-to-one user chat with bell notifications, a token‑protected HTTP cron trigger for shared hosting, and admin tables that size to their content — plus a fixed chat notification title.

One-to-one user chat

Frontend users can now message each other directly. The messenger is served by public/api/chat.php (a thin router over includes/Controller/Api/ChatController.php) and opens from the Chat entry in the avatar menu (public/assets/js/chat-panel.js, openChatPanel()); a ?chat=1 deep link opens it straight away. Conversations are strictly 1‑to‑1 — there are no group threads, and a message is visible only to its sender and recipient. Reading requires only a login; sending and hiding require the editor role, so viewers can follow their conversations but cannot write. Message bodies are trimmed and capped at 4000 characters in both PHP and a CHECK constraint, and a user cannot message themselves. The search box finds users with an escaped ILIKE match (at least two characters) and the conversation list collapses each peer to their latest message via DISTINCT ON (peer_id).

Instant bell notifications

Sending a message immediately inserts an in‑app notification for the recipient (ChatController::notifyRecipient()) into spw_users_notifications, with the source table chat and a ?chat=1 deep link, so the recipient is alerted without waiting for a cron run. The insert is idempotent (ON CONFLICT … DO NOTHING). Either participant can hide a message; hiding is a soft delete that flips is_visible without removing the row, and the ownership check refuses to hide a message you are not part of.

An HTTP cron trigger for shared hosting

Most shared hosts run cron through a URL fetch and chroot the process to the web root, so they can never reach the CLI‑only cron/ directory. The notification cron can now also run over HTTP: public/cron.php reads a CRON_SECRET environment variable and a token parameter, compares them with hash_equals, and answers plain text. An empty CRON_SECRET disables the endpoint entirely. The worker itself (cron_notifications_run() in includes/cron_notifications.php) is shared by both the CLI entry (cron/cron_notifications.php) and the HTTP endpoint, so there is no second runner to drift. cron/ ships a Deny from all .htaccess like every other backend folder. Secrets placed in a URL must be hex (openssl rand -hex 32), never base64 — a + decodes to a space inside a query string. The admin Cron → Setup tab now includes a ready‑to‑copy HTTP trigger example.

Admin tables that size to their content

Admin tables no longer squeeze their columns into the panel width. public/admin/style.css makes .adm-tbl size to its content (width: max-content; min-width: 100%) with white-space: nowrap cells, and .adm-sec-body scrolls horizontally, so timestamps, buttons and SQL stay readable instead of wrapping. The fix is global — it covers both the literal adm-tbl tables and every mkTable() caller — and the hand‑rolled per‑table overflow-x:auto wrappers were removed.

Also in this release

Chat notification title fixed — the new‑message alert rendered the raw i18n key chat.new_message; the key is now defined in all 20 language files.
4.4_user_chat migration — creates spw_chat_messages (sender, recipient, body, timestamp, soft‑hide flag, a foreign key to spw_users with ON DELETE SET NULL, and per‑participant indexes); apply it from System → Migrations → Initialize System Tables.
Demo seed — the CRM demo now ships a sample chat between the demo users, so the messenger is populated right after install.

Following this series?

OpenSparrow v4.3 adds record links and @mentions to comments, user info tooltips, and a unified frontend

Websites

https://opensparrow.org

OpenSparrow v4.3 adds record links and @mentions to comments

Tomasz — Sat, 17 Oct 2026 18:20:35 +0000

OpenSparrow v4.3 adds record links and @mentions to comments, user info tooltips, and a unified frontend — plus a shared help cursor, first-column row actions, and a simpler setup wizard.

Record links in comments

A comment body can now reference any record by writing [Record: table:id], which renders as a clickable link that opens that record. A record picker dialog (public/assets/js/comments.js) inserts the marker for you — you never hand-type an id. Markers are extracted server-side (includes/comment_links.php) and validated against the schema before being trusted, so a malformed or non-positive id is dropped rather than turned into a broken link.

@mentions with in-app notifications

Typing @username in a comment (with an autocomplete picker, public/assets/js/comments/mention.js) mentions an active user and drops an in-app notification into their bell. includes/comment_mentions.php parses the mention, matches it against spw_users (active, non-author accounts only), and writes into spw_users_notifications with a title (“X mentioned you”) and a deep link to the record’s Comments tab:

Mentions never notify the author of the comment
The insert is idempotent (ON CONFLICT … DO NOTHING), so repeating the same mention on the same record does not spam
Any failure is logged server-side without leaking driver text to the client

User info tooltips

Hovering a comment author or a record owner now shows a tooltip with the user’s full name, role, email and phone. Data comes from includes/user_info.php and renders through the shared enableUserTooltips() funnel (public/assets/js/util/user-tooltip.js), which also upgrades every existing data-username anchor — so @mention chips and comment author links get the tooltip for free.

A unified frontend

Buttons, fonts and tables now follow one shared set of design tokens in public/assets/css/styles.css (--accent, --bg, --border, --radius, --shadow-*, --font-sans/--font-mono). Buttons inherit the page font everywhere — a plain <button> no longer falls back to Arial — and the edit/create forms render Excel-style subtable borders, gray headers and zebra rows from the same recipe as the record tables. The grid’s row-actions column (edit/delete) moved to the leftmost position, and every record/user tooltip now shows the shared ? help cursor.

Also in this release

Shared ? cursor — all tooltips funnel through showRecordTooltip() / enableUserTooltips(), so grid cells, board cards, calendar chips, roadmap bars, drilldown rows, owners and comment authors all show a consistent question-mark cursor; a hand-rolled cursor on the grid cell was removed in favour of the single rule.
Simpler setup wizard — the database step no longer prefills the database name, user or password (or a default schema); only localhost and 5432 are prefilled, so a fresh install can’t accidentally target placeholder credentials. The config/demo_meta.json install marker is now documented.
Demo CRM phone masked for viewers — the seeded contact.phone column now carries the mask_for_viewer flag, so read-only Viewers see ••••• while editors and admins keep the real values.

Following this series?

OpenSparrow v4.2 adds native Excel export, column masking for viewers, and record-tooltips in drilldowns

Websites

https://opensparrow.org

OpenSparrow v4.2 adds native Excel export, column masking for viewers

Tomasz — Thu, 15 Oct 2026 06:13:54 +0000

OpenSparrow v4.2 adds native Excel export, column masking for viewers, and record‑tooltips in drilldowns — plus a handful of UI polish and stability fixes.

Native Excel export

The record grid, public share links, saved views and the mass‑edit “Export Selected” panel can now download a real .xlsx workbook alongside the existing CSV. The workbook is built completely in the browser by a dependency‑free writer (public/assets/js/export_xlsx.js) that emits a valid OPC package:

Typed cells for numbers, booleans and dates
XML‑escaped text and sanitized sheet names (max 31 chars)
Required docProps/core.xml and docProps/app.xml parts

The UI now shows an Export Excel button (also in the mobile actions menu), views and share pages render one next to Export CSV, and the mass‑edit panel adds a Format radio (CSV / Excel) that switches the download label. All new strings are translated in every one of the 20 languages.

Viewer column masking

A column can be flagged in the schema editor (Schema → table → column → Mask value for Viewer) to hide its value from frontend Viewer accounts. The column stays visible everywhere, but every value is replaced with the ••••• placeholder:

Grid, drill‑down sub‑tables, record form, board cards, calendar events, roadmap bars, dashboard lists and group/time‑series labels
M2M option labels, FK __display labels, and public share links

Masking is enforced server‑side – the raw value never leaves the backend for a Viewer – while Editors and Admins see the real data. The flag lives in the schema configuration (columns.<col>.mask_for_viewer) and requires no migration; the schema editor marks such columns with a warning badge.

Record tooltip in drilldowns

Hovering a child row inside an expanded drill‑down now shows the same record tooltip as grid cells, with the child’s title and its configured columns. The tooltip logic was refactored to use a monotonic show token, eliminating race conditions between overlapping hovers.

Also in this release

XLSX export hardening – object‑URL revocation is now deferred (setTimeout(…, 0)) so Firefox no longer cancels a download before it starts, and every download site (grid, views, mass‑edit CSV, share) uses the shared downloadBlob helper instead of hand‑rolled <a> + URL.createObjectURL code.
Tooltip hide race fixed – a stale hide timer could dismiss a tooltip that a newer hover had just shown; the new token‑based approach guarantees the correct tooltip stays visible.

Following this series?

OpenSparrow v4.1 adds optional two‑factor login by email, streaming answers in Ask AI, and PostgreSQL views to the External API

Websites

https://opensparrow.org

OpenSparrow v4.1 adds optional two-factor login by email

Tomasz — Sat, 10 Oct 2026 08:24:06 +0000

OpenSparrow v4.1 adds optional two-factor login by email, streaming answers in Ask AI, and PostgreSQL views to the External API — plus a security hardening pass across the write paths.

Two-factor login, off by default

System → Settings → Login Security gains a toggle: after a correct password, accounts with a contact email must enter a 6-digit code sent to their inbox — the pattern banking apps use, now built into your self-hosted instance.

Opt-in, per account — off by default; accounts without an email keep password-only login, so nobody gets locked out during rollout
Abuse-resistant — codes expire after 60 seconds, allow 5 attempts, sending is throttled per IP and username, and a delivery failure fails closed instead of skipping the factor
No new infrastructure — reuses your existing cron/SMTP email settings and the contact fields from v3.3; no migration

Ask AI streams its answers

Responses arrive token by token instead of one block at the end — long analyses start appearing immediately while the model is still writing.

Clickable record links — answers reference rows as links straight to the record
The answer of record — the streamed text is a preview; the final payload carries the cleaned-up version with markdown, links and follow-up suggestions
A working off switch — the admin's Ask AI enable flag finally gates the API (403) and hides the menu button and FAB

External API now serves PostgreSQL views

System → API can bind a registered PostgreSQL view — aggregates included — instead of only a raw table, which is how integrations usually want to consume data:

Columns and filters are re-checked against the live database on every save and request; an altered view answers a clean 404, never a 500
Views sort by their first selected column (no guaranteed id); tables keep id DESC
Fixed: freshly generated API keys are adopted on save instead of being regenerated on every re-save

Also in this release

CSV import moved to a proper service layer; cron output is plain text
Version info moved from the login page to the footer
New records open on the first tab

Following this series?

OpenSparrow v4.0 adds a JSONB column type, public read-only table sharing, and many-to-many editing directly on the grid

Websites

https://opensparrow.org

OpenSparrow v4.0: JSONB columns, public table sharing

Tomasz — Wed, 07 Oct 2026 15:35:03 +0000

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

OpenSparrow v3.9 - makes the board and calendar fully touch-friendly

Tomasz — Mon, 28 Sep 2026 15:30:03 +0000

OpenSparrow v3.9 makes the board and calendar fully touch-friendly and moves the CRM demo out of the setup wizard. A shared pointer-drag shim brings drag & drop to touch devices; the demo is now an opt-in in Admin → Demo after first login.

Touch drag & drop for board and calendar

HTML5 drag & drop never fires dragstart / dragover / drop for touch pointers — dragging a board card on a phone did nothing. Workarounds (long-press timers, hijacked touchmove) fail: the browser decides the scroll-vs-drag gesture before JavaScript can react.

v3.9 fixes this with CSS, not JavaScript: the shim (public/assets/js/util/touch-dnd.js) sets touch-action at wiring time — pan-y on board cards (horizontal drags, vertical swipes scroll) and none on calendar chips. The browser routes the gesture natively; no timers, no hijacked scrolling.

Drags start by movement, not hold time — a >12 px, direction-aware slop keeps taps as clicks. A ghost follows the finger, drop targets highlight, and pointercancel cleans up. Gated on pointerType === 'touch', the shim leaves desktop HTML5 DnD untouched; both paths call the same move function (moveCard, moveEventTo) — one source of truth for optimistic update, API call and rollback. It wires only where modules gate on canEdit. A same-pass fix puts the drop-target class on the lane element itself, so the highlight renders.

The setup wizard no longer installs the demo

The demo checkbox is gone; the wizard now does one thing: schema, system tables, migrations, and the first admin account with a one-time random password. The demo itself is untouched — after first login, Admin → Demo offers every part (sample tables, dashboards, board, RAG knowledge base, demo accounts) as a reversible opt-in. public/setup_api.php no longer fakes a logged-in admin session mid-setup, and the four setup.*demo* i18n keys were dropped from all 20 language files.

Smaller interface changes

Nav menu items got thin 1px borders, stronger on hover and active — structure without visual noise
The sidebar collapse icon right-aligns to the far edge of the menu, where it reads as "collapse this"

Upgrade notes

No database migration, no removed configuration keys; the release migration only deletes the unused public/mock.html. One caveat: emulated pointers bypass native scrolling, so the shim was validated on real devices before shipping.

Following this series?

OpenSparrow v3.8 — a Roadmap (Gantt) module and a spreadsheet-style grid

Websites

https://opensparrow.org

OpenSparrow v3.8 - Roadmap (Gantt) module and new style grid

Tomasz — Tue, 22 Sep 2026 14:16:05 +0000

OpenSparrow v3.8 ships a brand-new Roadmap (Gantt) module and gives the record grid a long-overdue new-style facelift. The roadmap module turns any schema table into a read-only Gantt timeline — multiple roadmaps, category color chips, milestone markers — with zero new database objects. The rest of the release polishes what you look at all day: crisper table borders, a relocated sidebar toggle, refreshed branding and better color contrast on enum dropdowns. Below is a breakdown of what changed and why it matters.

Roadmap (Gantt) module

The headline feature lives in Admin → Data Management → Roadmap. You define one or more roadmaps, each bound to a schema table through a set of column mappings: which column holds the title, which ones the start and end dates, optionally a category column, a progress column and card columns for the row details. Everything is configuration in the existing spw_config store — there is no new table, no migration, nothing to run after upgrading.

On the frontend each roadmap renders as a read-only Gantt timeline:

A month grid with quarter shading and prev/next navigation to scroll through time
Milestone markers for rows without an end date — one-off deadlines show up as distinct markers instead of phantom bars
A category filter bar — colored chips with a legend dot per category; click a chip to toggle the whole category on or off, and the filter state is remembered
A per-roadmap color for the bars, plus configurable card columns showing extra data when you inspect a row

Access control follows the existing per-user scope model rather than inventing a new one. A new roadmaps scope gates the menu entries and the front API, and the bound table must additionally be within the user's table access — the same empty-grant-list-means-unrestricted semantics as every other scope. Granting a user a roadmap does not grant its table.

The CRM demo dataset gained a projects table with sample rows so you can seed a working roadmap in seconds, the UI strings are translated into all 20 locales, and the new endpoints are pinned by guard tests.

The grid now looks like a spreadsheet

If you spend your day inside the record grid, this is the change you'll notice first. The grid and view tables dropped the zebra striping in favor of new style cell borders every cell gets a border, the header row sits on a light background, and hover states highlight individual cells instead of whole rows. The result reads like a spreadsheet: dense, regular, easier to scan column-by-column. All colors come from the existing design tokens, so custom themes keep working.

A static design reference for the new look ships as public/mock.html — it isn't referenced by the application and is safe to delete from production deployments.

Smaller interface changes

Sidebar toggle relocated — the desktop collapse toggle moved from the header to the bottom of the menu, where it belongs; the hamburger stays in the header on mobile
Menu-list nav links now render at 14px font-sans, aligning with the grid typography
Branding assets were refreshed — a new logo.png and a favicon that dropped from 207 KB to under 3 KB

Fixed

Enum dropdowns keep readable text color via YIQ contrast on both the grid and the edit/create form — previously a custom enum color could render white-on-light or dark-on-dark option text. The contrast is computed server-side and applied consistently
docs.js inconsistencies across admin modules were repaired, along with a broken Polish string in docs-strings.js
The cron, anonymization and ETL log docs now describe the status values correctly as running / success / error (they were documented as ok), in docs/DATABASE.md and in the column comments emitted by the system-tables DDL

Upgrade note

No database migration and no configuration keys are removed by this release. Roadmap configuration lives in the existing spw_config store under the roadmap key; there is nothing to run or clean up after upgrading. For per-user access, remember the roadmaps scope is independent — users also need table access to see any roadmap content.

Following this series?

OpenSparrow v3.7 — Automation Email Queue and a Unified Admin UI

Websites

https://opensparrow.org

OpenSparrow v3.7 - brings an automation email queue to the admin panel and unifies the entire admin UI

Tomasz — Fri, 18 Sep 2026 05:58:12 +0000

OpenSparrow v3.7 brings an automation email queue to the admin panel and unifies the entire admin UI around a single section-card pattern. The email queue gives admins full visibility and control over outgoing automation emails — filter, requeue, bulk delete, purge — while the rest of the release is a consistent overhaul of how admin screens are built: one card style, one width, one typography scale, one statistics tile pattern. Below is a breakdown of what changed and why it matters.

Automation email queue

A new section in Admin → System → Cron lists the queued automation emails stored in the existing spw_automation_emails table. Until now those rows were written by the notification cron but invisible from the UI — if a message bounced or sat undelivered, there was nowhere to see it.

Filter the queue by status — pending / sent / error — with live per-status counters
Bulk requeue resets the attempts counter and error message, so failed sends retry cleanly
Bulk delete works on up to 500 rows at once
Purge by status, optionally limited to entries older than a configurable retention window

The queue tab reports a missing table instead of failing, so an instance that never ran email automations degrades gracefully.

RAG document preview

A Preview button in the Centrum AI documents list opens a modal with the stored document content — filename, tags, size and dates included — so you can inspect what the RAG pipeline actually chunked without downloading the file. The rechunk action now shows success feedback, and the documents table respects container width with vertically stacked action buttons on narrow screens.

The RAG section is now Centrum AI

The admin section formerly known as RAG is renamed to Centrum AI across the sidebar, breadcrumbs, demo copy and guidance docs. The RAG settings fields gained explanatory tooltips covering context-file limits, upload size and timeout constraints — previously you had to check the docs to know what a field accepted.

One admin UI pattern instead of twenty

The bulk of the release is invisible until you open two tabs side by side. Every settings form, data table and log in the admin panel now renders inside a buildSectionCard section with a consistent header style:

Section cards are never nested — one card is one section, a tab is a flat sequence of cards in reading order
All settings tabs share one width via the .admin-page wrapper; per-panel max-width inline styles were removed
Statistics and log tabs (External API Usage, Click Statistics, Cron email queue, Users) follow the same tile-grid pattern as Centrum AI → Query Statistics
Typography was unified to three sizes and two weights via CSS tokens; .adm-sec-hdr styling now lives in the stylesheet instead of inline overrides

Fixed

Admin tab renderers no longer wipe section descriptions when re-rendering a tab — content is now rendered into the inner content div instead of replacing the whole panel.

Upgrade note

No database migration and no configuration keys are removed by this release. The email queue operates on the existing spw_automation_emails table (created by earlier releases); if the table does not exist, the queue tab says so instead of erroring.

Following this series?

OpenSparrow v3.6 — External API Module with Encrypted Read-Only Keys

Websites

https://opensparrow.org

OpenSparrow v3.6 adds an External API module

Tomasz — Tue, 08 Sep 2026 15:41:24 +0000

OpenSparrow v3.6 adds an External API module that lets admins expose table data to external services through read-only, encrypted API keys. Each key is bound to a single table with a fixed column set, server-side filters and a row limit, and is served by a sessionless JSON endpoint that accepts nothing from the client except the key itself. Below is a breakdown of what changed and why it matters.

External API module

A new Admin → System → API tab lets admins define read-only API keys. Each definition is bound to one schema table, a chosen set of columns, fixed server-side filters and a row limit, so the data an external service can see is decided entirely by the admin — never by the caller.

Keys are stored encrypted (key_enc) plus an HMAC key_hash for constant-time lookup, and are never returned to the browser after generation
A new or regenerated key is generated server-side and shown exactly once in a modal
Filter values are type-checked against the column's schema type on save, so a stored filter can't 500 at query time

A sessionless JSON endpoint

public/api/external.php is the single endpoint. External services authenticate with an Authorization: Bearer <key> header and receive JSON rows.

The endpoint accepts no table, column or filter names from the client — everything comes from the config
Hidden and system (spw_) tables are refused, both on save and at request time
Rate limiting is enforced per key and per IP, answering 429 with a Retry-After header
Responses are 401 (missing/bad key), 403 (disabled), 404 (configured table gone) and 429 (rate limited)

Usage statistics

A new spw_external_api_log table records each successful request — API, table, rows returned and duration — with a stats/log view and a purge action in the admin module, so you can see exactly how your keys are being used.

Changed

includes/db.php gained sys_table_prefix() / is_system_table() helpers so the endpoint can refuse system tables.
includes/crypto.php gained secret_hash() (HMAC-SHA256) so keys can be matched without decrypting every entry.

Fixed

nginx and Apache configs now forward the Authorization header to PHP (fastcgi_param HTTP_AUTHORIZATION / SetEnvIf), so Bearer auth works out of the box instead of silently 401ing.

Upgrade note

No database migration and no configuration keys are removed by this release. The external_api config key is created on first save; an unknown key is ignored, so the document upgrades itself.

Following this series?

OpenSparrow v3.5 — Language Selector, Searchable FK Pickers and Notification Timestamps

Websites

https://opensparrow.org

OpenSparrow v3.5 - Login language selector, searchable FK pickers & notification polish

Tomasz — Fri, 04 Sep 2026 15:13:20 +0000

OpenSparrow v3.5 is a usability release that adds a language selector to the login page, searchable foreign-key pickers, and notification timestamps, while unifying the notifications dropdown with the shared panel style. This update makes the app friendlier for international users, speeds up data entry on long foreign-key lists, and tidies a few small UI details. Below is a breakdown of what changed and why it matters.

Language selector on the login page

Users can now pick their locale before signing in. The login form gains a language <select> populated from the available locales, and the <html lang> attribute follows the active locale so screen readers and browsers render the page in the right language from the very first screen.

Locale is chosen before authentication, so the whole session starts in the user's language
The <html lang> attribute now tracks the active locale instead of a hardcoded value
Selection is driven by I18n::availableLanguageMeta(), so new locales appear automatically

Searchable foreign-key pickers

Edit and create forms replace the plain <select> on foreign-key fields with a searchable input backed by a <datalist>. Long FK lists are now filterable by typing instead of scrolling through a dropdown.

New public/assets/js/edit/fk-search.js wires up the search behaviour
Typing filters the datalist; the selected value is synced to a hidden field carrying the real FK id
Blurring with an unmatched value restores the original label, so invalid selections can't slip through

Notification timestamps

Each item in the notifications dropdown now shows its created_at time, formatted with the browser's locale. You can tell at a glance how old a notification is.

GitHub link in the footer

A GitHub lockup badge now links to the project repository from the footer, so the source is one click away from anywhere in the app.

Changed

The notifications dropdown was unified with the shared bp-panel style (header, close button, overlay) instead of its own bespoke markup.
Notes UI strings are now translated into all 19 locales.

Fixed

The #debug bar no longer flashes before JS hides it on page load — it is now hidden inline.

Upgrade note

No database migration and no configuration keys are removed by this release. Upgrading is a drop-in replacement.

Following this series?

OpenSparrow v3.4 — Material Symbols Outlined and a Searchable Icon Picker

Websites

https://opensparrow.org

OpenSparrow v3.4 — replaces the bundled icon set with Material Symbols Outlined and adds a searchable icon picker

Tomasz — Wed, 02 Sep 2026 06:30:41 +0000

OpenSparrow v3.4 is a design-focused release that replaces the bundled icon set with Material Symbols Outlined and adds a searchable icon picker to the admin panel. This update modernizes the visual language across the whole application, gives admins a fast way to find the right icon, and cleans up a handful of smaller UI details. Below is a breakdown of what changed and why it matters.

Material Symbols Outlined

The bundled icon set has been replaced with Google's Material Symbols Outlined. The new set lives in public/assets/icons/material/ and carries the full collection — 3,899 SVG files at optical size 24, normalized to 48×48 so they match the intrinsic size of the PNG icons they sit next to.

84 files removed from public/assets/icons/ (83 PNG plus chevron-down-white.svg)
The dropdown arrow on <select> elements is now an inline data URI in styles.css
Eight PNG icons the system still references by name (book_3s, delete, folder_zip, grid_on, notifications, picture_as_pdf, search, settings) stay where they are
Generated manifests (index.json, tags.json, README.txt) ship alongside the SVGs

Searchable icon picker

The icon field in the admin now gets a proper picker instead of a bare text input:

Matches icon names and the full Google tag list
Understands common Polish words (e.g. "ciężarówka" → local shipping)
Category chips for quick filtering
Remembers recently picked icons in the browser

Upgrade note: icon paths live in the database

This is the one thing to know before upgrading. Icon paths are stored in the database, not in code — the menu_icon keys of the dashboard, calendar, files, board, views and workflows config, the per-table icon in the schema config, and the printout icons.

A stored path naming one of the removed files stays valid as far as validation is concerned and renders as a broken image — nothing rewrites those values automatically. Repoint them in the admin with the Browse picker. 79 of the 84 removed names have an identically named .svg under assets/icons/material/, so assets/icons/NAME.png usually becomes assets/icons/material/NAME.svg. The exceptions are calendar.png (use calendar_month.svg) and folder_zip_blue.png, grid_on_blue.png, picture_as_pdf_blue.png, which have no direct counterpart.

No database migration and no configuration keys are removed by this release.

Also in this release

Removed the GitHub link from the login page
Fixed demo image seeding; long lines wrapped; Files menu icon unified
Updated grid action icon names in the admin docs
Refreshed module demo video links and images in the README

Following this series?

OpenSparrow v3.3 — Click Statistics, Production Readiness Checks, and Deployment Hardening

Websites

https://opensparrow.org