committing to insanityHEADmaster

1 files changed, 281 insertions, 0 deletions

diff --git a/README.md b/README.md
new file mode 100644
index 0000000..3aa184b
--- /dev/null
+++ b/README.md
@@ -0,0 +1,281 @@
+# 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**
+```json
+// 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
+```json
+{
+  "action": "select",
+  "table": "assets",
+  "columns": ["id", "name", "status"],
+  "where": {"status": "Good"},
+  "order_by": [{"column": "name", "direction": "ASC"}],
+  "limit": 50
+}
+```
+
+**INSERT** - Create records
+```json
+{
+  "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
+```json
+{
+  "action": "update",
+  "table": "assets",
+  "data": {"status": "In Repair"},
+  "where": {"id": 123}
+}
+```
+
+**DELETE** - Remove records
+```json
+{
+  "action": "delete",
+  "table": "assets",
+  "where": {"id": 123}
+}
+```
+
+**BATCH** - Multiple operations in one transaction
+```json
+{
+  "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
+```json
+{
+  "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
+```json
+{
+  "where": {
+    "status": {"operator": "IN", "value": ["Good", "Attention"]},
+    "price": {"operator": ">=", "value": 100},
+    "name": {"operator": "LIKE", "value": "%Laptop%"}
+  }
+}
+```
+
+**Aggregations** - GROUP BY and aggregate functions
+```json
+{
+  "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
+```json
+{
+  "status": "hurensohn modus aktiviert",
+  "database": "connected"
+}
+```
+
+## Running It
+
+1. Set up your MariaDB/MySQL database
+2. Configure `config/*.toml` files
+3. Build: `cargo build --release`
+4. 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:
+```bash
+cd testing
+./1-workflow.sh
+```
+
+This creates sample data and tests all features.