Auditing is a critical component in platforms like OLX India where large volumes of user-generated content and transactions occur daily. It ensures transparency and accountability by recording key activities related to users and ads like account creation, account updates, ad postings, edits, and moderation actions. The audit trail system helps detect and prevent fraud, support compliance with data regulations, and aid in troubleshooting technical or user-related issues eventually enhancing platform integrity, fosters user trust, and contributes to a safer and more reliable marketplace experience.
An Audit record is a detailed log entry that captures information about a specific event or action within a system. It typically includes what happened, who performed the action, when it occurred, and sometimes where (e.g., IP address or device).
At OLX India, examples of audit records include actions like when a user posts an ad, updates their profile, or gets flagged for suspicious activity. These records are essential for tracking user behaviour, investigating issues, detecting fraud, and maintaining overall transparency and accountability within the system.
Why Auditing Matters
Auditing is essential for us at OLX India to maintain trust, safety, and transparency in a dynamic user-driven environment. With thousands of users posting, editing, and interacting with ads daily, auditing ensures that every critical action such as Ad creation, User Verification, Content Moderation, or Account Changes are tracked and recorded. This helps detect fraud, prevent abuse, resolve disputes, and comply with legal regulations.
Challenges with Third Party Auditing Services
While third-party audit trail services offer convenience, scalability, and advanced features, they also come with several challenges like
Expensive License cost
Limited Data Ingestion
Data Security & Privacy Risk
Regulatory Compliance Issues
Data Portability Challenges
Integration complexities
Lack of Transparency
Massive Data Volume
High Operational Cost
At OLX India, we serve over 3M+ daily active users (DAUs) and 30M+ monthly active users (MAUs) and thus produce huge volumes of audit records.
Given this scale, relying on third-party solutions can quickly become limiting and hence the idea of developing an in-house audit system came in which allows complete control over how audit records are captured, stored, and analysed and also can be optimised for OLX India’s specific workflows, offering greater flexibility, performance and cost-efficiency in the long run.
In-House Audit Trail System
Considering the challenges associated with third party solutions, we started exploring an alternate way that could better meet our needs. The goal was to explore a solution that offers greater flexibility, efficiency and cost-effective audit trail system at OLX India.
Game Changer : ClickHouse, Vector & Grafana
Using ClickHouse, Vector, and Grafana to build an in-house audit trail system offers a fast, flexible and cost-effective solution for handling massive volumes of user audit records.
Audit Trail System Pipeline
Let’s dive deep into the intricacies of building and maintaining an in-house audit trail system. In this section, we will explore the key components essential for setting up a robust, secure, and efficient system. From understanding its architecture to discussing the benefits, challenges, and best practices, we’ll try to cover the components required to design and implement a tailored audit trail solution.
The following sections will break down the critical components that form the backbone of a successful in-house audit trail system.
Audit Library :- Intercepts database operation on entities and log audit records to a file.
Vector :- Aggregate and transform audit record file generated by applications.
ClickHouse :- To store transformed audit records in structured format.
Grafana :- User Interface for query and visualisation of audit records.
High Level System Design
Audit Library
At OLX India, we built a light weight in-house audit library to intercept database operations and log the audit records to a file configured to be rotated based on size. This in-house audit library gives us fine-grained control over audit record without binding to any specific ORM or logging vendor.
Role In Audit System
Intercept low-level SQL operations to capture statements before they are executed on the database.
Filter relevant database operations like INSERT, UPDATE and DELETE.
Create structured audit record in JSON format records by capturing relevant data from the context.
Work agnostically, regardless of the ORM or query method.
Extensible to add custom audit records in application.
Audit Statement
Vector
Vector is an ultra-fast log shipping agent that has a low resource overhead. Designed to collect logs records from various sources, Vector can perform numerous transformations on the collected data and send them to multiple sinks.
Role In Audit System
Read audit records file generated by the applications.
Aggregate audit data read from multiple services.
Transform aggregated audit data to a compatible structured schema.
Send transformed data to configured clickhouse database table.
Sample Aggregator Configuration
Sample Sink Configuration
ClickHouse
ClickHouse is an open-source and cost effective column-oriented database management system (DBMS) designed for online analytical processing (OLAP). It enables users to generate real-time analytical reports using SQL queries. Moreover if also offers the following advantages
Blazing-Fast Analytical Queries.
Efficient Storage with Compression.
Real-Time Ingestion at Scale.
Powerful Filtering & Aggregation.
Flexible Schema Design.
Integration with Dashboards and BI Tools.
Cost-Effective for In-House Deployments.
Role In Audit System
Persistence of audit records in to table for querying, monitoring and analysis.
Why ClickHouse
ClickHouse, a column-oriented DBMS, excels at handling OLAP workloads, which aligns closely with the characteristics of logging workloads. Both workloads are read-heavy, involve large batches of inserts with infrequent updates, and primarily deal with immutable data. These similarities make ClickHouse particularly well-suited for efficiently managing and querying large volumes of log data.
ClickHouse Compression Codecs
ClickHouse provides excellent compression codecs at the block level, including LZ4, Brotli, and ZSTD. These codecs effectively reduce the amount of data that needs to be read from disk, thereby enhancing performance.
ClickHouse utilizes dictionary compression, which is particularly useful for columns with low cardinality. This method replaces repeated values with smaller integers, known as dictionary indices, further optimising data storage and retrieval.
Scalability with ClickHouse
Clickhouse is relatively easy to scale and comes bundled with clickhouse-keeper, which makes it possible to set up a distributed Clickhouse cluster with multiple nodes replicating data with each other.
Clickhouse-keeper is a drop-in replacement for ZooKeeper written in C++, with a fully compatible client protocol and the same data model, and multiple improvements over traditional zookeeper.
Sample Table Schema
Grafana
Grafana acts as the query and user interface layer, providing powerful visualization and query capabilities to analyze and interpret the stored logs. Together, these components create a robust and scalable logging solution, from log collection through to visualization and analysis.
Role in Audit System
Grafana is used as an interface to view and query audit logs records over the data stored in clickhouse database.
Dashboard for real time monitoring of the log ingestion for specific entities.
Why Grafana
Rich Visualisations.
Searchable Log Exploration.
Real-Time Monitoring.
Alerting and Notifications.
Multi-Source Integration.
Self-Hosted, Open-Source, and Extensible.
Sample Visualisation
Conclusion
Combining Vector, ClickHouse and Grafana capabilities, we at OLX India build a robust and scalable and cost effective audit trail system without relying on expensive or opaque third-party platforms.
This stack can be an ideal solution if you are aspiring for
Full control over your audit data.
Need to retain logs long-term and query them efficiently.
Want real-time observability of user actions and system events.
Cost effective and open-source, vendor-neutral solution.
Thank You
We want to extend our deepest gratitude to the incredible team whose hard work and expertise made this project a reality. Your dedication, insight, and teamwork have been nothing short of inspiring. It’s a true privilege to work alongside such a talented group of individuals.
Team
Developer – Bhupesh Kumar
DevOps : Ravi Maurya
Technical Architect : Parvez Hassan
Engineering Manager : Hitesh Das
To our readers — thank you for taking the time to explore this post. Your curiosity and engagement drive us to continuously push the boundaries of what’s possible. We hope the insights shared here spark new ideas, solutions, and innovations in your own work. We look forward to continuing this journey with you all. Stay curious, stay innovative! 🚀
