UNA 14 Timeline Cleanup Script – Expert Review Requested

Anyone back from holiday to spare some time on this thread, especially from the developers or unacms team. It is not only a concern or request benefiting me, but the solution could mean quite a lot for the entire unacms community.

Sometimes a demand don't exist until we are made aware of such and realize how we've missed out on the benefits it might holds.

The unacms framework currently don't support a data-only export tool; letting records count continuously grow with the only means of pruning membership, and so getting rid of unused related records, and so we are unaware really managing records in general.

Hey Dannie, Much ppreciated that the community responds and to answer you, yes, you are right and hope those who are unaware of the resizing of photos/images in particular can be set to reduce upload size. In my case, unfortunately, it is not the case, butI  seek the solution to periodically delete records and media that have become rather outdated or completely unused.

My appology for the delay respond as we were suffering power outages in our area.

First, I take no responsibility for any damages to projects resulting from these detailed suggestions.

BACK UP, BACK UP!!!! THEN TRY these steps.

I would not run that script on production as-is. It is very likely to (a) miss the real UNA data paths for timeline media, (b) leave orphans, and (c) potentially delete files that are not exclusively owned by the timeline event.

Below is a structured review and a safer pattern.

Key issues in the current script

1) Wrong tables for UNA 14 Timeline/comments/notifications (high probability)

From your own schema list earlier, you have bx_timeline_comments, not bx_timeline_cmts. UNA’s comments system is often abstracted and not always stored where people expect (there’s sys_cmts_* plus per-module comment tables depending on module/version). If you delete from the wrong table, you either:

• delete nothing (so media stays), or
• delete partial state (orphans elsewhere).

Same concern for notifications: your DB list shows bx_notifications_*, not bx_ntfs_*. So this block will likely be wrong on your install.

2) Deleting from bx_files is almost certainly not deleting the physical media

UNA media is typically stored via Storage objects (sys_files, sys_images, sys_storage_*, transcoders, ghosts/deletions queues). The “Timeline post has images/videos” usually means:

• timeline event references storage IDs (and transcoder outputs), not a “bx_files module row” you can safely delete.
• deleting a row from a module table does not guarantee storage cleanup (and can actually break cleanup).

So even if your SQL deletes some module mapping rows, disk usage likely won’t drop as expected.

3) Ownership checks are insufficient / can be wrong

DELETE FROM bx_files WHERE id IN (...) AND owner_id=... assumes:

• those file rows are unique to that timeline post,
• and owned by that event owner.
• Both assumptions are risky. Reused media, shared attachments, or cross-module references can exist.

4) Bypasses UNA’s internal delete hooks

UNA deletion typically triggers:

• storage delete / garbage collection
• transcoders cleanup
• votes, reactions, reports, views, metas
• alerts/events for other modules
• Your script deletes “a few obvious tables” but does not run those hooks. That’s the main reason orphaned data accumulates.

5) escape() use is not what you think

You cast to (int), which is good. But then you do $db->escape($eventId) in numeric contexts. Many escape implementations return quoted strings. It often “works,” but it’s not guaranteed and it can produce odd SQL. For ints, use ints.

6) Operational risks

• No batching/limits: could run for a long time, lock tables, and spike load.
• Lock file TTL is 300s; a real run could exceed that and allow overlap if cron triggers again.
• Log path /var/log/... will often fail on shared hosting unless permissions are configured.

What you should do instead (safe UNA-native approach)

Preferred approach: delete via the Timeline module / service API

The safe pattern is:

1. Bootstrap UNA (inc/header.inc.php)
2. Load the Timeline module instance
3. For each event ID, call the module’s delete method (or service) that UNA uses internally.

This ensures:

• storage objects are notified,
• dependent system tables are cleaned,
• transcoders/ghosts/deletions are handled correctly.

Because I cannot execute your server code, I’ll describe the pattern you should use:

• Use module instance: BxDolModule::getInstance('bx_timeline')
• Then call the module’s delete entry/event method (often something like deleteEvent(), deleteEntity(), or service wrapper).

If you open modules/boonex/timeline/classes/BxTimelineModule.php and search for delete methods, you’ll typically find the correct callable that UNA expects you to use (and Studio/admin UI uses).

Alternative approach: “soft delete” then let storage GC do its job

If you’re nervous about hard deletes:

• Mark old posts as hidden/archived (status update)
• Run UNA maintenance / storage cleanup to purge unreferenced files

This reduces risk of accidental data loss and still gets you disk back once garbage collection runs.

