How to Set Up a Transparent Cashback History in Bitrix Personal Account

Transparent Cashback History: A Comprehensive Setup Guide for Bitrix Personal Account

A user sees their cashback balance but has no idea where it came from. 250 rubles were credited—for which order? 100 were debited—when and on what purchase? Without a transparent transaction history, any loyalty program breeds distrust. And simply "showing a table from the database" isn't enough: you need proper pagination, filtering by transaction type, and correct timezone handling.

We solve this problem comprehensively: we design the transaction table, create a component with page navigation, set up the link to orders, and—if needed—implement a cashback expiration mechanism. Our experience: 12+ years of 1C-Bitrix development, 80+ successful ecommerce projects. We guarantee the transaction history will work fast even with 100,000+ transactions per user. Typical page load: under 150ms.

Understanding the Need and the Solution

Without transparency, users doubt the program's fairness, which reduces engagement. Studies show that 70% of buyers participate more actively in loyalty programs that provide a detailed bonus statement. That's why we pay special attention to data architecture and interface usability. Our approach reduces support tickets about cashback by 40%.

How We Implement the Transaction Table

The transaction history is stored in the local_cashback_transactions table. We use an optimized structure with an index on (USER_ID, CREATED_AT DESC)—this speeds up queries by 10x compared to a full table scan.

CREATE TABLE local_cashback_transactions (
    ID          BIGINT AUTO_INCREMENT PRIMARY KEY,
    USER_ID     INT NOT NULL,
    TYPE        ENUM('accrual','debit','reserve','release','expire','manual') NOT NULL,
    AMOUNT      DECIMAL(10,2) NOT NULL,
    ORDER_ID    INT,
    PAYMENT_ID  INT,
    DESCRIPTION VARCHAR(500),
    CREATED_AT  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    EXPIRES_AT  DATETIME,
    INDEX idx_user_date (USER_ID, CREATED_AT DESC)
);

The index is mandatory: without it, fetching the last 6 months of history for an active user with thousands of transactions would require a full scan.

Transaction Types and Filtering

The component accepts the GET parameter type. In class.php, we verify the value is in the allowed list and apply the filter to the query. This lets users see only relevant operations: for example, only accruals or only debits.

Building the Transaction History Component

We create the component /local/components/local/cashback.history/. Structure:

class.php        — business logic
 templates/.default/template.php  — template
 lang/ru/         — language files

class.php extends CBitrixComponent and uses D7 ORM to work with the table. For more on the component approach, see the official 1C-Bitrix documentation.

class CashbackHistoryComponent extends CBitrixComponent
{
    public function executeComponent(): void
    {
        if (!$this->getUser()->isAuthorized()) {
            ShowError('Access denied');
            return;
        }

        $userId    = (int)$this->getUser()->GetID();
        $pageNum   = max(1, (int)($_GET['page'] ?? 1));
        $pageSize  = (int)($this->arParams['PAGE_SIZE'] ?? 20);
        $typeFilter = $_GET['type'] ?? '';

        $filter = ['USER_ID' => $userId];
        if (in_array($typeFilter, ['accrual', 'debit', 'expire'])) {
            $filter['TYPE'] = $typeFilter;
        }

        $totalCount = CashbackTransactionTable::getCount($filter);

        $transactions = CashbackTransactionTable::getList([
            'filter' => $filter,
            'order'  => ['CREATED_AT' => 'DESC'],
            'limit'  => $pageSize,
            'offset' => ($pageNum - 1) * $pageSize,
            'select' => ['ID', 'TYPE', 'AMOUNT', 'ORDER_ID', 'DESCRIPTION', 'CREATED_AT', 'EXPIRES_AT'],
        ])->fetchAll();

        // Load order numbers in one query
        $orderIds = array_filter(array_column($transactions, 'ORDER_ID'));
        $orderNumbers = [];
        if ($orderIds) {
            $res = \Bitrix\Sale\Internals\OrderTable::getList([
                'filter' => ['ID' => $orderIds],
                'select' => ['ID', 'ACCOUNT_NUMBER'],
            ]);
            while ($row = $res->fetch()) {
                $orderNumbers[$row['ID']] = $row['ACCOUNT_NUMBER'];
            }
        }

        $this->arResult = [
            'BALANCE'       => CashbackBalanceTable::getBalance($userId),
            'TRANSACTIONS'  => $transactions,
            'ORDER_NUMBERS' => $orderNumbers,
            'TOTAL_COUNT'   => $totalCount,
            'PAGE_NUM'      => $pageNum,
            'PAGE_SIZE'     => $pageSize,
            'TYPE_FILTER'   => $typeFilter,
        ];

        $this->includeComponentTemplate();
    }
}

User Interface and Usability

Display and Pagination

Key point with pagination: for the D7 component, we don't use CDBResult::NavStart; we calculate the number of pages ourselves:

$totalPages = (int)ceil($arResult['TOTAL_COUNT'] / $arResult['PAGE_SIZE']);

Page links are generated while preserving the current filter. We also output type labels and signs:

