SeckelAPI

What is this even

A hopefully somewhat secure, role based SQL API server built in Rust. Provides a API interface to MariaDB with goofy authentication methods, basic and kinda advanced table authorization, and logging capabilities.

kinda recycled from an older project and not cleaned up from swearwords or drunk coding sessions reminants at all! please do not take any insult from the code or its "quality" incase the automatic sanetization by an llm model failed mlol.

currently contains example config for beepzone inventory system but should be usable for more than just that

Purpose (for now):

To server as a basic API plus "firewall" between any BeepZone client and its actual database.

Goofy ah Features worth mentioning:

Three auth methods

Password Authentication: Normal username/password
PIN Authentication: Username+PIN based login with IP whitelisting
Token Authentication: Reusable token strings (for like RFID cards etc. etc.) with IP restrictions

Attempts at Security

Basic and advanced table permissions (kinda RBAC style): Control read and write access per table or if you're schizophrenic even as granular as table column specific.
IP Whitelisting: Restrict PIN and token authentication by IPee ranges as these auth meths are by their nature insecure but needed for my application. (can be saved as bcrypt hash or of you really want to in plaintext it technically can do both)
Input Validation: Protection against some of the most basic ass common SQL injections (atleast according to chat gpt i dont do opsec myself im probably worse than an llm model in that aspect)
Audit Logging: Comprehensive request, query, and error logging. You see what comes in from a client, what happens within the API, and what goes out from the API to the database.

Goofy Data-BASED helping Features

Generated Fields: Automatic generation of defined fields if they are sent empty in request (for like asset tags and stuff with dynamic generation template strings that are too complex fo me to let just the DB triggers handle it)
User and Transaction Context: User and Transaction ID context automatically on ALL operations with optional exclusions of fields your next upcoming dataleak <3
Read Only Tables: Enforce read only for tables where all stuff is handled by database triggers
Connection Pooling: Somewhat efficient database connection management

Das Archtiktur und so