Concrete improvements you can apply immediately

A) Start with a “list-only” dry run that does not touch dependent tables

Your current dry-run is already querying the wrong comment table and wrong file linkage table. First, confirm the real schema relationships by querying bx_timeline_events to see how media is referenced.

Run this in phpMyAdmin:

SELECT id, owner_id, type, content, date
FROM bx_timeline_events
WHERE type='post'
ORDER BY date DESC
LIMIT 5;

Look at the content column. If it contains serialized/JSON references to storage objects (common), you must use the module delete routine, not manual deletes.

B) Add batching and time-boxing

Delete in batches (e.g., 200 events per run), then exit cleanly. This avoids timeouts and reduces lock/load risk.

C) Use a lock that cannot expire mid-run

Example: create lock file, write PID + timestamp, and only release at end. Don’t rely on a short TTL.

D) Log to a writable path

Use something like:

• BX_DIRECTORY_PATH_LOGS . 'timeline_cleanup.log' (if available), or
• __DIR__ . '/timeline_cleanup.log' inside your UNA root (temporarily)

A safer skeleton (still requires you to map the correct delete call)

This is what a production-safe structure looks like, conceptually:

require_once __DIR__ . '/inc/header.inc.php';

$daysOld = 60;
$dryRun  = true;
$batch   = 200;

$cutoff = time() - (86400 * $daysOld);
$db = BxDolDb::getInstance();

