-ˋˏ Veralis ETL engine

Veralis ETL is a dynamic and resilient data integration engine. It is designed to bridge the gap between fragmented raw data and structured cloud environments. Veralis acts as a passive intelligence layer that extracts, sanitizes, and mirrors data into a PostgreSQL (Neon) environment without ever endangering the integrity of the user's source or destination database.

⭑.ᐟ TECH STACK

• Python: Core logic.

• Polars: High-performance data manipulation.

• SQLAlchemy: Secure SQL execution and ORM layer.

• Neon: Serverless PostgreSQL for cloud-native storage.

• Next.js and TypeScript: Infrastructure and security layer.

• Batch script: Windows automation for immediate execution.

⧽ SYSTEM ARCHITECTURE (ETL)

• EXTRACT (main.py): Odentifies user context, fetches dynamic mapping rules, and handles the ingestion of source files.

• TRANSFORM (engine.py): Executes massive data sanitization and generates MD5 integrity hashes to prevent data duplication.

• LOAD (database.py): Manages secure, restricted-privilege connections to the Neon PostgreSQL instance.

PROJECT HIGHLIGHTS ✧˖°.

• Metadata-driven mapping: The system avoids tipically rigid transformations by consulting a configuration table (etl_config).

• Resilient processing: Powered by Polars, the engine handles inconsistent formatting, malformed quotes, and varied encodings.

• Automated workflow: Includes a windows batch script (.bat) for a easy drag and drop execution.

SECURITY & DATA INTEGRITY ୭˚.

Veralis operates under a strict non-destructive policy. My priority is the safety of the ingestion pipeline and user data.

• SQL injection shield: Implements extreme sanitization at the application level. By using SQL bind parameters and rigorous string filtering, it ensures that malicious inputs are neutralized.

• Data immutability: The engine is incapable of deleting or modifying existing records of the databases the users allow the Veralis engine to interact.

• Principle of least privilege (PoLP): Optimized for environments where only INSERT and SELECT privileges are granted.

• Active defense: The backend actively neutralizes SQL injections, XSS, and formulas injections.

DATABASE SCHEMA ‧₊˚ ┊ SETUP

1. Consolidated billing table Primary warehouse for all processed and sanitized data.

CREATE TABLE consolidated_billing (
    customer_name TEXT,
    monetary_value NUMERIC(15, 2),
    reference_date DATE,
    category_type TEXT,
    additional_info JSONB,
    integrity_id TEXT PRIMARY KEY,
    user_id TEXT NOT NULL
);

2. ETL configuration table Stores dynamic mapping rules that allow the engine to identify custom headers.

CREATE TABLE etl_config (
    user_id TEXT NOT NULL,
    csv_column TEXT NOT NULL,
    db_column TEXT NOT NULL,
    PRIMARY KEY (user_id, csv_column)
);

• Verification: To verify if the system is working, you can insert a test mapping for client_john (an example):

INSERT INTO etl_config (user_id, csv_column, db_column) 
VALUES ('client_john', 'Total Billing', 'monetary_value');

MY LATEST COMMIT: Architecture and security overhaul ⚠︎

1. Core engine and database

• Internationalization: Full migration of internal logic, variables and documentation from portuguese to english (to avoid the famous "portuinglês").

• Sanitization: Added sanitize_column_names and validate_mandatory_columns.

• Refactoring: Updated tables with professional naming conventions and added JSONB for metadata storage.

2. Infrastructure (Next.js and TypeScript)

• Rate limiting: Integrated Upstash Redis (5 attempts per 15 minutes per IP).

DISCLAIMER: even if cibersecurity attackers usually uses VPN, this allows a passive ban -> the scanned IP will be limited, even if it changes, the logic endures.

• Data contracts: Robust Zod schema with refine filters for SQL injection protection.

• Secure actions: Added registerUserAction with IP logging and domain verification to prevent "ghostly users" or spam accounts.

FUTURE ROADMAP ⌯⌲ˎˊ˗

• Database to database migration: Expanding ingestion to SQLite (.db), SQL dumps (.sql), and NoSQL integrations.

• Direct connectivity: Support for direct integration with client servers (MySQL, SQL Server) to fetch data automatically.

• Cybersecurity audit: Forensic logs to track DoS/DDoS attempts and protecting against CSRF or ID tampering.

• Interactive UI: A responsive column mapper for users to manually select headers in the browser.

• Dynamic BI dashboard: A visual analytics layer to transform processed data into real-time charts and KPIs.

• Data archaeological site: A repository for users to access historical files and dashboards without local downloads.