┌─────────────────┐    ┌─────────────────┐    ┌─────────────────┐
│   Client App    │───▶│    SeckelAPI    │───▶│   MySQL/MariaDB │
│  (BeepZone UI)  │    │   (Port 8800)   │    │   (Port 3306)   │
└─────────────────┘    └─────────────────┘    └─────────────────┘
                              │
                              ▼
                       ┌─────────────────┐
                       │   Log Files     │
                       │  ./logs/*.log   │
                       └─────────────────┘

Internal Request Flow

Client Request
     │
     ▼
┌─────────────────────────────────────────┐
│ 1. Rate Limiting (per IP)               │ ──▶ {"success": false, "error": "Too Many Requests"}
│    - Auth: 60/min, 10/sec (configurable)│ 
│    - API: 120/min, 20/sec (configurable)│
└─────────────────────────────────────────┘
     │
     ▼
┌─────────────────────────────────────────┐
│ 2. Authentication                       │
│    - Extract Bearer token               │
│    - Validate session                   │ ──▶ {"success": false, "error": "Invalid session"}
│    - Set user context (@current_user_id)│
└─────────────────────────────────────────┘
     │
     ▼
┌─────────────────────────────────────────┐
│ 3. RBAC Permission Check                │
│    - Check basic_rules (table access)   │ ──▶ {"success": false, "error": "Insufficient permissions [request_id: xxx]"}
│    - Apply advanced_rules (column-level)│
└─────────────────────────────────────────┘
     │
     ▼
┌─────────────────────────────────────────┐
│ 4. Query Building & Validation          │
│    - Validate table/column names        │
│    - Filter writable columns            │ ──▶ {"success": false, "error": "Invalid table/column [request_id: xxx]"}
│    - Auto-generate fields (if needed)   │
│    - Apply LIMIT caps                   │
└─────────────────────────────────────────┘
     │
     ▼
┌─────────────────────────────────────────┐
│ 5. Database Execution                   │
│    - Execute via connection pool        │
│    - Triggers run (change log, etc.)    │ ──▶ {"success": false, "error": "Database query failed [request_id: xxx]"}
└─────────────────────────────────────────┘
     │
     ▼
┌─────────────────────────────────────────┐
│ 6. Audit Logging                        │
│    - Log request, query, result         │
│    - Mask sensitive fields              │
└─────────────────────────────────────────┘
     │
     ▼
  JSON Response

Database Requirements

Your database needs these tables for the API to work:

Required Tables

users - User accounts with authentication credentials, if you dont use pin or string logins (for kiosk accounts or rfid) they can be left out
Fields: id, username, password (bcrypt hash), pin_code, login_string (RFID), role_id, active
roles - Role definitions with power levels
Fields: id, name, power (1-100, where 100 = admin)

Your Application Tables

The API works with ANY tables you define. Common examples (for BeepZone as an Example): - assets, categories, zones, suppliers (asset management) - lending_history, borrowers (lending system) - physical_audit_logs, physical_audits (audit system) - Literally anything else - it's your database

Note: Use database triggers to populate audit fields (created_by, last_modified_by) using @current_user_id session (or even last change transactionid for tracing) variable that the API automatically sets.

API Features

Authentication Endpoints

POST /auth/login

// Password auth
{"method": "password", "username": "admin", "password": "pass123"}

// PIN auth (IP restricted)
{"method": "pin", "username": "user1", "pin": "1234"}

// Token auth (IP restricted)
{"method": "token", "login_string": "RFID_TOKEN_12345"}

Returns: {"success": true, "token": "session-token-here"}

Use token in all subsequent requests: Authorization: Bearer <token>

Query Endpoint

POST /query - Main data operations

SELECT - Read data

{
  "action": "select",
  "table": "assets",
  "columns": ["id", "name", "status"],
  "where": {"status": "Good"},
  "order_by": [{"column": "name", "direction": "ASC"}],
  "limit": 50
}

INSERT - Create records

{
  "action": "insert",
  "table": "assets",
  "data": {
    "name": "Laptop",
    "status": "Good",
    "category_id": 5
    // "asset_numeric_id" auto-generated if configured
  }
}

Returns: {"success": true, "data": 123} (new ID)

UPDATE - Modify records

{
  "action": "update",
  "table": "assets",
  "data": {"status": "In Repair"},
  "where": {"id": 123}
}

DELETE - Remove records

{
  "action": "delete",
  "table": "assets",
  "where": {"id": 123}
}

BATCH - Multiple operations in one transaction

{
  "action": "batch",
  "queries": [
    {"action": "insert", "table": "assets", "data": {...}},
    {"action": "update", "table": "assets", "data": {...}, "where": {...}}
  ],
  "rollback_on_error": true  // All or nothing
}

Advanced Query Features

JOINs - Query across tables

{
  "action": "select",
  "table": "assets",
  "columns": ["assets.*", "categories.name as category_name"],
  "joins": [
    {
      "type": "INNER",
      "table": "categories",
      "on": "assets.category_id = categories.id"
    }
  ]
}

Complex WHERE - Multiple conditions

{
  "where": {
    "status": {"operator": "IN", "value": ["Good", "Attention"]},
    "price": {"operator": ">=", "value": 100},
    "name": {"operator": "LIKE", "value": "%Laptop%"}
  }
}

Aggregations - GROUP BY and aggregate functions

{
  "action": "select",
  "table": "assets",
  "columns": ["category_id", "COUNT(*) as total"],
  "group_by": ["category_id"]
}

Security Features

Rate Limiting: 429 if you spam too hard
Column Filtering: Auto removes columns you can't write based on permissions
Query Limits: Max LIMIT capped per power level (prevents SELECT * disasters for big boy databases)
WHERE Limits: Max conditions per query (prevents complex attack queries)
Read-Only Tables: Some tables blocked from writes entirely
Session Timeouts: Auto-expire sessions based on power level
Audit Everything: All operations logged with user context

Health Check

GET /health - Check if the API and database are alive

{
  "status": "hurensohn modus aktiviert",
  "database": "connected"
}

Running It

Set up your MariaDB/MySQL database
Configure config/*.toml files
Build: cargo build --release
Run: ./target/release/SeckelAPI

Server starts on configured port (default 8800).

Check logs in logs/ folder to see what's happening.

Testing

Run the workflow test to verify everything works:

cd testing
./1-workflow.sh

This creates sample data and tests all features.