$eventIds = $db->getColumn("
    SELECT `id`
    FROM `bx_timeline_events`
    WHERE `type`='post'
      AND `date` < :cutoff
      AND `status` <> 'hidden'
    ORDER BY `date` ASC
    LIMIT :batch
", ['cutoff' => $cutoff, 'batch' => $batch]);

$timeline = BxDolModule::getInstance('bx_timeline');
if (!$timeline) die('Timeline module not found');

foreach ($eventIds as $eventId) {
    $eventId = (int)$eventId;

    if ($dryRun) {
        // log "would delete"
        continue;
    }

    // Replace this with the ACTUAL timeline deletion call from your install:
    // e.g. $timeline->deleteEvent($eventId);
    // or BxDolService::call('bx_timeline', 'delete_event', [$eventId]);
}

Important: I’m intentionally not inventing the final call name. You must use the method/service your installed Timeline module provides, because that’s what guarantees storage cleanup.

What I need from you to give the exact final delete call

Paste the relevant portion of one of these files (just the delete-related methods, ~50–150 lines is enough):

1. modules/boonex/timeline/classes/BxTimelineModule.php (search for function delete)
2. modules/boonex/timeline/classes/BxTimelineDb.php (search for delete/remove)
3. Any service definitions for timeline (search in the module for service methods)

With that snippet, I can tell you exactly which method/service to call and how to structure the script so it:

• deletes the event,
• deletes attached media via Storage,
• triggers required cleanup across comments/reactions/views/reports,
• and doesn’t accidentally delete shared files.

One more operational note

Because you previously had a lot of collation inconsistency and PHP notices, I’d also recommend:

• Run this cleanup only after your DB collation normalization is complete.
• run it during low traffic,
• and keep $dryRun=true until you’ve validated that disk usage decreases via UNA’s storage tables/paths.

If you paste the delete-method snippet from BxTimelineModule.ph

I’ll produce a production-ready script that uses the correct UNA API and a safe batch/lock/log design.

@Michael Newton Thank you SO much for sharing this timeline cleanup script! 🙏

You've literally saved us from a storage crisis. We've set up a proper **test environment** first (no live data risk!) and will thoroughly validate everything before production.

Your disclaimer is noted and appreciated - we completely understand and take full responsibility.

If this works (and I have every confidence it will), your time and effort will be **remembered for years**. This could become the go-to solution for every UNA site owner hitting disk limits.

**Community hero status achieved!** 🏆

Will report back with test results. Massive thanks again! 🚀

@unateam

If this timeline cleanup script proves stable in testing (which I'm confident it will), would the UNA team consider **packaging it as an official free Studio module**?

Since Michael Newton openly shared it with the community, perfect opportunity to:

- Make it easily installable for everyone

- Add admin settings (days threshold, dry-run toggle, log viewing)

- Official UNA quality stamp + support

**"Michael Newton Timeline Cleanup Module"** – his name remembered forever as the solution that saved countless UNA sites from storage crises! 🏆

What do you think? Could be a fantastic **free community module** addition.

Thanks again @Michael Newton for kickstarting this! 🙏

@Michael Newton ..

Sorry for the delayed response — things have been quite busy on our side. We’re in the process of procuring a VPS to replicate the live server (around 380–400GB). Since we don’t have a dedicated development server, this VPS will allow us to test safely outside of production. It’ll be a temporary setup, just for thorough testing and validation.

As per your request, I extracted the Delete Function from the BxTimelineModule.php file :

public function deleteEvent($aEvent)

  {

   if(empty($aEvent) || !is_array($aEvent) || !$this->_oDb->deleteEvent(array('id' => (int)$aEvent['id'])))

      return false;

    $this->onDelete($aEvent);

    return true;

  }

  public function getFormPost($aParams = [])

  {     

    $CNF = &$this->_oConfig->CNF;

    $iUserId = $this->getUserId();

    $oForm = $this->getFormPostObject($aParams);

    $aFormInitCheckerParams = [];

    $bValues = !empty($aParams['values']) && is_array($aParams['values']);

    if($bValues) {

      $this->_prepareFormForAutoSubmit($oForm, $aParams['values']);

      $aFormInitCheckerParams = [[], $aParams['values']];

      unset($aParams['values']);

    }

    call_user_func_array([$oForm, 'initChecker'], $aFormInitCheckerParams);

    $bAjaxMode = $oForm->isAjaxMode();

    $bDynamicMode = $bAjaxMode;

--

  public function deleteLinksUnused($iProfileId)

  {

    $aLinks = $this->_oDb->getUnusedLinks($iProfileId);

    if(empty($aLinks) || !is_array($aLinks))

      return;

    $oStorage = BxDolStorage::getObjectInstance($this->_oConfig->getObject('storage_photos'));

    foreach($aLinks as $aLink)

      if(!empty($aLink['media_id']))

        $oStorage->deleteFile($aLink['media_id']);

    $this->_oDb->deleteUnusedLinks($iProfileId);

  }

  public function deleteCacheItem($iEventId)

  {

    //--- Delete own item cache.

    $oCacheItem = $this->getCacheItemObject();

    $aCacheKeys = $this->_oConfig->getCacheItemKeys($iEventId);

    foreach($aCacheKeys as $sCacheKey)

      $oCacheItem->delData($sCacheKey);

    //--- Delete item cache for reposts.

    $aReposts = $this->_oDb->getReposts($iEventId);

    foreach($aReposts as $aRepost)

      $this->deleteCacheItem($aRepost['event_id']);

  }

  public function rebuildSlice()

  {

    if(!$this->_oConfig->isCacheTable())

      return;

    $this->_oDb->rebuildSlice();

  }

  /**

   * Protected Methods 

   */

  protected function _serviceGetBlockView($iProfileId = 0, $aBrowseParams = array())

  {

    if(empty($iProfileId) && bx_get('profile_id') !== false)

      $iProfileId = bx_process_input(bx_get('profile_id'), BX_DATA_INT);

@Michael Newton

Please examine code for your approval and correction and yes, the above method is included: Final Code from my side... The minute I have the replica setup, I will start testing and give feedback to the community how things go

<?php

/**

* UNA Timeline Cleanup Script - Safe Version

* Deletes old timeline posts and their associated media files properly

*

* IMPORTANT: Test with $dryRun = true first!

*/

// Bootstrap UNA

require_once __DIR__ . '/inc/header.inc.php';

// ===== CONFIGURATION =====

$daysOld = 60; // Delete posts older than this many days

$dryRun = true; // SET TO FALSE when ready to actually delete

$batch = 200; // Process this many posts per run

$lockFile = __DIR__ . '/timeline_cleanup.lock';

$logFile = __DIR__ . '/timeline_cleanup.log';

// ===== LOCK CHECK (prevent concurrent runs) =====

if (file_exists($lockFile)) {

$lockAge = time() - filemtime($lockFile);

if ($lockAge < 3600) { // 1 hour lock timeout

die("Script is already running (lock file exists)\n");

}

unlink($lockFile); // Remove stale lock

}

file_put_contents($lockFile, getmypid() . "\n" . date('Y-m-d H:i:s'));

// ===== SETUP =====

$cutoff = time() - (86400 * $daysOld);

$db = BxDolDb::getInstance();

// Start logging

$logMsg = "\n" . str_repeat('=', 60) . "\n";

$logMsg .= "Timeline Cleanup Run: " . date('Y-m-d H:i:s') . "\n";

$logMsg .= "Mode: " . ($dryRun ? "DRY RUN (no deletions)" : "LIVE (deleting)") . "\n";

$logMsg .= "Cutoff date: " . date('Y-m-d H:i:s', $cutoff) . "\n";

$logMsg .= str_repeat('=', 60) . "\n";

file_put_contents($logFile, $logMsg, FILE_APPEND);

// ===== GET OLD EVENTS =====

try {

$eventIds = $db->getColumn("

SELECT `id`

FROM `bx_timeline_events`

WHERE `type` = 'post'

AND `date` < ?

ORDER BY `date` ASC

LIMIT ?

", [$cutoff, $batch]);

} catch (Exception $e) {

$error = "ERROR: Database query failed: " . $e->getMessage() . "\n";

file_put_contents($logFile, $error, FILE_APPEND);

unlink($lockFile);

die($error);

}

if (empty($eventIds)) {

$msg = "No old events found to process.\n";

echo $msg;

file_put_contents($logFile, $msg, FILE_APPEND);

unlink($lockFile);

exit(0);

}

echo "Found " . count($eventIds) . " events to process\n";

file_put_contents($logFile, "Found " . count($eventIds) . " events\n", FILE_APPEND);

// ===== LOAD TIMELINE MODULE =====

$timeline = BxDolModule::getInstance('bx_timeline');

if (!$timeline) {

$error = "FATAL ERROR: Timeline module not found!\n";

file_put_contents($logFile, $error, FILE_APPEND);

unlink($lockFile);

die($error);

}

// ===== PROCESS DELETIONS =====

$deleted = 0;

$failed = 0;

foreach ($eventIds as $eventId) {

$eventId = (int)$eventId;

// Get event details for logging

$aEvent = $db->getRow("SELECT * FROM `bx_timeline_events` WHERE `id` = ?", [$eventId]);

if (empty($aEvent)) {

$msg = "Event $eventId not found (already deleted?)\n";

echo $msg;

file_put_contents($logFile, $msg, FILE_APPEND);

continue;

}

if ($dryRun) {

$msg = "[DRY RUN] Would delete event: $eventId (owner: {$aEvent['owner_id']}, date: " .

date('Y-m-d', $aEvent['date']) . ")\n";

echo $msg;

file_put_contents($logFile, $msg, FILE_APPEND);

$deleted++;

continue;

}

// ===== ACTUAL DELETION (uses UNA's native method) =====

try {

// This is the safe method that handles all cleanup:

// - Deletes from bx_timeline_events

// - Removes associated media files from storage

// - Cleans up comments, reactions, votes, reports

// - Triggers all necessary hooks

$result = $timeline->deleteEvent($aEvent);

if ($result) {

$deleted++;

$msg = "✓ Deleted event: $eventId (owner: {$aEvent['owner_id']})\n";

echo $msg;

file_put_contents($logFile, $msg, FILE_APPEND);

} else {

$failed++;

$msg = "✗ Failed to delete event: $eventId\n";

echo $msg;

file_put_contents($logFile, $msg, FILE_APPEND);

}

} catch (Exception $e) {

$failed++;

$msg = "✗ Exception deleting event $eventId: " . $e->getMessage() . "\n";

echo $msg;

file_put_contents($logFile, $msg, FILE_APPEND);

}

// Small delay to prevent overwhelming the system

usleep(50000); // 0.05 seconds

}

// ===== SUMMARY =====

$summary = "\n" . str_repeat('-', 60) . "\n";

$summary .= "Summary:\n";

$summary .= " Total found: " . count($eventIds) . "\n";

$summary .= " Successfully deleted: $deleted\n";

$summary .= " Failed: $failed\n";

$summary .= str_repeat('-', 60) . "\n\n";

echo $summary;

file_put_contents($logFile, $summary, FILE_APPEND);

// ===== CLEANUP =====

unlink($lockFile);

// ===== NEXT STEPS MESSAGE =====

if ($dryRun) {

echo "\n";

echo "DRY RUN COMPLETE - No actual deletions performed.\n";

echo "Review the log file: $logFile\n";

echo "\n";

echo "To perform actual deletions:\n";

echo "1. Check the log to verify correct events are targeted\n";

echo "2. Set \$dryRun = false in the script\n";

echo "3. Start with a small batch (e.g., \$batch = 10)\n";

echo "4. Monitor disk usage before/after\n";

echo "5. Gradually increase batch size if successful\n";

echo "\n";

} else {

echo "\nTo continue cleanup, run this script again.\n";

echo "It will process the next $batch events.\n";

}

Hey @Michael Newton

Hi Michael, we’re ready to begin testing after completing our prep project to replicate the live server. I’d appreciate it if you could stay on standby in case I need your advice or input.

There’s no need for you to engage in the full cycle — just keep an eye on things in the background. Also, anyone interested is more than welcome to join in; I truly believe that within each of us lies a hidden talent, and when we combine our strengths, we become unstoppable — the sky’s the limit.

Here’s a code review focused on safety + “will it really delete what you think it will delete” for UNA Timeline.

What’s good

• Dry-run mode + batch processing + logging + lock file are all solid operational guardrails.
• You’re not doing raw SQL deletes; you’re attempting to go through the module layer (good idea for storage cleanup, votes, comments, etc.).
• Your cutoff uses a UNIX timestamp, which matches how Timeline stores dates in many UNA installs (also consistent with UNA queries you’ll see in logs).

High-risk / likely-bug items

1) Your selection filter is probably wrong: WHERE type = 'post'

In UNA Timeline, type and action are used together (handlers join on te.type = alert_unit and te.action = alert_action as seen in typical timeline queries). ()

So type='post' is suspicious: in many setups, “post” is the action, not the type (type is usually the unit/module).

Safer approach:

• First, inspect what you actually have before deleting anything:

SELECT `type`, `action`, COUNT(*) c
FROM `bx_timeline_events`
GROUP BY `type`, `action`
ORDER BY c DESC;

Then set your delete criteria accordingly.

Also consider excluding system/pinned/promoted items (see suggestions below).

2) $timeline->deleteEvent($aEvent) may not exist / may not be the supported API

UNA publicly documents a service call to delete a timeline event by ID:

• bx_srv('bx_timeline', 'delete', [$iId]); ()

That’s the most “version-stable” way compared to calling an internal method name that may differ by release.

3) Script is dangerously callable from the web unless you block it

If this file is in your web root (or reachable), anyone who hits it could trigger deletions once $dryRun=false.

Minimum safety:

• Restrict to CLI:

if (php_sapi_name() !== 'cli') die("CLI only.\n");

Or require a secret token / IP allowlist / move outside web root.

Improvements I strongly recommend (practical)

A) Use the documented deletion service

Replace the deletion block with:

$result = bx_srv('bx_timeline', 'delete', [$eventId]); // documented Timeline service

This should still trigger the Timeline delete hooks (“bx_timeline”, “delete”), etc. ()

B) Don’t log owner_id blindly

