freetables: A Dynamic Data Table Framework Built on Vanilla JS

Every business module in ShinobiApps has tables: invoices, partners, products, projects, notifications. Instead of writing table logic in each module, freetables provides a config-driven system: define your columns in a PHP array, and the framework handles server-side processing, filtering, sorting, pagination, caching, and CSRF — all backed by Tabulator.js on the frontend.

The interesting parts aren't the table rendering. They're the optimizations: cascading filter detection that avoids unnecessary queries, a cache that only works when no filters are active, and an identifier sanitizer that knows the difference between a column name and a SQL expression.

Architecture: No DI, No Services

freetables registers zero services in the DI container. It's two PHP files:

1. FreetablesSSP — static methods for server-side processing (queries, filtering, sorting, unique values)
2. FreetablesFetchTrait — controller trait for CSRF validation, config loading, and response building

class PartnerController
{
    use FreetablesFetchTrait;

    public function data(Request $request): Response
    {
        $config = $this->loadFreetablesConfig('fiskmkv', 'partners');
        return $this->fetchFreetablesData($request, $config, ['condition' => 'company_id = ?', 'bindings' => [$companyId]]);
    }
}

The whereResult parameter scopes rows to the current company. Row-level security via SQL, not application logic.

The Fetch Flow

Every data request follows this path:

1. validateFreetablesCsrf()     → 403 if invalid
2. ensureCacheManager()         → lazy init
3. applyGlobalSearch()          → build OR clause across searchable columns
4. mapDefaultSortToRequest()    → inject default sort if no frontend sort
5. FreetablesSSP::tabulatorSSP()
   ├─ Cascading filter?  → return only uniqueValues
   └─ Regular request?   → return data + totalCount + uniqueValues
6. Add fresh CSRF token to response

CSRF Double-Check

Every freetables request validates a CSRF token from multiple sources:

$csrfToken = $request->input('csrf_token');
if ($csrfToken === null || $csrfToken === '') {
    $csrfToken = $request->getHeaderLine('X-CSRF-TOKEN');
}

POST body first, X-CSRF-TOKEN header as fallback (for AJAX). After validation, a fresh token goes back in the response JSON. The frontend stores it and sends it on the next request. Token rotation happens on every data fetch — not just form submissions.

Cascading Filter Detection

Here's the optimization that saves the most queries. When a user changes a dropdown filter, the other dropdowns need to update their options — but we don't need the actual table data yet.

$isCascadingRequest = isset($request['cascadingFilters'])
    && $request['cascadingFilters'] === 'true';

$dataResult = null;
if (!$isCascadingRequest) {
    $dataResult = self::tabulatorQuery($request, $db, $table, ...);
}

$uniqueValues = [];
if (self::shouldFetchUniqueValues($request, $columnMapping)) {
    $uniqueValues = self::getUniqueValuesOptimized($db, $table, ...);
}

if ($isCascadingRequest) {
    return ['uniqueValues' => $uniqueValues, 'cascading' => true];
}

A cascading request skips the data query entirely. It only fetches unique values for dropdown population. The frontend fires a cascading request when a filter changes, then a full request when the user finishes selecting.

How Unique Values Cascade

The subtle part: when computing unique values for column A, the query applies filters from columns B and C — but not column A's own filter:

foreach ($request['filter'] as $filter) {
    $filterColumnName = $filterColumn['db'];

    // Skip current column for cascading
    if ($filterColumnName === $columnName) {
        continue;
    }

    // Apply filter from other columns
    switch ($type) {
        case 'in':
            $query->whereIn($filterColumnName, $filterValues);
            break;
        // ...
    }
}

If the user selects "Zagreb" in the city filter, the status dropdown shows only statuses for Zagreb records. But the city dropdown still shows all cities — because its own filter is excluded. This is how cascading filters work in tools like Excel. It's a small detail that prevents users from accidentally filtering themselves into an empty set.

Unique Value Caching

Computing distinct values requires a SELECT DISTINCT per column — expensive for large tables. The cache kicks in when there are no active filters:

$useCache = !$isCascading && self::$cache;

