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

Testing got postponed until tomorrow—what a nightmare when you're deep into CLI workflows and suddenly face transferring 370GB of data, only to realize you have to start over.

HestiaCP (an open-source control panel) is great for testing, but beware: your work can get overwritten if you skip the manual or overlook hosting agent quirks.​

Never rsync across VPSes when using a cPanel-like system—some configs must be handled at the panel level. No matter your command-line mastery, tools like Hestia on Ubuntu replace your daily vocab; skip them, and you'll loop endlessly, clueless about what's happening (unless you're an expert). Sharing this lesson anyway—lol.

(Did I mention the script actually works? We're just double-checking every variable with utmost care to ensure it's production-ready and gets the official approval stamp—no matter how scary that process feels—guaranteeing successful end results.)

Update and Apologies

We’ve encountered several challenges setting up the development (replica) environment for a particularly complex and large-scale storage system spread across multiple symlinks.

I’m confident that before the end of today, testing will be underway and results shared for review and feedback. Most importantly, we’ve achieved a major breakthrough — successfully eliminating old and unused records from the system

It is deleting as planned but we still double check and cross examine including table counts etc...

BUT IT WORKS HEY... IT DOES... you here me @Michael Newton 😉

ONE FINAL TEST TO GO...

Script Works But Can't Validate Deleted Post/Repost Records

Status: We've successfully run the timeline cleanup script targeting  timeline_common_post  and  timeline_common_repost events older than 30 days. Script executed without errors and table sizes reduced significantly.

The Issue - Need Validation Help:

We cannot confirm if the script deleted exactly the records we expected (old posts/reposts >30 days). Post-cleanup queries return 0 expected records, but we need team confirmation this is correct behavior.

We've ran our own internal sql queries but still with great concern whether we could have missed something. How would the experts query tables and which ones..

@unateam @Michael Newton

Regarding validation - here's what I need:

Before running the cleanup script, I want to run two SQL queries to verify the counts:

Example scenario:

• Total posts: 1000
• Posts older than 30 days: 700
• Posts within 30 days: 300

Query 1 (old posts to delete):

sql

SELECT COUNT(*) FROM bx_timeline_events 
WHERE type IN ('timeline_common_post', 'timeline_common_repost') 
AND date < UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 30 DAY));

Expected result: 700

Query 2 (recent posts to keep):

sql

SELECT COUNT(*) FROM bx_timeline_events 
WHERE type IN ('timeline_common_post', 'timeline_common_repost') 
AND date >= UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 30 DAY));

Expected result: 300

After cleanup completes: Total remaining should be exactly 300.

Question: Are these queries accurate for distinguishing old vs. recent timeline posts in UNA?

@unateam @Michael Newton @Clubhouse @LeonidS

I've made it much more easier for any of our experts to respond. Time is against us and force us to look at alternative storage solutions. Things look good so to speak but we need to clean our house first and who knows...

We're ready to move forward with this cleanup script and just need a YES confirmation from UNA experts before deploying to production.

Purpose of this test:

I want to temporarily modify the script to validate two fundamental coding principles:

The delete function - We're using the same deleteEvent() method that UNA's core uses for timeline deletions

The event type identification - We've confirmed timeline_common_post and timeline_common_repost are the correct types

Test plan:

Temporarily delete the newest 10 posts (easy to visually confirm on the timeline), verify:

php$dryRun = false; // Actually delete

// Get NEWEST 10 posts for easy visual confirmation

$eventIds = $db->getColumn("

   SELECT `id`

   FROM `bx_timeline_events`

   WHERE `type` IN ($typeList)

   ORDER BY `date` DESC

   LIMIT 10

");

Change: ASC → DESC (descending = newest first)

Posts disappear from timeline ✓

Media files removed from storage ✓

No database errors ✓

Confidence level:

I'm highly confident we're on the right track because:

Using UNA's native deletion method (not custom SQL)

Verified correct event types from database schema

Successfully tested on dev server (165 deletions, zero failures)

Just need expert confirmation that our approach is sound before production implementation.

Thanks Guys!

Chris

@unateam @Michael Newton @Clubhouse @LeonidS

None of the tagged members has responded yet, but my expectation for responding after I've simplified the thread was as follow:

From the Timeline UI on our unacms systems can we delete records right, we can add and even modify... but deleting is what I want to focus on.

From our thread discussion can we learn that by using the same DeleteFuction() guaranty us a 100% deletion across relational table and that we've identified the EvenTypes.

Now we've come along way using symlinks in our unacms system meaning that if users deleted records during this period that YES, so did they delete the related symlinks records too.

Having said that in it's own proves that what we intent to achieve with the script complies 100% to the theory that we not doing anything extra or less

The formula therefor is a bullet proof concept and should have raised votes across the unacms community especially tagged members. But I am confident enough that going to production should give us what we expect coming from extensive testing on dev that it works.

@unateam @Michael Newton @Clubhouse @LeonidS

Timeline Cleanup Script - Success Report

Great news! We've successfully tested and deployed the timeline cleanup script discussed earlier in this thread.

Results:

The script performed flawlessly across multiple environments:

• ✅ Dev server: Clean deletions with proper media file removal
• ✅ Symlinked storage: Files correctly deleted from remote NFS mounts
• ✅ Production deployment: Zero orphaned records, zero failures

Key Validations:

✓ Database records properly removed

✓ Physical media files deleted from disk (verified storage size decrease)

✓ All dependent data cleaned up (comments, reactions, mappings)

✓ Works perfectly with symlinked remote storage

✓ No orphaned files or broken references

Technical Approach:

Used UNA's native  BxDolModule::getInstance('bx_timeline')->deleteEvent() method, which ensured all storage hooks and cleanup processes fired correctly. This is the same deletion method UNA's admin interface uses internally.

For anyone needing similar cleanup:

The script safely handles timeline post deletion with proper media cleanup. Happy to share implementation details if others need similar functionality.

Thanks to everyone who provided input and validation during testing! 🙏

Good — with timeline_common_post + timeline_common_repost confirmed, your test plan is sane. Here’s what I’d tighten so this “newest 10” test is max-safety and also proves you’re deleting exactly what you intend.

1) Build $typeList safely (no SQL injection / quoting bugs)