Some Timeline versions and/or edge cases can differ. Your logging line assumes owner_id exists and is populated. It often does, but your selection query doesn’t use it anyway.

Safer: log owner_id only if set; also consider logging type + action:

$owner = $aEvent['owner_id'] ?? 'n/a';
$msg = "Would delete event $eventId (type={$aEvent['type']}, action={$aEvent['action']}, owner=$owner, date=" . date('Y-m-d', $aEvent['date']) . ")\n";

C) Exclude pinned/promoted/system events (optional but usually desired)

Common “don’t touch these” filters (adjust to your schema after inspecting):

AND `system` = 0
AND `sticked` = 0
AND `promoted` = 0

(Your own site may differ—verify columns exist.)

D) Dry-run summary wording is misleading

Right now, you increment $deleted++ in dry-run and print “Successfully deleted”. That’s fine internally, but misleading.

Suggestion:

• Track $wouldDelete in dry-run instead of $deleted.

A tightened-up “safe version” (minimal edits)

Below is a conservative patch showing the core changes (CLI-only + service delete + better logging). Keep your lock/log/batch as-is.

<?php
require_once __DIR__ . '/inc/header.inc.php';

if (php_sapi_name() !== 'cli') {
    die("CLI only.\n");
}

$daysOld = 60;
$dryRun  = true;
$batch   = 200;