if ($useCache && !$hasActiveFilters) {
    $cacheKey = 'dt_unique_base:' . $table . ':' . md5(json_encode([
        'columns' => array_keys($dropdownColumns),
        'where' => $whereResult
    ]));

    $cached = self::$cache->get($cacheKey);
    if ($cached !== null) {
        return $cached;
    }
}

The cache key includes $whereResult — the row-level security clause. User A (company 1) and User B (company 2) get different cached results because their where differs. TTL: 10 minutes (CACHE_TTL = 600).

When filters are active, the cache is bypassed. Filtered unique values change with every filter selection — caching them would return stale data.

Maximum 500 unique values per column (MAX_UNIQUE_VALUES = 500). If a column has 10,000 distinct values, the dropdown shows the first 500. Beyond that, users should use text search, not a dropdown.

Identifier Sanitization

Columns in freetables configs can be simple names (status) or SQL expressions (CONCAT(first_name, ' ', last_name) AS full_name). The sanitizer needs to handle both:

private static function sanitizeIdentifier(string $identifier): string
{
    if (self::isSqlExpression($identifier)) {
        return self::sanitizeSqlExpression($identifier);
    }
    return preg_replace('/[^a-zA-Z0-9_]/', '', $identifier);
}

private static function isSqlExpression(string $identifier): bool
{
    return preg_match('/[\(\)]/', $identifier) ||
           preg_match('/\s+AS\s+/i', $identifier) ||
           preg_match('/\s+[\-\+\*\/]\s+/', $identifier);
}

Simple identifiers: strip everything except [a-zA-Z0-9_]. SQL expressions: allow parentheses, spaces, arithmetic operators, commas (for function arguments), dots (for table.column), and the AS keyword. This lets configs define computed columns without concatenating raw SQL.

Global Search

When the user types in the global search box, the trait builds a parameterized OR across all searchable columns:

$orParts = [];
$bindings = [];
foreach ($searchableColumns as $col) {
    $orParts[] = "`{$col}` LIKE ?";
    $bindings[] = "%{$globalSearch}%";
}
$searchCondition = '(' . implode(' OR ', $orParts) . ')';

The search term is bound as a parameter, not interpolated. Even though LIKE with leading % doesn't use indexes efficiently, it's safe from injection. For tables with millions of rows, you'd want full-text search — but for business tables with thousands of rows, LIKE %term% is fine and doesn't require additional infrastructure.

The where clause composition handles three formats: null (first condition), string (legacy, append with AND), and array (safe parameterized format with bindings). The array format is preferred:

return [
    'condition' => $whereClause['condition'] . ' AND ' . $searchCondition,
    'bindings' => array_merge($whereClause['bindings'] ?? [], $bindings)
];

Config Files

Each table has a config file in the module's Config/ directory. Example from fiskmkv partners:

// moduli/fiskmkv/Config/freetables_partners.php
return [
    'table_name' => 'fiskmkv_partners',
    'primary_key' => 'id',
    'globalSearch' => true,
    'defaultSort' => [['column' => 'name', 'dir' => 'asc']],
    'columns' => [
        ['title' => 'Naziv', 'sqlcolumnname' => 'name', 'searchbox' => 'yes'],
        ['title' => 'OIB', 'sqlcolumnname' => 'oib', 'searchbox' => 'yes'],
        ['title' => 'Grad', 'sqlcolumnname' => 'city', 'dropdown' => true],
        // ...
    ],
];

searchbox: 'yes' includes the column in global search. dropdown: true enables the cascading filter dropdown. The config drives everything — adding a new column to the table is a one-line change in the config array.

Why No JS Framework

freetables uses Tabulator.js — a vanilla JS data grid library. No React, no Vue, no build step. The config file generates the Tabulator initialization options server-side, and the frontend consumes them. Adding a table to a new page is: include the JS, call initFreetable(config).

This matters because business users don't need a SPA. They need a table that loads fast, filters correctly, and exports to CSV. Tabulator does all of that in 50KB of JS.

Up Next

Next up: LIMAlife: A Self-Hosted Personal AI Assistant That Nobody Else Has Built in PHP — encrypted personal memory, Gmail and Calendar integration, 7 proactive jobs that cross-reference meetings with invoices and legal cases, and the draft-then-approve email pattern.