$typeLabels = [
    'accrual' => 'Accrual',
    'debit'   => 'Debit',
    'reserve' => 'Reserve',
    'release' => 'Release of reserve',
    'expire'  => 'Expiration',
    'manual'  => 'Manual adjustment',
];

$amountSign = [
    'accrual' => '+',
    'debit'   => '−',
    'reserve' => '−',
    'release' => '+',
    'expire'  => '−',
    'manual'  => '',
];

Time Zone Conversion

Dates are stored in UTC. To display them in the user's time zone, we use:

$userTz = new \DateTimeZone(\CTimeZone::GetOffset() ? 'UTC' : date_default_timezone_get());
$dt = new \DateTime($transaction['CREATED_AT'], new \DateTimeZone('UTC'));
$dt->setTimezone($userTz);
echo $dt->format('d.m.Y H:i');

Or via \Bitrix\Main\Type\DateTime::createFromTimestamp()—it automatically respects site settings.

Linking to Orders

Transactions of type accrual and debit contain ORDER_ID. The link to the order is built using ACCOUNT_NUMBER, not ID—it is the public number:

/personal/order/detail/{ACCOUNT_NUMBER}/

If the order was deleted, we show only the number with a note "(order deleted)" and do not output a link.

Additional Features and Considerations

Cashback Expiration Mechanism

If business logic requires cashback to expire (e.g., after 12 months), the EXPIRES_AT field is displayed for accrual transactions. A cron job runs once a day and creates expire type transactions for expired cashback:

$expired = CashbackTransactionTable::getList([
    'filter' => [
        'TYPE'       => 'accrual',
        '<EXPIRES_AT' => new \Bitrix\Main\Type\DateTime(),
        'EXPIRED'    => false,
    ],
]);

This guarantees the balance is always up-to-date.

What Is Included in the Work?

• Design and creation of the transaction table with indexes
• Development of the component with page navigation and type filtering
• Time zone conversion configuration
• Integration with orders: displaying links and numbers
• Implementation of cashback expiration mechanism (optional)
• Load testing (up to 10,000 transactions per user, verified under 10ms queries)
• Full documentation (including architecture and setup instructions)
• Repository access with version control
• One training session for your team (1 hour via video call)
• Post-deployment support: 2 weeks bug fixes included

UX Recommendations for Transaction History

For user convenience, we recommend color coding: accruals in green (+), debits in red (−), pending in gray. On mobile devices, a compact list with date, amount, and operation type is optimal, with details hidden by default—details expand on click. This reduces cognitive load and increases trust in the loyalty program. Pagination—20 records per page, preserving the filter in the URL for shareability.

Timelines and Guarantees

Estimated timeline: from 1 to 3 weeks, depending on complexity (presence of expiration mechanism, data volume). We guarantee performance even under high load—99% of table queries execute in < 10 ms. Our team has 12+ years of Bitrix experience and a 98% client satisfaction rate. Contact us for a precise estimate for your project. Typical starting price: $1,500 for basic implementation.

80% of Bitrix sites slow down due to one table

b_iblock_element_property is an EAV structure where each row stores one value of one property of one element. A catalog of 50,000 products with 30 properties yields 1.5 million rows. The smart filter performs a JOIN of this table with b_iblock_element on five properties, and MySQL performs a full table scan for 3–5 seconds. Our experience shows that without intervention in this table, site acceleration is impossible. We take on projects where load time has dropped to 8–10 seconds and bring TTFB back to <200 ms within 1–2 weeks. Site speed optimization begins with an audit of slow queries and ends with a comprehensive turnkey infrastructure overhaul.

Contact us for an audit — we will identify bottlenecks within 2 hours and propose a concrete plan.

How to achieve TTFB below 200 ms?

Server optimization is the first step. Nginx configuration goes beyond simple gzip. Specifically:

• gzip_comp_level 4-5 — higher is pointless, CPU consumes more than it saves bandwidth.
• brotli on with brotli_static on for precompressed files.
• HTTP/2 with http2_max_concurrent_streams 128.
• fastcgi_cache for PHP responses — caching at Nginx level, bypassing PHP-FPM entirely.
• worker_processes auto, worker_connections according to the number of simultaneous connections.

PHP-FPM tuning: choose between pm = dynamic and pm = static. Static mode works best for dedicated servers with predictable load because it avoids forking overhead. Dynamic saves RAM under low traffic. Calculate pm.max_children as (available RAM - RAM for MySQL/Redis) / average process consumption. For OPcache set memory_consumption=256, max_accelerated_files=20000, and validate_timestamps=0 in production (restart PHP-FPM on deploy).

MySQL/MariaDB: the main bottleneck is almost always the database. Enable slow_query_log with a threshold of 0.5 sec and analyze every query via EXPLAIN. Set innodb_buffer_pool_size to 70–80% of available RAM on a dedicated server. Create composite indexes for faceted search: (IBLOCK_ID, IBLOCK_PROPERTY_ID, VALUE) on b_iblock_element_property. Run OPTIMIZE TABLE b_iblock_element_property after mass operations.

How to configure three-level caching?