$lockFile = __DIR__ . '/timeline_cleanup.lock';
$logFile  = __DIR__ . '/timeline_cleanup.log';

$cutoff = time() - (86400 * $daysOld);
$db = BxDolDb::getInstance();

$timeline = BxDolModule::getInstance('bx_timeline');
if (!$timeline) die("FATAL: Timeline module not found.\n");

/**
 * IMPORTANT:
 * Verify your real distribution of (type, action) BEFORE relying on this.
 * Run:
 *   SELECT type, action, COUNT(*) FROM bx_timeline_events GROUP BY type, action ORDER BY COUNT(*) DESC;
 */
$eventIds = $db->getColumn("
    SELECT `id`
    FROM `bx_timeline_events`
    WHERE `date` < ?
      AND `system` = 0
      AND `sticked` = 0
      AND `promoted` = 0
      AND `action` = 'post'
    ORDER BY `date` ASC
    LIMIT ?
", [$cutoff, $batch]);

foreach ($eventIds as $eventId) {
    $eventId = (int)$eventId;
    $aEvent = $db->getRow("SELECT * FROM `bx_timeline_events` WHERE `id` = ?", [$eventId]);
    if (!$aEvent) continue;

    $owner = $aEvent['owner_id'] ?? 'n/a';

    if ($dryRun) {
        echo "[DRY RUN] Would delete event {$eventId} (type={$aEvent['type']}, action={$aEvent['action']}, owner={$owner}, date=" . date('Y-m-d', $aEvent['date']) . ")\n";
        continue;
    }

    // Documented Timeline delete service by ID
    $ok = bx_srv('bx_timeline', 'delete', [$eventId]);
    echo $ok ? "✓ Deleted {$eventId}\n" : "✗ Failed {$eventId}\n";

    usleep(50000);
}

Notes:

• I used action='post' and excluded system/sticked/promoted as a sane default, but you must confirm with the distribution query first.
• bx_srv('bx_timeline','delete',[$id]) is the documented API. ()

What I’d do before the first live run

1. Run the (type, action) distribution query (above) and paste the top ~10 rows back here if you want me to tune the WHERE clause exactly.
2. Dry-run with $batch=10, verify the IDs correspond to the posts you actually consider “old timeline clutter”.
3. Only then flip $dryRun=false.

If you tell me what UNA version you’re on (or paste the output of the (type, action) count query), I’ll give you the correct, precise WHERE clause for your specific Timeline data.

I'll be around...

Hi @Michael Newton

Thank you so much for the comprehensive code review and for being on standby during this critical period. Your timing couldn't have been better—I really appreciate the detailed analysis.

Current Status

I did test the script on a dev environment (pulled from production backup), and it successfully reduced the record count as expected. However, I ran into display issues on the dev site due to symlinked storage that I forgot to account for when setting up the test environment. This was compounded by production hitting critical disk space (90%+ full), which pulled my attention away from the test script that I'd already started.

The good news: Production is now stable (55% disk usage) after moving media files to remote NFS storage and fixing some symlink/permission issues. The bad news: I haven't yet run the cleanup script on production due to the symlink complications discovered during testing.

Addressing Your Review Points

You're absolutely right about the high-risk items:

1. The type='post' filter - I need to run your distribution query first. I was making assumptions about the schema without verifying.
2. Using bx_srv('bx_timeline', 'delete', [$iId]) - This is exactly the kind of version-stable approach I should be using instead of calling internal methods directly.
3. Web accessibility - The CLI-only check is a must. I hadn't considered this could be triggered accidentally via web.

Next Steps Before Production Run

Before I run this on production, I'll:

1. Run the distribution query to verify actual type/action combinations:

sql

   SELECT `type`, `action`, COUNT(*) c
   FROM `bx_timeline_events`
   GROUP BY `type`, `action`
   ORDER BY c DESC;

1. Add the exclusion filters for system/pinned/promoted events (after verifying column names exist in my schema)
2. Switch to the documented service call (bx_srv) instead of direct method calls
3. Implement CLI-only restriction and improve dry-run logging clarity
4. Start with a very small batch (10-20 records) in dry-run mode to verify targeting

Question for You

My production environment is UNACMS 14 (I'll confirm exact version and post it). Once I run the distribution query, would you mind helping me craft the precise WHERE clause for my specific setup?

Also, regarding the symlinked storage issue I encountered in testing—when Timeline events are deleted via bx_srv('bx_timeline', 'delete', [$iId]), should it properly clean up associated media files even when they're symlinked to remote NFS storage? Or do I need additional cleanup steps?

Appreciation

Your tightened-up "safe version" is exactly what I needed. The improvements around:

• Logging type/action/owner for better audit trails
• Excluding system events
• Using documented APIs
• Misleading dry-run wording

...are all spot-on and will make this much more maintainable going forward.

Thanks again for the thorough review and for standing by. I'll post the distribution query results shortly.

Regards Chris

Hey @LeonidS ...

Thanks, buddy, but it turned out to be more of a team effort achieving this goal....

Understood on UNA CMS 14.x. Yes—once you paste the (type, action) distribution output, I can help you narrow it to a precise, low-risk WHERE clause that matches your instance’s real event taxonomy (and avoids collateral deletes).

Below is the exact workflow I recommend so the WHERE clause is grounded in your data, not assumptions.

1) Crafting the precise WHERE clause from your distribution query

Step A — Confirm what you’re actually deleting (taxonomy + sample rows)

Run the distribution query you mentioned:

SELECT `type`, `action`, COUNT(*) AS `cnt`
FROM `bx_timeline_events`
GROUP BY `type`, `action`
ORDER BY `cnt` DESC;

Then, for the top 5–10 combos (and any combo you think are user “posts”), pull a small sample to validate they are the intended target:

SELECT `id`, `type`, `action`, `owner_id`, `object_id`, `content`, `date`
FROM `bx_timeline_events`
WHERE `type` = 'X' AND `action` = 'Y'
ORDER BY `date` DESC
LIMIT 20;

This is what prevents the classic failure mode: deleting things that look like “posts” but are actually system-generated events, module activity, profile changes, etc.

Step B — Build a “candidate delete set” filter, then tighten it

In almost every UNA Timeline cleanup, the final WHERE ends up being:

1. Age cutoff
2. One or more specific (type, action) pairs
3. Explicit exclusions for system/admin/sticky/promoted/special events (based on what columns/flags exist in your schema)

A safe template (to be adapted to your actual columns/taxonomy) looks like:

WHERE `date` < {CUTOFF_UNIX_TS}
  AND (
        (`type` = '...' AND `action` = '...')
     OR (`type` = '...' AND `action` = '...')
     OR (`type` = '...' AND `action` = '...')
  )
  AND `owner_id` > 0
  -- Optional, only if these columns exist in your install:
  -- AND `system` = 0
  -- AND `sticked` = 0
  -- AND `promoted` = 0
  -- AND `pinned` = 0
;

Step C — Prove the WHERE clause before deleting anything

Before you run the deleter, validate with:

SELECT COUNT(*) AS `to_delete`
FROM `bx_timeline_events`
<WHERE_CLAUSE>;

And then eyeball a stratified sample:

SELECT `id`, `type`, `action`, `owner_id`, `date`
FROM `bx_timeline_events`
<WHERE_CLAUSE>
ORDER BY `date` DESC
LIMIT 200;

If you paste your distribution output, I’ll translate it into the minimal, correct WHERE for your environment (including exclusions that match what’s actually present in your schema).

2) Symlinked/NFS storage: will bx_srv('bx_timeline','delete',[$iId]) remove media?