Don’t inline $typeList as a raw string unless you’re 100% sure it’s hardcoded. Use proper quoting:

$types = ['timeline_common_post', 'timeline_common_repost'];
$typeList = "'" . implode("','", array_map('addslashes', $types)) . "'";

(You can also parameterize, but many UNA DB helpers don’t support expanding IN lists cleanly.)

2) Add “guard rails” to ensure you only delete “normal” timeline items

Even with correct types, it’s smart to exclude protected flags if they exist in your schema:

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

If any of those columns don’t exist in your installation, drop them; if they do, keep them.

3) “Newest 10” selection query (corrected + safe)

Use DESC (newest first) and a deterministic tie-breaker:

$eventIds = $db->getColumn("
    SELECT `id`
    FROM `bx_timeline_events`
    WHERE `type` IN ($typeList)
      AND `system` = 0
      AND `sticked` = 0
      AND `promoted` = 0
    ORDER BY `date` DESC, `id` DESC
    LIMIT 10
");

That id DESC prevents weirdness if multiple events share the same date.

4) Delete via the most stable “native” API (recommended)

If your current method is bx_srv('bx_timeline','delete',[$id]), keep it. That’s the safest “public surface” in UNA and avoids method-name drift across versions.

Inside your loop:

$ok = bx_srv('bx_timeline', 'delete', [$eventId]);

If you’re using $timeline->deleteEvent($aEvent) and it works on your version, fine — but bx_srv is the safer long-term choice.

5) Prove storage cleanup beyond “files disappeared.”

To be absolutely sure you’re not leaving orphaned records, add optional post-delete checks:

• Confirm the event row is gone:

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

• If you know your install’s storage object(s) used by timeline posts (from your prior storage config query), spot-check that the associated storage rows are removed too. (Exact tables depend on the storage engine, so I won’t guess names here.)

6) One more operational safety switch

Make it CLI-only for live deletes:

if (!$dryRun && php_sapi_name() !== 'cli') {
    die("LIVE mode allowed only via CLI.\n");
}

This prevents someone from hitting the URL and nuking content.

Verdict on your plan

• Change ASC → DESC for the “newest 10” confirmation run: correct?
• Your confidence claims are reasonable, provided you are truly deleting via UNA’s service/module layer (not direct SQL), which you are.
• The only thing I’d still call out as “gotcha potential” is forgetting the protected flags (system/sticked/promoted) and accidentally removing something curated/pinned.

If you paste your exact current deletion call (one line: either bx_srv(...) or $timeline->...), I’ll tell you if there’s any version-specific risk with it and what the “most compatible” alternative is for UNA 14/15.

Thanks @Michael Newton ! Really appreciate the detailed safety checks.

Confirming our approach:

1. Type list safety: ✅ We're using hardcoded array with proper quoting:

php

   $eventTypes = ['timeline_common_post', 'timeline_common_repost'];
   $typeList = "'" . implode("','", $eventTypes) . "'";

1. Guard rails: I'll add these checks. Quick question - in our UNA 14 schema, I should verify which of these columns exist:

sql

   DESCRIBE bx_timeline_events;

Then add any that exist  (system, sticked, promoted). Correct?

1. Newest 10 test clarification: The DESC order was purely for dev testing - I adjusted the Timeline module settings in Studio (sort order + filtered to show only these two event types) so we could visually confirm the deleted posts disappeared from the timeline immediately. For production, we're using ASC (oldest first) with the 90-day date filter.
2. Delete method: We're using:

php

   $timeline = BxDolModule::getInstance('bx_timeline');
   $result = $timeline->deleteEvent($aEvent);

This is working perfectly across dev/production (including symlinked NFS storage). Should we switch to  bx_srv('bx_timeline', 'delete', [$eventId])  for better version compatibility? What's the exact syntax?

1. Storage cleanup verification: Confirmed working - remote NFS storage decreased from 260GB → 259GB, orphaned file mappings = 0. The native  deleteEvent()  properly triggers all storage hooks.
2. CLI-only enforcement: ✅ Adding this.

Questions:

• For the bx_srv() approach, is it:  bx_srv('bx_timeline', 'delete', [$eventId]) or does it need the full event array?
• Any UNA 14-specific gotchas we should watch for?

Script has been rock-solid (165/200 successful, 35 cascade-deleted, 0 failures). Want to ensure we're future-proof for UNA 15+.

Thanks for the thorough review! 🙏