openclaw - 💡(How to fix) Fix [Proposal] SovereignLedger: Unified Accounting Ecosystem [1 participants]

CREATE TABLE entries (
    id INTEGER PRIMARY KEY,
    debit_account TEXT NOT NULL,
    credit_account TEXT NOT NULL,
    amount REAL NOT NULL,
    currency TEXT NOT NULL,
    date DATE NOT NULL,
    reconciled BOOLEAN DEFAULT FALSE
);

CREATE INDEX idx_date ON entries (date);
CREATE INDEX idx_account ON entries (debit_account, credit_account);

from fastapi import FastAPI, HTTPException
from fastapi.responses import JSONResponse
import duckdb

app = FastAPI()

# Connect to the DuckDB database
con = duckdb.connect('ledger.db')

# Define the CRUD API endpoints
@app.post("/entries/")
def create_entry(entry: dict):
    con.execute("INSERT INTO entries VALUES (?, ?, ?, ?, ?, ?, ?)",
                (entry['id'], entry['debit_account'], entry['credit_account'], entry['amount'], entry['currency'], entry['date'], entry['reconciled']))
    return JSONResponse(content={"message": "Entry created successfully"}, status_code=201)

@app.get("/entries/")
def read_entries():
    results = con.execute("SELECT * FROM entries").fetchall()
    return JSONResponse(content=[dict(row) for row in results], status_code=200)

@app.put("/entries/{entry_id}")
def update_entry(entry_id: int, entry: dict):
    con.execute("UPDATE entries SET debit_account = ?, credit_account = ?, amount = ?, currency = ?, date = ?, reconciled = ? WHERE id = ?",
                (entry['debit_account'], entry['credit_account'], entry['amount'], entry['currency'], entry['date'], entry['reconciled'], entry_id))
    return JSONResponse(content={"message": "Entry updated successfully"}, status_code=200)

@app.delete("/entries/{entry_id}")
def delete_entry(entry_id: int):
    con.execute("DELETE FROM entries WHERE id = ?", (entry_id,))
    return JSONResponse(content={"message": "Entry deleted successfully"}, status_code=200)

curl -X POST -H "Content-Type: application/json" -d '{"id": 1, "debit_account": "Account 1", "credit_account": "Account 2", "amount": 100.0, "currency": "USD", "date": "2022-01-01", "reconciled": false}' http://localhost:8000/