Database Stats Cleaning

Database Stats Cleaning

<code>tec_datacleaning</code> is a PrestaShop module to safely clean and maintain database tables that contain website statistics (connections, page views, guest records, 404 pages, searches, etc.). It provides an automated cron endpoint, a fast TRUNCATE option, and a safe batched DELETE fallback when TRUNCATE cannot be executed.

> Important: TRUNCATE and DELETE are destructive operations. Always run <code>dry_run</code> first and take a full database backup before performing destructive operations.

---

Key features

• Automated cron endpoint for scheduled cleaning: <code>/module/tec_datacleaning/cron</code> (authenticated via <code>secure_key</code>).
• <code>truncate=1</code> fast-clean option to empty selected statistic tables.
• <code>dry_run=1</code> mode for both truncate and clean operations to preview how many rows would be removed without changing the database.
• Batched <code>DELETE</code> fallback if <code>TRUNCATE</code> is not allowed (permission issues or foreign-key constraints) — configurable batch size.
• Default managed tables include <code>connections</code>, <code>connections_page</code>, <code>connections_source</code>, <code>guest</code>, <code>statssearch</code>, <code>pagenotfound</code> and <code>log</code> (log is always skipped for truncate operations).
• Admin UI shows a deterministic <code>secure_key</code> and a configuration form to select which tables to include and set the batch size.

---

Requirements

• PrestaShop 1.7+ (module tested in PS 1.7 / 8 / 9 environments).
• The DB user must have appropriate privileges for <code>DELETE</code> operations. <code>TRUNCATE</code> requires elevated privileges and can be blocked by FK constraints.

---

Installation

1. Upload the <code>tec_datacleaning</code> module folder to your PrestaShop <code>modules/</code> directory. 2. Install the module from the PrestaShop Back Office (Modules -> Module Manager). 3. Open the module configuration page and review the pre-selected tables. Adjust the <code>batch_size</code> and the number of months to keep as needed.

Notes:

• By default the module pre-selects a safe list of statistic tables. The <code>ps_log</code> table is intentionally skipped for truncation to avoid losing log history.

---

Configuration (Back Office)

• <code>Module secure key</code>: a deterministic key shown on the form (may be computed by the module or read from configuration). Use this value in cron URLs.
• <code>How long to keep the data</code>: choose how many months of data to retain for cleaning operations.
• <code>Tables to clean</code>: checkbox list of allowed statistic tables — pick the ones you want included in automated cleaning.
• <code>Batch size</code>: number of rows processed per batch when performing batched DELETE operations.

Notes about secure_key and the form

• The module attempts to compute a deterministic secure key (based on <code>_COOKIE_KEY_</code> and the module name) when no explicit key is stored. If no key can be computed the module returns the sentinel value <code>NOKEY</code> and the cron endpoint will refuse requests until a valid key is configured in the BO.
• When upgrading from older versions: previously the module used PHP <code>serialize()</code> to store selected tables. For security, the module now stores selected tables as JSON. If legacy serialized data exists, the module intentionally ignores it and logs a message — please open the module configuration and re-save the table selection to migrate the stored value to JSON.

---

Cron / Endpoint usage

The cron endpoint is:

http(s)://<your-shop>/module/tec_datacleaning/cron?secure_key=<SECURE_KEY>

Replace <code>&lt;SECURE_KEY&gt;</code> with the value shown in the module configuration (read-only or the value you set in BO).

Example: Dry-run (safe)

This command returns the counts of rows that would be removed (no changes):

curl -s "https://example.com/module/tec_datacleaning/cron?secure_key=YOUR_SECURE_KEY&dry_run=1" | jq .

Example: Run cleaning (non-destructive deletes in batches)

curl -s "https://example.com/module/tec_datacleaning/cron?secure_key=YOUR_SECURE_KEY&batch_size=1000" | jq .

Truncate examples (fast cleanup, destructive)

Dry-run for truncate (reports rows that would be removed):

curl -s "https://example.com/module/tec_datacleaning/cron?secure_key=YOUR_SECURE_KEY&truncate=1&dry_run=1" | jq .

Real truncate (DESTRUCTIVE — empties selected tables, except <code>ps_log</code>):

curl -s "https://example.com/module/tec_datacleaning/cron?secure_key=YOUR_SECURE_KEY&truncate=1" | jq .

---

secure_key: how it is generated (CLI)

You can compute the same deterministic secure key on the server by running (from your PrestaShop root):

php -r "require 'config/config.inc.php'; echo md5(_COOKIE_KEY_ . 'tec_datacleaning') . PHP_EOL;"

If the module configuration contains a user-provided value (<code>TEC_DATACLEANIG_SECURE_KEY</code>) that value is used in preference to the computed one.

If the module cannot compute or read any valid key it will return the <code>NOKEY</code> sentinel and the cron endpoint will respond with a clear error. In that case open the module configuration and provide a valid secure key.

---

Behaviour details

• Authentication: the cron endpoint requires <code>secure_key</code> as the authentication parameter.
• <code>dry_run=1</code>: returns statistics (counts) and does not perform any DELETE/TRUNCATE.
• <code>truncate=1</code>: attempts a fast <code>TRUNCATE TABLE</code> for each selected table (skips <code>ps_log</code>). If <code>TRUNCATE</code> fails or is not permitted, the module attempts a batched <code>DELETE</code> using the configured <code>batch_size</code> until the table is empty or a safety iteration limit is reached.
• <code>batch_size</code> applies to batched DELETE operations (not to TRUNCATE).

---

Safety & recommendations

• Always perform a <code>dry_run</code> first and keep a recent database backup before executing destructive operations.
• Prefer running truncate only during a low-traffic window.
• If your database uses InnoDB and has foreign-key constraints, <code>TRUNCATE</code> may fail: the module will attempt a batched <code>DELETE</code> but FK constraints can still block deletion. Review constraints before running destructive operations.
• Consider limiting access to the cron URL (IP whitelist or VPN access) to reduce the risk of unauthorized calls.

---

Troubleshooting

• If you receive <code>Invalid token</code> in the JSON response, verify that <code>secure_key</code> matches the computed or configured value.
• If TRUNCATE fails and the fallback <code>DELETE</code> reports zero deleted rows, examine DB permissions and foreign-key constraints. Check server error logs for DB error messages.
• If you see permission errors, ensure your DB user has <code>DELETE</code> privileges. For TRUNCATE, database-level privileges may be required.

---

Changelog

See <code>CHANGELOG.md</code> in the module folder for release notes and details about recent changes (version 1.0.4 contains security fixes and the migration to JSON storage for selected tables).

---

Support

For support and further customization contact the module author or your technical partner. If you provide logs and a description of steps to reproduce, troubleshooting will be faster.

---

License

Refer to the <code>LICENSE</code> file included in the module for license terms and usage rules.