DEV Community: Tomasz

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

MatrAIx consists of 8.3 billion agent-persons who statistically represent the entire human population

Tomasz — Fri, 28 Aug 2026 20:52:27 +0000

It is an open-source research community that has created an infrastructure for simulating user behavior. At its core is a database of 8.3 billion virtual agents (personas) that statistically represent the entire human population, allowing digital products and AI to be tested without involving real people.

How are users created?
Personas are created using a hybrid approach—combining real-world data (Wikipedia, surveys) and synthetic data (generated by AI). Each of the 8.3 billion personas has 1,290 detailed traits (demographics, psychology, skills) and is then materialized as an LLM agent that makes decisions.

How do you use them technically?

Resources: Download a ready-made set of 1 million personas from Hugging Face (or the full 8B dataset).
Requirements: You’ll need Docker, uv (Python), Node.js, and API keys (primarily from Anthropic).
Setup: Clone the repo, install dependencies, and download the data.
Running: Create test tasks (surveys, chat, browser, app) and run them on individual personas, in batch mode, or via the Playground GUI.
Applications: Market research, chatbot testing, website usability assessment, and evaluation of AI models for different user groups.

The developers emphasize that MatrAIx is intended for preliminary testing and hypothesis evaluation, but does not fully replace final testing with real people. An interesting application is simulating user behavior in applications (Web & App UI/UX): Agents interact with websites or apps (by controlling the browser or default API) to test purchase paths and the intuitiveness of the interface.

What does the trained data look like (data structure)?
It consists of structured attribute profiles (JSON / Parquet).

Before an agent is used, its attributes are dynamically converted into a system prompt for the LLM.

On the project website, you'll find something interesting: graphical interface at matraix.ai/playground

You select a persona (e.g., from the Persona-1M database based on filters such as age, country, and education).
You choose one of 8 tasks (e.g., Web Browsing or Survey).
You run the simulation and observe in real time (or analyze in the summary) how that profile performs the assigned task.

Website: matraix.ai

What do you think of this project?
In what areas can this project be used?

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

Tomasz — Thu, 20 Aug 2026 18:25:47 +0000

OpenSparrow v3.3 is a stability‑focused and deployment‑focused release that brings real‑world operational visibility, a production‑readiness checklist, and a full hardening pass across environment variables and configuration defaults.

This update introduces an optional Click Statistics module, expands the Admin Health Check into a proper production‑readiness validator, and tightens deployment settings for anyone running OpenSparrow outside a local dev box. Below is a detailed breakdown of what changed, why it matters, and how it improves day‑to‑day operations.

Click Statistics

A new optional module — off by default, and off genuinely means off.
When the module disabled, no collector script is emitted, nothing is downloaded, and the endpoint returns 204 without writing even if a page stays open across a toggle‑off.

Records who clicked what, when, on which page, and optionally the table/record in context (“Record Table And Record” can be turned off to keep entries to just user/time/element)
Log tab: recent clicks newest‑first, filterable by element and user, with a Top Elements rollup of the 20 most‑clicked elements
Retention: “Delete Automatically After N days” (default 90, 0 = keep forever) enforced by the notifications cron so the table doesn’t grow unbounded

Admin Health Check: Production Readiness tab

Health Check now includes a dedicated tab answering is this deployment actually production‑ready? instead of just “is the database reachable.

It validates:

APP_ENV
demo mode being off
secure cookies
HTTPS (including behind reverse proxies via X-Forwarded-Proto / CF-Visitor)
display_errors off
Argon2id availability
minimum password length of 12
API rate limiting configuration
SESSION_SAMESITE
trusted‑proxy‑header configuration

Existing checks were refreshed as well — minimum supported PHP version now correctly requires 8.4, matching the rest of the codebase.

Deployment hardening

A broad pass through .env.example and includes/config.php:

every environment variable is now documented
numeric config values are clamped to sane ranges
APP_TIMEZONE and SESSION_SAMESITE are validated instead of trusted blindly
new settings cover: APP_URL, STORAGE_PATH, SESSION_COOKIE_NAME, DB_SSLMODE, API rate limiting, statement/SMTP/HTTP client timeouts, session idle timeout, Argon2 tuning, and a trusted‑proxy IP list

Additional hardening:

API throttle now fails open (instead of blocking all requests) if its state store becomes unwritable
libpq connection‑info values are quoted
CSP report URI restricted to same‑origin

Also in this release

Optional first name, last name, email, and phone fields on admin user accounts (informational only — not used for login, ownership, or notifications)
Logo and favicon refreshed; login/admin brand text sized down to match
Large internal cleanup: PHP/JS variable names expanded from abbreviations to full words, public API endpoints moved behind typed controllers (App\Controller), and die() / exit() replaced with a typed exception hierarchy — no behavior change, but relevant if you track the codebase

Following this series?

OpenSparrow v3.2 — Per‑User Access Control, Materialized Views in RAG, and a Business Design Refresh

Websites

https://opensparrow.org

OpenSparrow v3.2 - Per-User Access Control, Materialized Views in RAG, and a Business Design Refresh

Tomasz — Thu, 13 Aug 2026 14:22:39 +0000

OpenSparrow v3.2 is a security‑focused and consistency‑focused release that strengthens access boundaries, improves AI‑driven aggregate queries, and unifies the entire admin and frontend experience under one design system.