What should happen

In UNA, deleting via the module service (bx_srv('bx_timeline','delete',[$eventId])) is the right call because it is expected to:

• delete the timeline event row
• delete any module-specific media references
• call into the storage layer to remove stored files (and their DB records)

So, in principle, the service delete is the mechanism that should properly clean up associated media—regardless of whether the underlying storage path is “local” or happens to be a symlink pointing at NFS.

Why symlink/NFS can still leave leftovers

Even with the correct service call, you can still end up with orphaned files if any of the following are true:

• Permission/ownership mismatch on the NFS mount (PHP user can delete DB rows but cannot unlink the remote file).
• realpath/symlink path validation inside the storage engine behaves unexpectedly (less common, but possible in hardened filesystem checks).
• NFS latency / transient I/O errors during unlink cause the physical delete to fail while the DB transaction succeeds (or vice-versa).
• Your timeline attachments are stored in a different storage object than you think (e.g., separate storage objects for photos/videos/files), and some objects are not being invoked by that specific deletion path (version/module variations).

The practical answer: rely on service delete, but add verification + “orphan sweep”

I recommend treating this as two-phase:

1. Primary deletion via bx_srv('bx_timeline','delete', ...) (what you are already doing)
2. Post-run verification that catches orphaned storage records/files, and optionally deletes them via the storage API (not manual unlink())