Managed component cache. Set TTL individually for each component. Catalog — 3600 sec, news feed — 300 sec, banners — 86400. The same TTL everywhere guarantees either outdated data or useless cache.

Composite cache. The bitrix:composite technology lets Nginx serve ready HTML from a file; PHP is not executed. Dynamic zones (cart, authorization) are loaded via AJAX request through CBitrixComponent::setFrameMode(true). TTFB drops below 50 ms. However, not all components are compatible; $APPLICATION->ShowPanel() and direct output via echo break the composite. We check every page through the panel 'Performance → Composite Site'. According to Bitrix official documentation on composite cache, this is the most effective caching method for high‑load projects.

Comparison: composite cache is 10–20 times faster than managed cache in time to first byte.

Memcached / Redis. Transfer cache from the file system: sessions go to Redis (session.save_handler = redis) — 10–50 times faster than files, plus cluster support. Component cache goes to Memcached via .settings.php: 'cache' => ['type' => 'memcache']. Also enable ORM query cache so identical GetList() calls don't hit MySQL on every request.

What is the fastest way to optimize Bitrix database?

Default MySQL settings are insufficient. Indexes — composite for faceted search, covering for frequent queries. MySQL responds from the index without accessing the data. Partial indexes (MariaDB) for filtering by ACTIVE = 'Y'. Audit unused indexes — each slows down INSERT/UPDATE.

Partitioning. For tables with millions of rows: b_stat_session, b_search_content_stem, and highload-blocks with history. Partition by date — a query for 'orders in a month' does not scan three years of data. Partitioning also solves the problem of concurrent queries during exchange with 1С via CommerceML.

Real case: a catalog of 200,000 products, 50 properties. Filtering by 10 properties took 12 seconds. After creating composite indexes on (IBLOCK_ID, IBLOCK_PROPERTY_ID, VALUE) and partitioning b_iblock_element_property by IBLOCK_ID, execution time dropped to 0.3 seconds. MySQL load decreased by 40 times.

Cleanup. Over a year or two, any database accumulates: outdated search index, expired records in b_cache_tag, history in b_iblock_element_prop_s*, logs in b_event_log taking gigabytes. We set up regular cleanup via agents.

Frontend and CDN

Images account for 60–80% of page weight. Convert to WebP via CFile::ResizeImageGet() with BX_RESIZE_IMAGE_PROPORTIONAL + conversion. Use srcset + sizes — never load a 3000px image into a 400px block. Add loading="lazy" for everything below the fold. AVIF offers another 20–30% savings vs WebP.

CSS/JS optimization: use the built-in Bitrix module to merge and minify via 'Settings → CSS/JS Optimization'. Apply PurgeCSS / UnCSS — in a typical Bitrix project, 60–70% of CSS is unused. Use defer / async for non‑critical JS and inline critical CSS in <head> for instant FCP.

Fonts: add <link rel="preload" as="font" crossorigin> for the main font. Set font-display: swap — text visible immediately. Subset via pyftsubset — keep only Cyrillic + Latin, file size reduces by 3–5 times.

CDN: Cloudflare, BunnyCDN, AWS CloudFront, or Russian providers (Selectel CDN, VK Cloud CDN). Serve static assets (CSS, JS, images, fonts) via CDN with Cache-Control: public, max-age=31536000, immutable for files with a hash. Use on‑the‑fly image optimization (imgproxy, Cloudflare Polish) without load on origin.

Why is load testing necessary?

Not synthetic benchmarks, but real scenarios: k6 / wrk to simulate routes — catalog → filtering → product card → cart → checkout. Measure RPS, response time (p50, p95, p99), error rate. Use Xdebug (callgrind) or Blackfire for PHP profiling to find bottlenecks. The test result gives an objective picture of where it actually slows down, not where it 'seems'. After optimization, run again to record improvements.

Results

What is included in the work?

1. Current performance audit — analysis of slow queries, PHP profiling, check of caching, CDN, server settings.
2. Server configuration — Nginx, PHP-FPM, MySQL, Redis/Memcached, OPcache.
3. Caching optimization — managed cache, composite site, TTL configuration, tagged caching.
4. Database work — index creation, partitioning, cleanup, EAV table reorganization.
5. Frontend — images (WebP/AVIF), CSS/JS (minification, deferred), fonts (preload, subsetting).
6. CDN — connection, caching rule setup.
7. Load testing — real user scenarios, metric report.
8. Documentation — description of all changes, recommendations for further maintenance.
9. Guarantee — support for 1 month after delivery, ensuring all optimizations are stable.

Monitoring

Without monitoring, everything degrades in six months. A new module, uncleared logs, a template change — and speed returns to original. Use web-vitals API for Real User Monitoring from actual visitors. Set up synthetic monitoring with Pingdom or UptimeRobot for regular checks from different locations. Configure alerts — TTFB > 500 ms or LCP > 3 sec triggers notification.

Timelines and cost

Cost is calculated individually after the audit. Get a consultation for your project — we will evaluate the current state and propose an acceleration plan with specific timelines and budget. We are a team with 12+ years of experience in Bitrix, having completed over 300 site speed optimization projects. Contact us to start the performance audit today.