OpenSparrow v3.6 adds an External API module

Overview

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.

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 HMACkey_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

Endpoint Details

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

Response Codes

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.

Changes

• 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.

Fixes

• 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.

Series Continuation

Following this series? OpenSparrow v3.5 - Language Selector, Searchable FK Pickers and Notification Timestamps Websites https://opensparrow.org