A comprehensive QGIS plugin for PostgreSQL database audit monitoring, versioning, and rollback operations. Provides real-time change tracking, data versioning, and recovery capabilities for spatial and non-spatial databases.

Real-Time Monitoring	Version Comparison	Rollback Operations
Live change tracking with smart filtering	Side-by-side version comparison	Single and batch rollback operations

• Live Change Tracking: Monitor INSERT, UPDATE, DELETE operations as they happen
• Smart Filtering: Advanced filters by user, schema, table, time period, and operation type
• Intelligent Search: Natural language search with syntax like user:john, table:buildings, today
• Visual Indicators: Color-coded operations and enhanced record ID detection
• Auto-Refresh: Configurable automatic data refresh intervals

• Complete History: Track all changes with automatic version numbering
• Field-by-Field Diff: Visual comparison between versions with highlighted changes
• Record-Specific View: Filter complete history by specific record IDs
• Change Summaries: Human-readable summaries of modifications
• Export Capabilities: Export version history to CSV with metadata

• Precision Rollback: Restore individual records to any previous version
• User Change Undo: Bulk undo all changes made by specific users within time windows
• Batch Operations: Mass rollback operations across multiple tables and schemas
• Dry Run Mode: Preview rollback operations before execution
• Rollback Audit: Complete logging of all recovery operations

• One-Click Setup: Automated audit system installation with comprehensive SQL scripts
• Schema Configuration: Granular control over which schemas and tables to monitor
• Connection Management: Secure database connection handling with encrypted credential storage
• Performance Monitoring: Real-time system metrics, user activity, and storage analytics
• Health Dashboard: Monitor system performance and audit log growth

• QGIS: Version 3.x or higher
• PostgreSQL: Version 9.5+ with JSON support
• Python: 3.6+ (included with QGIS)

pip install psycopg2-binary  # PostgreSQL adapter

• CREATE privileges for initial system setup
• TRIGGER privileges for audit installation
• INSERT/SELECT on audit tables (automatically configured)

• JSON/JSONB data types
• PL/pgSQL procedural language
• Trigger functions
• System catalogs access

1. Open QGIS
2. Go to Plugins → Manage and Install Plugins
3. Search for "Audit System Manager"
4. Click Install Plugin

1. Download the latest release from Releases
2. Extract to your QGIS plugins directory:
   • Windows: %APPDATA%\QGIS\QGIS3\profiles\default\python\plugins\
   • macOS: ~/Library/Application Support/QGIS/QGIS3/profiles/default/python/plugins/
   • Linux: ~/.local/share/QGIS/QGIS3/profiles/default/python/plugins/
3. Restart QGIS
4. Enable the plugin in Plugins → Manage and Install Plugins → Installed

git clone https://github.com/yourusername/audit-system-manager.git
cd audit-system-manager
# Copy to QGIS plugins directory
cp -r . ~/.local/share/QGIS/QGIS3/profiles/default/python/plugins/audit_system_manager/

1. Launch the plugin from the toolbar or Plugins menu
2. Click Change Connection in the Configuration tab
3. Enter your PostgreSQL connection details:

   Host: localhost (or your server IP)
   Port: 5432
   Database: your_database_name
   Username: your_username
   Password: your_password
   

4. Click Test Connection to verify
5. Save the configuration

1. Go to Configuration tab → Setup Audit System
2. Click Install Audit System (requires CREATE privileges)
3. The installer will create:
   • logging schema with audit tables
   • Trigger functions for change tracking
   • Versioning and rollback functions
   • Performance indexes
   • Security permissions

1. In the setup dialog, go to Configure Monitoring tab
2. Select schemas to monitor from the available list
3. Add individual tables if needed
4. Click Apply Configuration
5. Triggers will be automatically created on selected tables

1. Open the Monitoring tab
2. Use quick action buttons for common tasks:
   - 🔄 Refresh: Update data manually
   - 📅 Today: Filter to today's changes only
   - 👤 My Changes: Show only your modifications
   - ⚡ Last 50: Display most recent changes
3. Use Smart Search for advanced filtering:
   - "user:john table:buildings today"
   - "insert delete schema:gis"
4. Double-click Record IDs to copy to clipboard
5. Right-click rows for context menu options

1. Go to Versioning tab
2. Select schema and table
3. Optionally filter by specific Record ID
4. Click "Load History"
5. Select multiple versions to compare
6. Use "Details" button for complete change information
7. Export history to CSV for external analysis

Single Record Rollback:
1. Go to Rollback tab → Single Record
2. Enter schema, table, version number
3. Provide rollback reason
4. Execute rollback

User Change Undo:
1. Go to Rollback tab → User Changes  
2. Select user and time window
3. Click "Test (Dry Run)" first
4. Review changes to be undone
5. Execute if satisfied