Verification approach (DB-level)

After deleting a small test batch, check whether storage DB rows tied to timeline are decreasing as expected.

Because table/object names can vary by install, the most robust approach is:

• Identify the storage object(s) used by timeline attachments via:

SELECT *
FROM `sys_objects_storage`
WHERE `object` LIKE '%timeline%';

Then for any relevant storage object, inspect its files table (commonly sys_storage_files or bx_storage_files, depending on UNA generation/customization). If your install uses sys_storage_files, you can usually filter by storage_id and confirm rows aren’t piling up without owners.

If you paste the result of the sys_objects_storage query (just the rows that match timeline), I can give you the exact orphan-detection SQL for your schema.

If you do need additional cleanup

Do not delete by filesystem path. Instead, delete orphans by calling the storage object’s delete method on file IDs. That ensures thumbnails/transcodings/variants and DB rows are handled correctly.

The “right” cleanup step is therefore:

• find orphaned file IDs (DB query)
• for each file ID, call the storage API delete for that storage object

I can provide a small, safe “orphan sweeper” routine once you confirm:

• the storage object name(s) for timeline, and
• the storage files table name in your install

What to paste next (and I’ll turn it into the exact WHERE)

1. Output of:

SELECT `type`, `action`, COUNT(*) AS `cnt`
FROM `bx_timeline_events`
GROUP BY `type`, `action`
ORDER BY `cnt` DESC;