This update introduces per‑user access control across all major scopes, brings materialized‑view support to RAG aggregate queries, and delivers a full visual refresh that aligns every UI surface with the new Business design language.

Below is a detailed breakdown of the changes, including how they work, why they matter, and what they unlock for real deployments.

Per-user access control

New Users → Access tab. Each user gets independent allow‑lists across five scopes — tables, views, printouts, boards, workflows — stored as user_table_access in config.

The default is unrestricted: an absent or empty list means “no restriction”, not “no access”. To fully cut a user off, deactivate the account instead.

Enforced at the API boundary by default, not just hidden in the UI — os_api_action() / os_api_dispatch() gate table, view, print, board, and workflow endpoints, and FK/file/comment/mass‑edit/data‑cleanup endpoints were extended to respect the same restrictions
Granting a board or workflow does not implicitly grant the tables it touches — a board whose table isn’t granted won’t show up, and a workflow drops if any step targets a table the user can’t reach
Hidden tables (no menu entry, no grid) can’t be ticked and are granted automatically
Delegated FK lookups and the schema endpoint are now scoped to what the requesting user can actually access, and the file listing is filtered by record ownership

RAG: materialized views as aggregate views

The aggregate‑view feature from 3.1 (attach a PostgreSQL view to a table so the AI assistant can answer exact “total/how many” questions) now also accepts materialized views, flagged as such in the admin UI — useful when the aggregate query is expensive enough that you’d rather refresh it on a schedule than recompute it on every chat message.

Views sync also discovers materialized views alongside regular ones.

A unified “Business” look

The admin panel, setup wizard, and frontend were unified onto a single Business 4+1 color palette and one system‑ui font stack, replacing a mix of ad-hoc colors and font declarations across dozens of JS/CSS files.

Every front‑end button now shares one height, weight, border radius, and font size instead of drifting per page.

Also in this release

CRM demo: richer install description, plus optional demo‑user and audit‑history install toggles
Release packaging excludes creator/, dist-local/, and storage/tmp/ from image and release archives
Admin docs updated to match actual current module behavior across several sections

Websites

opensparrow.org

Discussion

I encourage a substantive discussion, and I'll do my best to answer any questions regarding implementation and configuration.

OpenSparrow v3.1 - RAG assistant, Record Image Galleries, and Workflow Stored Procedures

Tomasz — Thu, 06 Aug 2026 06:12:57 +0000

Automation rules can now call out to n8n, the RAG assistant can answer exact aggregate questions via admin-configured views, tables can have their own image galleries, workflow steps can run a PostgreSQL stored procedure, and the grid gets conditional row highlighting.

Automations: n8n webhooks

The Automations admin is now split into two tabs - Record (the existing field-update/notification/create-record rules) and n8n. The n8n tab lets a rule fire an outgoing webhook to an n8n workflow instead of (or alongside) the built-in actions, with webhook credentials encrypted at rest. Useful for handing off to Slack, email marketing, or any of the hundreds of services n8n already integrates with, without writing custom PHP.

RAG assistant: aggregate views

The AI assistant used to be limited to whatever was on the current grid page, so it had to refuse "how many / total" questions about the whole table. In 3.1, an admin can attach a PostgreSQL view (schema.view) to a table under RAG settings - a pre-vetted, read-only aggregate query. When that table's page is open, the assistant runs the view (capped at a configurable row limit) and gets the exact figures fenced into the prompt as authoritative data, separate from page content and record text.

The model still never writes or chooses the query itself - it only ever sees the output of a view an admin explicitly configured, with an extra guard that skips owner-restricted tables since a plain view has no per-user filter. The result: questions like "what's the total invoice value this quarter" get an exact answer instead of "I can only see this page."

Record image galleries

New per-table image galleries - configure an image column and records show a thumbnail strip in the grid plus an upload gallery on the edit form.

Comes with full Cypress E2E coverage and demo data (contact photos in the CRM demo).

Workflow steps: PostgreSQL procedure hook

Workflow steps can now call a PostgreSQL stored procedure as part of a guided flow, in addition to the existing table-form steps. The CRM demo ships a worked example. Combined with the record-image upload option added to workflow steps, workflows can now do a lot more than collect form fields.

Grid: conditional row highlighting

Schema-configured row highlight rules color entire grid rows based on column conditions - e.g. flag overdue invoices or high-value deals at a glance - seeded with an invoice example in the CRM demo.

Grid hover/crosshair styling was also reworked: a vertical column highlight alongside the existing row highlight, solid accent header fills, and a fix for the FK cell tooltip getting stuck over open dropdowns.

Also in this release

Notes reminders now take a date and time, not just a date
Optional CRM demo install directly from the first-run setup wizard
RAG: grid context now built from the actual data model and fenced in the prompt, with a hardened no-answer/partial-answer prompt
Security: all admin write actions are now guarded by require_not_demo(), closing a demo-mode bypass in five admin actions; additional CSRF/demo-mode gaps fixed
License compliance pass: SPDX headers, NOTICE file, canonical FSF license texts, and initials-on-color avatars replacing bundled stock photos
spw_* system tables now carry COMMENT ON descriptions for easier DB introspection

Following this series?

OpenSparrow v3.0 - GDPR Anonymization Goes Production, User Records/Comments, and Ollama Cloud

Websites

opensparrow.org
github.com/wrobeltomasz/OpenSparrow
demo.opensparrow.org