logging/
├── t_history              # Main audit table
├── t_rollback_history     # Rollback operations log
├── notify_with_versioning() # Trigger function
├── rollback_record_to_version() # Recovery function
├── undo_user_changes()    # Bulk undo function
└── v_recent_changes       # Convenience view

• Automatic: Triggers created on monitored tables
• Comprehensive: Captures INSERT, UPDATE, DELETE operations
• Metadata: Records user, timestamp, IP, application name
• JSON Storage: Before/after states stored as JSON
• Versioning: Automatic version number assignment

AuditSystemManager/
├── AuditSystemManager.py    # Main plugin class
├── DatabaseConfigDialog     # Connection management  
├── AuditSetupDialog        # System installation
├── AuditDialog             # Main interface
├── HelpDialog              # Documentation
└── Utility Functions       # Helper methods

CREATE TABLE logging.t_history (
    id                 serial PRIMARY KEY,
    tstamp             timestamp DEFAULT now(),
    epoc               float,
    schemaname         text,
    tabname            text,
    operation          text,
    who                text DEFAULT current_user,
    new_val            json,    -- After state
    old_val            json,    -- Before state
    version_number     integer,
    is_current         boolean DEFAULT true,
    change_reason      text,
    user_ip            text,
    application_name   text
);

-- Optimized for common query patterns
CREATE INDEX idx_t_history_tstamp ON logging.t_history (tstamp);
CREATE INDEX idx_t_history_schema_table ON logging.t_history (schemaname, tabname);
CREATE INDEX idx_t_history_user ON logging.t_history (who);
CREATE INDEX idx_t_history_operation ON logging.t_history (operation);
CREATE INDEX idx_t_history_current ON logging.t_history (schemaname, tabname, is_current) 
WHERE is_current = true;

• SECURITY DEFINER: Functions run with elevated privileges
• Controlled Access: Users don't need direct table privileges
• Audit Trail: Complete record of all access and modifications
• IP Tracking: Source IP address logging for security

• Encrypted Storage: Connection credentials encrypted in QGIS settings
• Audit Integrity: Audit records cannot be modified by regular users
• Change Validation: Rollback operations require explicit confirmation
• Rollback Logging: All recovery operations are themselves audited

1. Create test tables with various data types
2. Perform INSERT, UPDATE, DELETE operations
3. Verify audit records are created correctly
4. Test rollback functionality
5. Validate user permissions and security

-- Test with high-volume changes
INSERT INTO test_table SELECT generate_series(1,10000), 'test_data';
UPDATE test_table SET data = 'modified' WHERE id < 5000;
DELETE FROM test_table WHERE id > 8000;

-- Verify audit performance
SELECT COUNT(*) FROM logging.t_history WHERE tabname = 'test_table';

We welcome contributions! Please follow these guidelines:

git clone https://github.com/yourusername/audit-system-manager.git
cd audit-system-manager
git checkout -b feature/your-feature-name

• Follow PEP 8 for Python code
• Use meaningful variable names
• Add docstrings to functions
• Include error handling
• Test changes thoroughly

1. Fork the repository
2. Create a feature branch
3. Make your changes
4. Add tests if applicable
5. Update documentation
6. Submit a pull request

Please use the Issue Tracker and include:

• QGIS version
• PostgreSQL version
• Operating system
• Steps to reproduce
• Expected vs actual behavior
• Error messages or logs

• User Manual - Comprehensive usage guide
• API Documentation - Function reference
• FAQ - Common questions and solutions
• Troubleshooting - Problem resolution
• Examples - Sample configurations and use cases

• Installation Guide
• Basic Configuration
• Advanced Features

Currently supported languages:

• English (default)
• Italian (native)

To add a new language:

1. Create translation files in i18n/ directory
2. Use Qt Linguist for translation
3. Submit a pull request

• Real-time notifications
• Advanced reporting dashboard
• REST API for external access
• Multi-database support
• Performance analytics
• Automated backup integration

• Web-based interface
• Machine learning anomaly detection
• Integration with other QGIS plugins
• Cloud database support
• Mobile companion app

This project is licensed under the GNU General Public License v2.0 - see the LICENSE file for details.

• Qt Framework: LGPL
• PostgreSQL: PostgreSQL License
• QGIS: GPL v2
• psycopg2: LGPL

• Federico Gianoli - Initial work - gianoli.federico@gmail.com

• QGIS Development Team for the excellent framework
• PostgreSQL Community for robust database features
• Rocco Pispico, for inspiring me
• Ulderico Sicilia, for the idea
• Open Source contributors who made this possible

• 📖 Check the Documentation
• 🐛 Report bugs in Issues
• 💬 Ask questions in Discussions
• 📧 Email: gianoli.federico@gmail.com

Professional support, customization, and training available upon request.