1. Output of:

SELECT `object`, `engine`, `params`
FROM `sys_objects_storage`
WHERE `object` LIKE '%timeline%';

With those two, I’ll:

• produce the precise WHERE clause for your setup (including the safest exclusions), and
• tell you whether your symlink/NFS case should be fully covered by service delete or whether an orphan sweep is advisable in your environment.

You truly an asset @Michael Newton and thanks a million for you contribution.

Query 1: Timeline Event Distribution

Query 2: Timeline Storage Configuration

Michael, I've also added the schema columns to help us know what exclusions to add

Query 3: Schema Columns

Below is an exact “safest” WHERE clause tailored to your schema and your distribution results. It is conservative: it deletes only real user timeline posts, skips system-generated events, skips pinned/sticked/promoted, and only targets active/visible items.

What we will delete (whitelist)

From your distribution table, the only thing that represents the “native Timeline post” objects is:

• type = 'timeline_common_post' (action is blank / NULL in your data)

We will not touch:

• comment (65639) — these are comment events
• timeline_common_repost (3130) — reposts
• module-generated “added” events: bx_posts, bx_photos, bx_videos, bx_ads, bx_groups, bx_polls
• profile changes: bx_persons actions

Safest WHERE clause

Use this in your cleanup query:

WHERE `type` = 'timeline_common_post'
  AND (`action` IS NULL OR `action` = '')
  AND `date` < ?
  AND `system` = 0
  AND `status` = 'active'
  AND `status_admin` = 'active'
  AND `active` = 1
  AND `pinned` = 0
  AND `sticked` = 0
  AND `promoted` = 0
  AND `owner_id` > 0
  AND `object_id` > 0

Full query drop-in (recommended)

Replace your current selection block with:

$eventIds = $db->getColumn("
    SELECT `id`
    FROM `bx_timeline_events`
    WHERE `type` = 'timeline_common_post'
      AND (`action` IS NULL OR `action` = '')
      AND `date` < ?
      AND `system` = 0
      AND `status` = 'active'
      AND `status_admin` = 'active'
      AND `active` = 1
      AND `pinned` = 0
      AND `sticked` = 0
      AND `promoted` = 0
      AND `owner_id` > 0
      AND `object_id` > 0
    ORDER BY `date` ASC
    LIMIT ?
", [$cutoff, $batch]);

Why this is the safest

• Type/action whitelist ensures you only delete the Timeline’s own “common post” items, not module activity events or comments/reposts.
• system = 0 prevents removal of “system” entries.
• status/status_admin/active ensure you only remove content that is currently treated as active/visible by UNA.
• pinned/sticked/promoted = 0 avoids deleting highlighted content.
• owner_id/object_id > 0 avoids orphan/system rows.

One optional safety improvement (recommended)

Run a preview query in phpMyAdmin before going live:

SELECT `id`, `owner_id`, `date`, `type`, `action`, `system`, `status`, `status_admin`,
       `active`, `pinned`, `sticked`, `promoted`
FROM `bx_timeline_events`
WHERE `type` = 'timeline_common_post'
  AND (`action` IS NULL OR `action` = '')
  AND `date` < UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 60 DAY))
  AND `system` = 0
  AND `status` = 'active'
  AND `status_admin` = 'active'
  AND `active` = 1
  AND `pinned` = 0
  AND `sticked` = 0
  AND `promoted` = 0
ORDER BY `date` ASC
LIMIT 50;

If those 50 rows look exactly like “old timeline posts you truly want gone,” you are good to proceed.

If you want to also delete reposts safely later, we can add a second pass with type='timeline_common_repost' but I would keep that separate from the first run.

You're very kind.

Damn!!!, that was quick Michael and much appreciated...

Will run tests today on Dev with our new final script after restoring the remote storage data(Production symlink records) to a new vps and recreate those symlink to have a complete replica of production.

Will keep you and the rest of the unacms community posted...