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";

}