Hermes Agent
1435 lines
54 KiB
Python
1435 lines
54 KiB
Python
"""
|
|
GreySec Security — Flask REST API
|
|
Replaces PostgREST with business logic, auth, and relationship traversal.
|
|
Run: python3 greysec_api.py
|
|
"""
|
|
|
|
import os
|
|
import json
|
|
import psycopg2
|
|
from flask import Flask, request, jsonify, g
|
|
from flask_cors import CORS
|
|
|
|
app = Flask(__name__)
|
|
|
|
# ── Database ──────────────────────────────────────────────────────────────────
|
|
|
|
def get_db():
|
|
if 'db' not in g:
|
|
g.db = psycopg2.connect(
|
|
host=os.environ.get('DB_HOST', 'localhost'),
|
|
port=int(os.environ.get('DB_PORT', 5432)),
|
|
database=os.environ.get('DB_NAME', 'greysecthreat'),
|
|
user=os.environ.get('DB_USER', 'greysec'),
|
|
password=os.environ.get('DB_PASS', 'GreySecDBPass2026!')
|
|
)
|
|
return g.db
|
|
|
|
@app.teardown_appcontext
|
|
def close_db(e=None):
|
|
db = g.pop('db', None)
|
|
if db is not None:
|
|
db.close()
|
|
|
|
# ── Auth ───────────────────────────────────────────────────────────────────────
|
|
|
|
API_KEY = os.environ.get('GREYSEC_API_KEY', 'greysec-dev-key-2026')
|
|
# Accept both legacy key and new key for backwards compat
|
|
VALID_KEYS = {API_KEY, 'dev-key-change-in-prod'}
|
|
|
|
def require_auth(f):
|
|
from functools import wraps
|
|
@wraps(f)
|
|
def authed(*args, **kwargs):
|
|
key = request.headers.get('Authorization', '').replace('Bearer ', '')
|
|
if key not in VALID_KEYS:
|
|
return jsonify({'error': 'unauthorized'}), 401
|
|
return f(*args, **kwargs)
|
|
return authed
|
|
|
|
# ── Routes ────────────────────────────────────────────────────────────────────
|
|
|
|
@app.route('/health')
|
|
def health():
|
|
return jsonify({'status': 'ok', 'service': 'greysec-api'})
|
|
|
|
# ── Companies ────────────────────────────────────────────────────────────────
|
|
|
|
@app.route('/rest/v1/companies', methods=['GET'])
|
|
@app.route('/rest/v1/companies/<uuid:company_id>', methods=['GET'])
|
|
@require_auth
|
|
def list_companies(company_id=None):
|
|
db = get_db()
|
|
cur = db.cursor()
|
|
if company_id:
|
|
cur.execute('SELECT * FROM companies WHERE id = %s', (str(company_id),))
|
|
row = cur.fetchone()
|
|
if not row:
|
|
return jsonify({'error': 'not found'}), 404
|
|
cols = [d[0] for d in cur.description]
|
|
return jsonify(dict(zip(cols, row)))
|
|
# Filtering
|
|
status = request.args.get('status')
|
|
tier = request.args.get('tier')
|
|
limit = min(int(request.args.get('limit', 50)), 200)
|
|
offset = int(request.args.get('offset', 0))
|
|
query = 'SELECT * FROM companies WHERE 1=1'
|
|
params = []
|
|
if status:
|
|
query += ' AND status = %s'
|
|
params.append(status)
|
|
if tier:
|
|
query += ' AND tier = %s'
|
|
params.append(tier)
|
|
query += f' ORDER BY created_at DESC LIMIT {limit} OFFSET {offset}'
|
|
cur.execute(query, params)
|
|
cols = [d[0] for d in cur.description]
|
|
rows = [dict(zip(cols, r)) for r in cur.fetchall()]
|
|
return jsonify(rows)
|
|
|
|
@app.route('/rest/v1/companies', methods=['POST'])
|
|
@require_auth
|
|
def create_company():
|
|
db = get_db()
|
|
data = request.json
|
|
cur = db.cursor()
|
|
cols = ['name','domain','industry','employee_count','annual_revenue','sources','tier','status','notes']
|
|
vals = [data.get(c) for c in cols]
|
|
placeholders = ','.join(['%s'] * len(cols))
|
|
cur.execute(
|
|
f"INSERT INTO companies ({','.join(cols)}) VALUES ({placeholders}) RETURNING *",
|
|
vals
|
|
)
|
|
row = cur.fetchone()
|
|
db.commit()
|
|
result_cols = [d[0] for d in cur.description]
|
|
return jsonify(dict(zip(result_cols, row))), 201
|
|
|
|
@app.route('/rest/v1/companies/<uuid:company_id>', methods=['PATCH'])
|
|
@require_auth
|
|
def update_company(company_id):
|
|
db = get_db()
|
|
data = request.json
|
|
cur = db.cursor()
|
|
set_clauses = [f"{k} = %s" for k in data.keys()]
|
|
cur.execute(
|
|
f"UPDATE companies SET {','.join(set_clauses)} WHERE id = %s RETURNING *",
|
|
list(data.values()) + [str(company_id)]
|
|
)
|
|
row = cur.fetchone()
|
|
if not row:
|
|
return jsonify({'error': 'not found'}), 404
|
|
db.commit()
|
|
result_cols = [d[0] for d in cur.description]
|
|
return jsonify(dict(zip(result_cols, row)))
|
|
|
|
@app.route('/rest/v1/companies/<uuid:company_id>', methods=['DELETE'])
|
|
@require_auth
|
|
def delete_company(company_id):
|
|
db = get_db()
|
|
cur = db.cursor()
|
|
cur.execute('DELETE FROM companies WHERE id = %s RETURNING id', (str(company_id),))
|
|
if not cur.fetchone():
|
|
return jsonify({'error': 'not found'}), 404
|
|
db.commit()
|
|
return '', 204
|
|
|
|
# ── Contacts ──────────────────────────────────────────────────────────────────
|
|
|
|
@app.route('/rest/v1/contacts', methods=['GET'])
|
|
@app.route('/rest/v1/companies/<uuid:company_id>/contacts', methods=['GET'])
|
|
@require_auth
|
|
def list_contacts(company_id=None):
|
|
db = get_db()
|
|
cur = db.cursor()
|
|
if company_id:
|
|
cur.execute('SELECT * FROM contacts WHERE company_id = %s ORDER BY created_at', (str(company_id),))
|
|
else:
|
|
cur.execute('SELECT * FROM contacts ORDER BY created_at DESC LIMIT 50')
|
|
cols = [d[0] for d in cur.description]
|
|
return jsonify([dict(zip(cols, r)) for r in cur.fetchall()])
|
|
|
|
@app.route('/rest/v1/contacts', methods=['POST'])
|
|
@require_auth
|
|
def create_contact():
|
|
db = get_db()
|
|
data = request.json
|
|
cur = db.cursor()
|
|
cols = ['company_id','name','title','email','linkedin_url','phone','is_decision_maker']
|
|
vals = [data.get(c) for c in cols]
|
|
placeholders = ','.join(['%s'] * len(cols))
|
|
cur.execute(
|
|
f"INSERT INTO contacts ({','.join(cols)}) VALUES ({placeholders}) RETURNING *",
|
|
vals
|
|
)
|
|
row = cur.fetchone()
|
|
db.commit()
|
|
result_cols = [d[0] for d in cur.description]
|
|
return jsonify(dict(zip(result_cols, row))), 201
|
|
|
|
# ── Engagements ───────────────────────────────────────────────────────────────
|
|
|
|
@app.route('/rest/v1/engagements', methods=['GET'])
|
|
@app.route('/rest/v1/companies/<uuid:company_id>/engagements', methods=['GET'])
|
|
@require_auth
|
|
def list_engagements(company_id=None):
|
|
db = get_db()
|
|
cur = db.cursor()
|
|
if company_id:
|
|
cur.execute('SELECT * FROM engagements WHERE company_id = %s ORDER BY created_at', (str(company_id),))
|
|
else:
|
|
cur.execute('SELECT * FROM engagements ORDER BY created_at DESC LIMIT 50')
|
|
cols = [d[0] for d in cur.description]
|
|
return jsonify([dict(zip(cols, r)) for r in cur.fetchall()])
|
|
|
|
@app.route('/rest/v1/engagements', methods=['POST'])
|
|
@require_auth
|
|
def create_engagement():
|
|
db = get_db()
|
|
data = request.json
|
|
cur = db.cursor()
|
|
cols = ['company_id','type','scope','status','start_date','end_date','price_cents','notes']
|
|
vals = [data.get(c) for c in cols]
|
|
placeholders = ','.join(['%s'] * len(cols))
|
|
cur.execute(
|
|
f"INSERT INTO engagements ({','.join(cols)}) VALUES ({placeholders}) RETURNING *",
|
|
vals
|
|
)
|
|
row = cur.fetchone()
|
|
db.commit()
|
|
result_cols = [d[0] for d in cur.description]
|
|
return jsonify(dict(zip(result_cols, row))), 201
|
|
|
|
@app.route('/rest/v1/engagements/<uuid:engagement_id>', methods=['PATCH'])
|
|
@require_auth
|
|
def update_engagement(engagement_id):
|
|
db = get_db()
|
|
data = request.json
|
|
cur = db.cursor()
|
|
set_clauses = [f"{k} = %s" for k in data.keys()]
|
|
cur.execute(
|
|
f"UPDATE engagements SET {','.join(set_clauses)} WHERE id = %s RETURNING *",
|
|
list(data.values()) + [str(engagement_id)]
|
|
)
|
|
row = cur.fetchone()
|
|
if not row:
|
|
return jsonify({'error': 'not found'}), 404
|
|
db.commit()
|
|
result_cols = [d[0] for d in cur.description]
|
|
return jsonify(dict(zip(result_cols, row)))
|
|
|
|
# ── OSINT Scans & Findings ────────────────────────────────────────────────────
|
|
|
|
@app.route('/rest/v1/osint_scans', methods=['GET'])
|
|
@app.route('/rest/v1/companies/<uuid:company_id>/osint_scans', methods=['GET'])
|
|
@require_auth
|
|
def list_osint_scans(company_id=None):
|
|
db = get_db()
|
|
cur = db.cursor()
|
|
limit = min(int(request.args.get('limit', 50)), 200)
|
|
if company_id:
|
|
cur.execute(
|
|
'SELECT * FROM osint_scans WHERE company_id = %s ORDER BY scan_date DESC LIMIT %s',
|
|
(str(company_id), limit)
|
|
)
|
|
else:
|
|
cur.execute(f'SELECT * FROM osint_scans ORDER BY scan_date DESC LIMIT {limit}')
|
|
cols = [d[0] for d in cur.description]
|
|
return jsonify([dict(zip(cols, r)) for r in cur.fetchall()])
|
|
|
|
@app.route('/rest/v1/osint_scans', methods=['POST'])
|
|
@require_auth
|
|
def create_osint_scan():
|
|
db = get_db()
|
|
data = request.json
|
|
cur = db.cursor()
|
|
cols = ['company_id','scan_type','scan_date','total_findings','exposed_service_count',
|
|
'subdomain_count','email_count','github_repo_count','s3_bucket_count',
|
|
'spiderfoot_module','raw_json_path','summary_md_path','gbrain_page_slug']
|
|
vals = [data.get(c) for c in cols]
|
|
placeholders = ','.join(['%s'] * len(cols))
|
|
cur.execute(
|
|
f"INSERT INTO osint_scans ({','.join(cols)}) VALUES ({placeholders}) RETURNING *",
|
|
vals
|
|
)
|
|
row = cur.fetchone()
|
|
db.commit()
|
|
result_cols = [d[0] for d in cur.description]
|
|
return jsonify(dict(zip(result_cols, row))), 201
|
|
|
|
@app.route('/rest/v1/osint_findings', methods=['GET'])
|
|
@app.route('/rest/v1/companies/<uuid:company_id>/osint_findings', methods=['GET'])
|
|
@require_auth
|
|
def list_osint_findings(company_id=None):
|
|
db = get_db()
|
|
cur = db.cursor()
|
|
limit = min(int(request.args.get('limit', 200)), 1000)
|
|
if company_id:
|
|
cur.execute(
|
|
'SELECT * FROM osint_findings WHERE company_id = %s ORDER BY created_at DESC LIMIT %s',
|
|
(str(company_id), limit)
|
|
)
|
|
else:
|
|
cur.execute(f'SELECT * FROM osint_findings ORDER BY created_at DESC LIMIT {limit}')
|
|
cols = [d[0] for d in cur.description]
|
|
return jsonify([dict(zip(cols, r)) for r in cur.fetchall()])
|
|
|
|
@app.route('/rest/v1/osint_findings', methods=['POST'])
|
|
@require_auth
|
|
def create_osint_finding():
|
|
db = get_db()
|
|
data = request.json
|
|
cur = db.cursor()
|
|
cols = ['scan_id','company_id','finding_type','value','source_module','risk_score','tags']
|
|
vals = [data.get(c) for c in cols]
|
|
placeholders = ','.join(['%s'] * len(cols))
|
|
cur.execute(
|
|
f"INSERT INTO osint_findings ({','.join(cols)}) VALUES ({placeholders}) RETURNING *",
|
|
vals
|
|
)
|
|
row = cur.fetchone()
|
|
db.commit()
|
|
result_cols = [d[0] for d in cur.description]
|
|
return jsonify(dict(zip(result_cols, row))), 201
|
|
|
|
# ── Prowler Findings ──────────────────────────────────────────────────────────
|
|
|
|
@app.route('/rest/v1/prowler_findings', methods=['GET'])
|
|
@app.route('/rest/v1/companies/<uuid:company_id>/prowler_findings', methods=['GET'])
|
|
@require_auth
|
|
def list_prowler_findings(company_id=None):
|
|
db = get_db()
|
|
cur = db.cursor()
|
|
limit = min(int(request.args.get('limit', 200)), 1000)
|
|
severity = request.args.get('severity')
|
|
if company_id:
|
|
query = 'SELECT * FROM prowler_findings WHERE company_id = %s'
|
|
params = [str(company_id)]
|
|
if severity:
|
|
query += ' AND severity = %s'
|
|
params.append(severity)
|
|
query += f' ORDER BY created_at DESC LIMIT {limit}'
|
|
cur.execute(query, params)
|
|
else:
|
|
cur.execute(f'SELECT * FROM prowler_findings ORDER BY created_at DESC LIMIT {limit}')
|
|
cols = [d[0] for d in cur.description]
|
|
return jsonify([dict(zip(cols, r)) for r in cur.fetchall()])
|
|
|
|
@app.route('/rest/v1/prowler_findings', methods=['POST'])
|
|
@require_auth
|
|
def create_prowler_finding():
|
|
db = get_db()
|
|
data = request.json
|
|
cur = db.cursor()
|
|
cols = ['engagement_id','company_id','provider','region','finding_id','check_result',
|
|
'severity','title','description','compliance_framework','risk_score','raw_output_path']
|
|
vals = [data.get(c) for c in cols]
|
|
placeholders = ','.join(['%s'] * len(cols))
|
|
cur.execute(
|
|
f"INSERT INTO prowler_findings ({','.join(cols)}) VALUES ({placeholders}) RETURNING *",
|
|
vals
|
|
)
|
|
row = cur.fetchone()
|
|
db.commit()
|
|
result_cols = [d[0] for d in cur.description]
|
|
return jsonify(dict(zip(result_cols, row))), 201
|
|
|
|
# ── Pipeline ──────────────────────────────────────────────────────────────────
|
|
|
|
@app.route('/rest/v1/pipeline', methods=['GET'])
|
|
@app.route('/rest/v1/companies/<uuid:company_id>/pipeline', methods=['GET'])
|
|
@require_auth
|
|
def list_pipeline(company_id=None):
|
|
db = get_db()
|
|
cur = db.cursor()
|
|
if company_id:
|
|
cur.execute('SELECT * FROM pipeline WHERE company_id = %s ORDER BY created_at', (str(company_id),))
|
|
else:
|
|
cur.execute('SELECT * FROM pipeline ORDER BY created_at DESC')
|
|
cols = [d[0] for d in cur.description]
|
|
return jsonify([dict(zip(cols, r)) for r in cur.fetchall()])
|
|
|
|
@app.route('/rest/v1/pipeline', methods=['POST'])
|
|
@require_auth
|
|
def create_pipeline_stage():
|
|
db = get_db()
|
|
data = request.json
|
|
cur = db.cursor()
|
|
cols = ['company_id','engagement_id','stage','probability','expected_close_date','amount_cents','notes','updated_by']
|
|
vals = [data.get(c) for c in cols]
|
|
placeholders = ','.join(['%s'] * len(cols))
|
|
cur.execute(
|
|
f"INSERT INTO pipeline ({','.join(cols)}) VALUES ({placeholders}) RETURNING *",
|
|
vals
|
|
)
|
|
row = cur.fetchone()
|
|
db.commit()
|
|
result_cols = [d[0] for d in cur.description]
|
|
return jsonify(dict(zip(result_cols, row))), 201
|
|
|
|
# ── Phishing Campaigns & Events ────────────────────────────────────────────────
|
|
|
|
@app.route('/rest/v1/phishing_campaigns', methods=['GET'])
|
|
@require_auth
|
|
def list_phishing_campaigns():
|
|
db = get_db()
|
|
cur = db.cursor()
|
|
cur.execute('SELECT * FROM phishing_campaigns ORDER BY created_at DESC')
|
|
cols = [d[0] for d in cur.description]
|
|
return jsonify([dict(zip(cols, r)) for r in cur.fetchall()])
|
|
|
|
@app.route('/rest/v1/phishing_campaigns', methods=['POST'])
|
|
@require_auth
|
|
def create_phishing_campaign():
|
|
db = get_db()
|
|
data = request.json
|
|
cur = db.cursor()
|
|
cols = ['engagement_id','company_id','campaign_name','gophish_campaign_id','status',
|
|
'pretext_type','target_count','sent_count','start_date','end_date','notes']
|
|
vals = [data.get(c) for c in cols]
|
|
placeholders = ','.join(['%s'] * len(cols))
|
|
cur.execute(
|
|
f"INSERT INTO phishing_campaigns ({','.join(cols)}) VALUES ({placeholders}) RETURNING *",
|
|
vals
|
|
)
|
|
row = cur.fetchone()
|
|
db.commit()
|
|
result_cols = [d[0] for d in cur.description]
|
|
return jsonify(dict(zip(result_cols, row))), 201
|
|
|
|
@app.route('/rest/v1/phishing_events', methods=['POST'])
|
|
@require_auth
|
|
def create_phishing_event():
|
|
db = get_db()
|
|
data = request.json
|
|
cur = db.cursor()
|
|
cols = ['campaign_id','event_type','target_email','ip_address','user_agent','redirect_url','creds_payload']
|
|
vals = [data.get(c) for c in cols]
|
|
placeholders = ','.join(['%s'] * len(cols))
|
|
cur.execute(
|
|
f"INSERT INTO phishing_events ({','.join(cols)}) VALUES ({placeholders}) RETURNING *",
|
|
vals
|
|
)
|
|
row = cur.fetchone()
|
|
db.commit()
|
|
result_cols = [d[0] for d in cur.description]
|
|
return jsonify(dict(zip(result_cols, row))), 201
|
|
|
|
# ── CRM: Contact Interactions ─────────────────────────────────────────────────
|
|
|
|
@app.route('/rest/v1/contact_interactions', methods=['GET'])
|
|
@require_auth
|
|
def list_contact_interactions():
|
|
db = get_db()
|
|
cur = db.cursor()
|
|
limit = request.args.get('limit', 50, type=int)
|
|
offset = request.args.get('offset', 0, type=int)
|
|
company_id = request.args.get('company_id')
|
|
contact_id = request.args.get('contact_id')
|
|
interaction_type = request.args.get('type')
|
|
|
|
where = []
|
|
params = []
|
|
if company_id:
|
|
where.append("ci.company_id = %s")
|
|
params.append(company_id)
|
|
if contact_id:
|
|
where.append("ci.contact_id = %s")
|
|
params.append(contact_id)
|
|
if interaction_type:
|
|
where.append("ci.type = %s")
|
|
params.append(interaction_type)
|
|
|
|
where_clause = " AND ".join(where) if where else "1=1"
|
|
params.extend([limit, offset])
|
|
|
|
cur.execute(f"""
|
|
SELECT ci.*, c.name as contact_name, co.name as company_name
|
|
FROM contact_interactions ci
|
|
LEFT JOIN contacts c ON ci.contact_id = c.id
|
|
LEFT JOIN companies co ON ci.company_id = co.id
|
|
WHERE {where_clause}
|
|
ORDER BY ci.created_at DESC
|
|
LIMIT %s OFFSET %s
|
|
""", params)
|
|
cols = [d[0] for d in cur.description]
|
|
return jsonify([dict(zip(cols, r)) for r in cur.fetchall()])
|
|
|
|
|
|
@app.route('/rest/v1/contact_interactions', methods=['POST'])
|
|
@require_auth
|
|
def create_contact_interaction():
|
|
db = get_db()
|
|
data = request.json
|
|
cur = db.cursor()
|
|
cols = ['contact_id','company_id','type','direction','subject','summary','outcome','next_action','next_action_date','duration_minutes','created_by']
|
|
vals = [data.get(c) for c in cols]
|
|
placeholders = ','.join(['%s'] * len(cols))
|
|
cur.execute(f"INSERT INTO contact_interactions ({','.join(cols)}) VALUES ({placeholders}) RETURNING *", vals)
|
|
row = cur.fetchone()
|
|
db.commit()
|
|
result_cols = [d[0] for d in cur.description]
|
|
return jsonify(dict(zip(result_cols, row))), 201
|
|
|
|
|
|
# ── CRM: Follow-ups ────────────────────────────────────────────────────────────
|
|
|
|
@app.route('/rest/v1/follow_ups', methods=['GET'])
|
|
@require_auth
|
|
def list_follow_ups():
|
|
db = get_db()
|
|
cur = db.cursor()
|
|
status = request.args.get('status', 'pending')
|
|
assigned_to = request.args.get('assigned_to')
|
|
|
|
where = ["fu.status = %s"]
|
|
params = [status]
|
|
if assigned_to:
|
|
where.append("fu.assigned_to = %s")
|
|
params.append(assigned_to)
|
|
|
|
cur.execute(f"""
|
|
SELECT fu.*, c.name as contact_name, co.name as company_name
|
|
FROM follow_ups fu
|
|
LEFT JOIN contacts c ON fu.contact_id = c.id
|
|
LEFT JOIN companies co ON fu.company_id = co.id
|
|
WHERE {' AND '.join(where)}
|
|
ORDER BY fu.due_date ASC NULLS LAST, fu.priority DESC
|
|
""", params)
|
|
cols = [d[0] for d in cur.description]
|
|
return jsonify([dict(zip(cols, r)) for r in cur.fetchall()])
|
|
|
|
|
|
@app.route('/rest/v1/follow_ups', methods=['POST'])
|
|
@require_auth
|
|
def create_follow_up():
|
|
db = get_db()
|
|
data = request.json
|
|
cur = db.cursor()
|
|
cols = ['contact_id','company_id','engagement_id','assigned_to','priority','status','due_date','description','notes']
|
|
vals = [data.get(c) for c in cols]
|
|
placeholders = ','.join(['%s'] * len(cols))
|
|
cur.execute(f"INSERT INTO follow_ups ({','.join(cols)}) VALUES ({placeholders}) RETURNING *", vals)
|
|
row = cur.fetchone()
|
|
db.commit()
|
|
result_cols = [d[0] for d in cur.description]
|
|
return jsonify(dict(zip(result_cols, row))), 201
|
|
|
|
|
|
@app.route('/rest/v1/follow_ups/<uuid:follow_up_id>', methods=['PATCH'])
|
|
@require_auth
|
|
def update_follow_up(follow_up_id):
|
|
db = get_db()
|
|
data = request.json
|
|
cur = db.cursor()
|
|
fid = str(follow_up_id)
|
|
setters = [f"{k} = %s" for k in data.keys() if k != 'id']
|
|
vals = [data[k] for k in data.keys() if k != 'id']
|
|
vals.append(fid)
|
|
if data.get('status') == 'completed':
|
|
setters.append("completed_at = NOW()")
|
|
cur.execute(f"UPDATE follow_ups SET {','.join(setters)} WHERE id = %s RETURNING *", vals)
|
|
row = cur.fetchone()
|
|
db.commit()
|
|
if not row:
|
|
return jsonify({'error': 'not found'}), 404
|
|
result_cols = [d[0] for d in cur.description]
|
|
return jsonify(dict(zip(result_cols, row)))
|
|
|
|
|
|
# ── CRM: Org Units ─────────────────────────────────────────────────────────────
|
|
|
|
@app.route('/rest/v1/company_org_units', methods=['GET'])
|
|
@require_auth
|
|
def list_org_units():
|
|
db = get_db()
|
|
cur = db.cursor()
|
|
company_id = request.args.get('company_id')
|
|
if company_id:
|
|
cur.execute("SELECT * FROM company_org_units WHERE company_id = %s ORDER BY name", (company_id,))
|
|
else:
|
|
cur.execute("SELECT * FROM company_org_units ORDER BY company_id, name")
|
|
cols = [d[0] for d in cur.description]
|
|
return jsonify([dict(zip(cols, r)) for r in cur.fetchall()])
|
|
|
|
|
|
@app.route('/rest/v1/company_org_units', methods=['POST'])
|
|
@require_auth
|
|
def create_org_unit():
|
|
db = get_db()
|
|
data = request.json
|
|
cur = db.cursor()
|
|
cols = ['company_id','parent_id','name','head_contact_id','description']
|
|
vals = [data.get(c) for c in cols]
|
|
placeholders = ','.join(['%s'] * len(cols))
|
|
cur.execute(f"INSERT INTO company_org_units ({','.join(cols)}) VALUES ({placeholders}) RETURNING *", vals)
|
|
row = cur.fetchone()
|
|
db.commit()
|
|
result_cols = [d[0] for d in cur.description]
|
|
return jsonify(dict(zip(result_cols, row))), 201
|
|
|
|
|
|
# ── CRM: Contact Org Placements ────────────────────────────────────────────────
|
|
|
|
@app.route('/rest/v1/contact_org_placement', methods=['GET'])
|
|
@require_auth
|
|
def list_contact_org_placements():
|
|
db = get_db()
|
|
cur = db.cursor()
|
|
contact_id = request.args.get('contact_id')
|
|
org_unit_id = request.args.get('org_unit_id')
|
|
where = []
|
|
params = []
|
|
if contact_id:
|
|
where.append("cop.contact_id = %s")
|
|
params.append(contact_id)
|
|
if org_unit_id:
|
|
where.append("cop.org_unit_id = %s")
|
|
params.append(org_unit_id)
|
|
where_clause = " AND ".join(where) if where else "1=1"
|
|
cur.execute(f"""
|
|
SELECT cop.*, c.name as contact_name, ou.name as org_unit_name, co.name as company_name
|
|
FROM contact_org_placement cop
|
|
LEFT JOIN contacts c ON cop.contact_id = c.id
|
|
LEFT JOIN company_org_units ou ON cop.org_unit_id = ou.id
|
|
LEFT JOIN companies co ON ou.company_id = co.id
|
|
WHERE {where_clause}
|
|
ORDER BY cop.is_primary_role DESC, c.name
|
|
""", params if params else [])
|
|
cols = [d[0] for d in cur.description]
|
|
return jsonify([dict(zip(cols, r)) for r in cur.fetchall()])
|
|
|
|
|
|
@app.route('/rest/v1/contact_org_placement', methods=['POST'])
|
|
@require_auth
|
|
def create_contact_org_placement():
|
|
db = get_db()
|
|
data = request.json
|
|
cur = db.cursor()
|
|
cols = ['contact_id','org_unit_id','role','is_primary_role']
|
|
vals = [data.get(c) for c in cols]
|
|
placeholders = ','.join(['%s'] * len(cols))
|
|
cur.execute(f"INSERT INTO contact_org_placement ({','.join(cols)}) VALUES ({placeholders}) RETURNING *", vals)
|
|
row = cur.fetchone()
|
|
db.commit()
|
|
result_cols = [d[0] for d in cur.description]
|
|
return jsonify(dict(zip(result_cols, row))), 201
|
|
|
|
|
|
# ── Dashboard / Aggregate Views ────────────────────────────────────────────────
|
|
|
|
@app.route('/rest/v1/dashboard/pipeline-summary')
|
|
@require_auth
|
|
def pipeline_summary():
|
|
"""Sales pipeline aggregated by stage."""
|
|
db = get_db()
|
|
cur = db.cursor()
|
|
cur.execute("""
|
|
SELECT stage,
|
|
COUNT(*) as count,
|
|
SUM(amount_cents) as total_value_cents,
|
|
AVG(probability) as avg_probability
|
|
FROM pipeline p
|
|
JOIN companies c ON p.company_id = c.id
|
|
WHERE c.status IN ('prospect','active')
|
|
GROUP BY stage
|
|
ORDER BY
|
|
CASE stage
|
|
WHEN 'lead' THEN 1 WHEN 'qualified' THEN 2
|
|
WHEN 'proposal' THEN 3 WHEN 'negotiation' THEN 4
|
|
WHEN 'closed_won' THEN 5 WHEN 'closed_lost' THEN 6
|
|
END
|
|
""")
|
|
cols = [d[0] for d in cur.description]
|
|
return jsonify([dict(zip(cols, r)) for r in cur.fetchall()])
|
|
|
|
@app.route('/rest/v1/dashboard/company-intel/<uuid:company_id>')
|
|
@require_auth
|
|
def company_intel(company_id):
|
|
"""All intel about a company: OSINT findings + Prowler findings + contacts + engagements."""
|
|
db = get_db()
|
|
cur = db.cursor()
|
|
cid = str(company_id)
|
|
|
|
cur.execute('SELECT * FROM companies WHERE id = %s', (cid,))
|
|
company = dict(zip([d[0] for d in cur.description], cur.fetchone())) if cur.rowcount else None
|
|
if not company:
|
|
return jsonify({'error': 'not found'}), 404
|
|
|
|
cur.execute('SELECT * FROM contacts WHERE company_id = %s', (cid,))
|
|
contacts = [dict(zip([d[0] for d in cur.description], r)) for r in cur.fetchall()]
|
|
|
|
cur.execute('SELECT * FROM engagements WHERE company_id = %s', (cid,))
|
|
engagements = [dict(zip([d[0] for d in cur.description], r)) for r in cur.fetchall()]
|
|
|
|
cur.execute('SELECT * FROM osint_findings WHERE company_id = %s ORDER BY created_at DESC LIMIT 50', (cid,))
|
|
osint_findings = [dict(zip([d[0] for d in cur.description], r)) for r in cur.fetchall()]
|
|
|
|
cur.execute('SELECT * FROM prowler_findings WHERE company_id = %s ORDER BY created_at DESC LIMIT 50', (cid,))
|
|
prowler_findings = [dict(zip([d[0] for d in cur.description], r)) for r in cur.fetchall()]
|
|
|
|
cur.execute('SELECT * FROM ir_cases WHERE engagement_id IN (SELECT id FROM engagements WHERE company_id = %s) ORDER BY created_at DESC LIMIT 10', (cid,))
|
|
ir_cases = [dict(zip([d[0] for d in cur.description], r)) for r in cur.fetchall()]
|
|
|
|
return jsonify({
|
|
'company': company,
|
|
'contacts': contacts,
|
|
'engagements': engagements,
|
|
'osint_findings': osint_findings,
|
|
'prowler_findings': prowler_findings,
|
|
'ir_cases': ir_cases
|
|
})
|
|
|
|
# ── IR Cases ──────────────────────────────────────────────────────────────────
|
|
|
|
@app.route('/rest/v1/ir_cases', methods=['GET'])
|
|
@app.route('/rest/v1/companies/<uuid:company_id>/ir_cases', methods=['GET'])
|
|
@require_auth
|
|
def list_ir_cases(company_id=None):
|
|
db = get_db()
|
|
cur = db.cursor()
|
|
status = request.args.get('status')
|
|
limit = min(int(request.args.get('limit', 50)), 200)
|
|
offset = int(request.args.get('offset', 0))
|
|
if company_id:
|
|
cur.execute(
|
|
'SELECT ic.* FROM ir_cases ic JOIN engagements e ON e.ir_case_id = ic.id WHERE e.company_id = %s ORDER BY ic.created_at DESC LIMIT %s OFFSET %s',
|
|
(str(company_id), limit, offset)
|
|
)
|
|
elif status:
|
|
cur.execute('SELECT * FROM ir_cases WHERE status = %s ORDER BY created_at DESC LIMIT %s OFFSET %s',
|
|
(status, limit, offset))
|
|
else:
|
|
cur.execute(f'SELECT * FROM ir_cases ORDER BY created_at DESC LIMIT %s OFFSET %s', (limit, offset))
|
|
cols = [d[0] for d in cur.description]
|
|
return jsonify([dict(zip(cols, r)) for r in cur.fetchall()])
|
|
|
|
|
|
@app.route('/rest/v1/ir_cases', methods=['POST'])
|
|
@require_auth
|
|
def create_ir_case():
|
|
db = get_db()
|
|
data = request.json
|
|
cur = db.cursor()
|
|
cols = [
|
|
'engagement_id', 'case_id', 'client', 'detection_date', 'reported_date',
|
|
'opened_date', 'closed_date', 'case_type', 'severity', 'incident_type',
|
|
'status', 'containment_status', 'lead_engineer', 'client_poc',
|
|
'evidence_path', 'classification', 'retention_years', 'coc_manifest', 'notes'
|
|
]
|
|
vals = [data.get(c) for c in cols]
|
|
placeholders = ','.join(['%s'] * len(cols))
|
|
cur.execute(
|
|
f"INSERT INTO ir_cases ({','.join(cols)}) VALUES ({placeholders}) RETURNING *",
|
|
vals
|
|
)
|
|
row = cur.fetchone()
|
|
db.commit()
|
|
result_cols = [d[0] for d in cur.description]
|
|
return jsonify(dict(zip(result_cols, row))), 201
|
|
|
|
|
|
@app.route('/rest/v1/ir_cases/<uuid:ir_case_id>', methods=['GET'])
|
|
@require_auth
|
|
def get_ir_case(ir_case_id):
|
|
db = get_db()
|
|
cur = db.cursor()
|
|
cur.execute('SELECT * FROM ir_cases WHERE id = %s', (str(ir_case_id),))
|
|
row = cur.fetchone()
|
|
if not row:
|
|
return jsonify({'error': 'not found'}), 404
|
|
cols = [d[0] for d in cur.description]
|
|
return jsonify(dict(zip(cols, row)))
|
|
|
|
|
|
@app.route('/rest/v1/ir_cases/<uuid:ir_case_id>', methods=['PATCH'])
|
|
@require_auth
|
|
def update_ir_case(ir_case_id):
|
|
db = get_db()
|
|
data = request.json
|
|
cur = db.cursor()
|
|
# Always update updated_at
|
|
data['updated_at'] = 'NOW()'
|
|
set_clauses = [f"{k} = %s" for k in data.keys()]
|
|
cur.execute(
|
|
f"UPDATE ir_cases SET {','.join(set_clauses)} WHERE id = %s RETURNING *",
|
|
list(data.values()) + [str(ir_case_id)]
|
|
)
|
|
row = cur.fetchone()
|
|
if not row:
|
|
return jsonify({'error': 'not found'}), 404
|
|
db.commit()
|
|
result_cols = [d[0] for d in cur.description]
|
|
return jsonify(dict(zip(result_cols, row)))
|
|
|
|
|
|
@app.route('/rest/v1/ir_cases/<uuid:ir_case_id>', methods=['DELETE'])
|
|
@require_auth
|
|
def delete_ir_case(ir_case_id):
|
|
db = get_db()
|
|
cur = db.cursor()
|
|
cur.execute('DELETE FROM ir_cases WHERE id = %s RETURNING id', (str(ir_case_id),))
|
|
if not cur.fetchone():
|
|
return jsonify({'error': 'not found'}), 404
|
|
db.commit()
|
|
return '', 204
|
|
|
|
|
|
@app.route('/rest/v1/ir_cases/<uuid:ir_case_id>/evidence_summary', methods=['GET'])
|
|
@require_auth
|
|
def ir_case_evidence_summary(ir_case_id):
|
|
"""Return file counts and sizes for each evidence category in this case."""
|
|
import os
|
|
db = get_db()
|
|
cur = db.cursor()
|
|
cur.execute('SELECT evidence_path, case_id FROM ir_cases WHERE id = %s', (str(ir_case_id),))
|
|
row = cur.fetchone()
|
|
if not row:
|
|
return jsonify({'error': 'not found'}), 404
|
|
evidence_path, case_id = row
|
|
result = {'case_id': case_id, 'evidence_path': evidence_path, 'categories': {}}
|
|
if evidence_path and os.path.isdir(evidence_path):
|
|
for cat in ['aws', 'o365', 'windows', 'chain_of_custody']:
|
|
cat_path = os.path.join(evidence_path, cat)
|
|
if os.path.isdir(cat_path):
|
|
files = [f for f in os.listdir(cat_path) if os.path.isfile(os.path.join(cat_path, f))]
|
|
total_size = sum(os.path.getsize(os.path.join(cat_path, f)) for f in files)
|
|
result['categories'][cat] = {'file_count': len(files), 'total_bytes': total_size}
|
|
return jsonify(result)
|
|
|
|
|
|
@app.route('/rest/v1/dashboard/ir-summary', methods=['GET'])
|
|
@require_auth
|
|
def ir_dashboard_summary():
|
|
"""IR case stats for the dashboard."""
|
|
db = get_db()
|
|
cur = db.cursor()
|
|
cur.execute("""
|
|
SELECT
|
|
status,
|
|
COUNT(*) as count,
|
|
CASE severity
|
|
WHEN 'critical' THEN 1 WHEN 'high' THEN 2 WHEN 'medium' THEN 3 ELSE 4 END as sev_order
|
|
FROM ir_cases
|
|
GROUP BY status, sev_order
|
|
ORDER BY sev_order, status
|
|
""")
|
|
by_status = [{'status': r[0], 'count': r[1]} for r in cur.fetchall()]
|
|
cur.execute("SELECT COUNT(*) FROM ir_cases WHERE status NOT IN ('closed')")
|
|
active = cur.fetchone()[0]
|
|
cur.execute("SELECT COUNT(*) FROM ir_cases WHERE status = 'closed'")
|
|
closed = cur.fetchone()[0]
|
|
return jsonify({'by_status': by_status, 'active': active, 'closed': closed})
|
|
|
|
|
|
# ══════════════════════════════════════════════════════════════════════════════
|
|
# PRODUCTS
|
|
# ══════════════════════════════════════════════════════════════════════════════
|
|
|
|
@app.route('/rest/v1/products', methods=['GET'])
|
|
@require_auth
|
|
def list_products():
|
|
db = get_db()
|
|
cur = db.cursor()
|
|
filters = []
|
|
params = []
|
|
for col in ('slug', 'type', 'billable', 'active'):
|
|
val = request.args.get(col)
|
|
if val is not None:
|
|
filters.append(f"{col} = %s")
|
|
params.append(val)
|
|
where = ("WHERE " + " AND ".join(filters)) if filters else ""
|
|
limit = min(int(request.args.get('limit', 100)), 500)
|
|
offset = int(request.args.get('offset', 0))
|
|
cur.execute(f"SELECT * FROM products {where} ORDER BY created_at DESC LIMIT %s OFFSET %s", params + [limit, offset])
|
|
cols = [d[0] for d in cur.description]
|
|
return jsonify([dict(zip(cols, r)) for r in cur.fetchall()])
|
|
|
|
|
|
@app.route('/rest/v1/products/<uuid:product_id>', methods=['GET'])
|
|
@require_auth
|
|
def get_product(product_id):
|
|
db = get_db()
|
|
cur = db.cursor()
|
|
cur.execute("SELECT * FROM products WHERE id = %s", (str(product_id),))
|
|
row = cur.fetchone()
|
|
if not row:
|
|
return jsonify({'error': 'not found'}), 404
|
|
return jsonify(dict(zip([d[0] for d in cur.description], row)))
|
|
|
|
|
|
@app.route('/rest/v1/products', methods=['POST'])
|
|
@require_auth
|
|
def create_product():
|
|
db = get_db()
|
|
data = request.get_json() or {}
|
|
required = ('slug', 'name', 'type')
|
|
for f in required:
|
|
if f not in data:
|
|
return jsonify({'error': f'missing required field: {f}'}), 400
|
|
cur = db.cursor()
|
|
cols = ['slug', 'name', 'description', 'type', 'billing_type', 'default_rate_cents',
|
|
'unit', 'billable', 'active', 'notes']
|
|
sql_cols = []
|
|
sql_vals = []
|
|
sql_params = []
|
|
for c in cols:
|
|
if c in data:
|
|
sql_cols.append(c)
|
|
sql_vals.append('%s')
|
|
sql_params.append(data[c])
|
|
cur.execute(
|
|
f"INSERT INTO products ({', '.join(sql_cols)}) VALUES ({', '.join(sql_vals)}) RETURNING *",
|
|
sql_params
|
|
)
|
|
row = cur.fetchone()
|
|
db.commit()
|
|
return jsonify(dict(zip([d[0] for d in cur.description], row))), 201
|
|
|
|
|
|
@app.route('/rest/v1/products/<uuid:product_id>', methods=['PATCH'])
|
|
@require_auth
|
|
def update_product(product_id):
|
|
db = get_db()
|
|
data = request.get_json() or {}
|
|
if not data:
|
|
return jsonify({'error': 'no fields to update'}), 400
|
|
cur = db.cursor()
|
|
setters = [f"{k} = %s" for k in data]
|
|
cur.execute(
|
|
f"UPDATE products SET {', '.join(setters)} WHERE id = %s RETURNING *",
|
|
list(data.values()) + [str(product_id)]
|
|
)
|
|
row = cur.fetchone()
|
|
if not row:
|
|
return jsonify({'error': 'not found'}), 404
|
|
db.commit()
|
|
return jsonify(dict(zip([d[0] for d in cur.description], row)))
|
|
|
|
|
|
@app.route('/rest/v1/products/<uuid:product_id>', methods=['DELETE'])
|
|
@require_auth
|
|
def delete_product(product_id):
|
|
db = get_db()
|
|
cur = db.cursor()
|
|
cur.execute("DELETE FROM products WHERE id = %s RETURNING id", (str(product_id),))
|
|
if not cur.fetchone():
|
|
return jsonify({'error': 'not found'}), 404
|
|
db.commit()
|
|
return '', 204
|
|
|
|
|
|
@app.route('/rest/v1/products/<uuid:product_id>/revenue', methods=['GET'])
|
|
@require_auth
|
|
def product_revenue(product_id):
|
|
"""Aggregate revenue/cost/margin for a single product."""
|
|
db = get_db()
|
|
cur = db.cursor()
|
|
cur.execute("SELECT * FROM v_product_revenue WHERE product_id = %s", (str(product_id),))
|
|
row = cur.fetchone()
|
|
if not row:
|
|
return jsonify({'error': 'not found'}), 404
|
|
cols = [d[0] for d in cur.description]
|
|
return jsonify(dict(zip(cols, row)))
|
|
|
|
|
|
# ══════════════════════════════════════════════════════════════════════════════
|
|
# PROJECTS
|
|
# ══════════════════════════════════════════════════════════════════════════════
|
|
|
|
@app.route('/rest/v1/projects', methods=['GET'])
|
|
@require_auth
|
|
def list_projects():
|
|
db = get_db()
|
|
cur = db.cursor()
|
|
filters = []
|
|
params = []
|
|
for col in ('slug', 'type', 'status', 'billable', 'company_id', 'product_id'):
|
|
val = request.args.get(col)
|
|
if val is not None:
|
|
filters.append(f"{col} = %s")
|
|
params.append(val)
|
|
where = ("WHERE " + " AND ".join(filters)) if filters else ""
|
|
limit = min(int(request.args.get('limit', 100)), 500)
|
|
offset = int(request.args.get('offset', 0))
|
|
cur.execute(f"SELECT * FROM projects {where} ORDER BY created_at DESC LIMIT %s OFFSET %s", params + [limit, offset])
|
|
cols = [d[0] for d in cur.description]
|
|
return jsonify([dict(zip(cols, r)) for r in cur.fetchall()])
|
|
|
|
|
|
@app.route('/rest/v1/projects/<uuid:project_id>', methods=['GET'])
|
|
@require_auth
|
|
def get_project(project_id):
|
|
db = get_db()
|
|
cur = db.cursor()
|
|
cur.execute("SELECT * FROM projects WHERE id = %s", (str(project_id),))
|
|
row = cur.fetchone()
|
|
if not row:
|
|
return jsonify({'error': 'not found'}), 404
|
|
return jsonify(dict(zip([d[0] for d in cur.description], row)))
|
|
|
|
|
|
@app.route('/rest/v1/projects', methods=['POST'])
|
|
@require_auth
|
|
def create_project():
|
|
db = get_db()
|
|
data = request.get_json() or {}
|
|
required = ('slug', 'name', 'type')
|
|
for f in required:
|
|
if f not in data:
|
|
return jsonify({'error': f'missing required field: {f}'}), 400
|
|
cur = db.cursor()
|
|
cols = ['slug', 'name', 'product_id', 'company_id', 'type', 'status', 'billable',
|
|
'rate_cents', 'price_cents', 'currency', 'start_date', 'end_date', 'notes']
|
|
sql_cols = []
|
|
sql_vals = []
|
|
sql_params = []
|
|
for c in cols:
|
|
if c in data:
|
|
sql_cols.append(c)
|
|
sql_vals.append('%s')
|
|
sql_params.append(data[c])
|
|
cur.execute(
|
|
f"INSERT INTO projects ({', '.join(sql_cols)}) VALUES ({', '.join(sql_vals)}) RETURNING *",
|
|
sql_params
|
|
)
|
|
row = cur.fetchone()
|
|
db.commit()
|
|
return jsonify(dict(zip([d[0] for d in cur.description], row))), 201
|
|
|
|
|
|
@app.route('/rest/v1/projects/<uuid:project_id>', methods=['PATCH'])
|
|
@require_auth
|
|
def update_project(project_id):
|
|
db = get_db()
|
|
data = request.get_json() or {}
|
|
if not data:
|
|
return jsonify({'error': 'no fields to update'}), 400
|
|
cur = db.cursor()
|
|
setters = [f"{k} = %s" for k in data]
|
|
cur.execute(
|
|
f"UPDATE projects SET {', '.join(setters)} WHERE id = %s RETURNING *",
|
|
list(data.values()) + [str(project_id)]
|
|
)
|
|
row = cur.fetchone()
|
|
if not row:
|
|
return jsonify({'error': 'not found'}), 404
|
|
db.commit()
|
|
return jsonify(dict(zip([d[0] for d in cur.description], row)))
|
|
|
|
|
|
@app.route('/rest/v1/projects/<uuid:project_id>', methods=['DELETE'])
|
|
@require_auth
|
|
def delete_project(project_id):
|
|
db = get_db()
|
|
cur = db.cursor()
|
|
cur.execute("DELETE FROM projects WHERE id = %s RETURNING id", (str(project_id),))
|
|
if not cur.fetchone():
|
|
return jsonify({'error': 'not found'}), 404
|
|
db.commit()
|
|
return '', 204
|
|
|
|
|
|
@app.route('/rest/v1/projects/<uuid:project_id>/summary', methods=['GET'])
|
|
@require_auth
|
|
def project_summary(project_id):
|
|
"""Aggregate hours and financials for a project."""
|
|
db = get_db()
|
|
cur = db.cursor()
|
|
cur.execute("SELECT * FROM v_project_summary WHERE id = %s", (str(project_id),))
|
|
row = cur.fetchone()
|
|
if not row:
|
|
return jsonify({'error': 'not found'}), 404
|
|
return jsonify(dict(zip([d[0] for d in cur.description], row)))
|
|
|
|
|
|
@app.route('/rest/v1/projects/<uuid:project_id>/time_entries', methods=['GET'])
|
|
@require_auth
|
|
def project_time_entries(project_id):
|
|
db = get_db()
|
|
cur = db.cursor()
|
|
limit = min(int(request.args.get('limit', 100)), 500)
|
|
offset = int(request.args.get('offset', 0))
|
|
cur.execute(
|
|
"SELECT * FROM time_entries WHERE project_id = %s ORDER BY date DESC LIMIT %s OFFSET %s",
|
|
(str(project_id), limit, offset)
|
|
)
|
|
cols = [d[0] for d in cur.description]
|
|
return jsonify([dict(zip(cols, r)) for r in cur.fetchall()])
|
|
|
|
|
|
# ══════════════════════════════════════════════════════════════════════════════
|
|
# TIME ENTRIES
|
|
# ══════════════════════════════════════════════════════════════════════════════
|
|
|
|
@app.route('/rest/v1/time_entries', methods=['GET'])
|
|
@require_auth
|
|
def list_time_entries():
|
|
db = get_db()
|
|
cur = db.cursor()
|
|
filters = []
|
|
params = []
|
|
for col in ('project_id', 'source'):
|
|
val = request.args.get(col)
|
|
if val is not None:
|
|
filters.append(f"{col} = %s")
|
|
params.append(val)
|
|
date_from = request.args.get('date_from')
|
|
date_to = request.args.get('date_to')
|
|
if date_from:
|
|
filters.append("date >= %s")
|
|
params.append(date_from)
|
|
if date_to:
|
|
filters.append("date <= %s")
|
|
params.append(date_to)
|
|
where = ("WHERE " + " AND ".join(filters)) if filters else ""
|
|
limit = min(int(request.args.get('limit', 100)), 500)
|
|
offset = int(request.args.get('offset', 0))
|
|
cur.execute(
|
|
f"SELECT * FROM time_entries {where} ORDER BY date DESC LIMIT %s OFFSET %s",
|
|
params + [limit, offset]
|
|
)
|
|
cols = [d[0] for d in cur.description]
|
|
return jsonify([dict(zip(cols, r)) for r in cur.fetchall()])
|
|
|
|
|
|
@app.route('/rest/v1/time_entries/<uuid:time_entry_id>', methods=['GET'])
|
|
@require_auth
|
|
def get_time_entry(time_entry_id):
|
|
db = get_db()
|
|
cur = db.cursor()
|
|
cur.execute("SELECT * FROM time_entries WHERE id = %s", (str(time_entry_id),))
|
|
row = cur.fetchone()
|
|
if not row:
|
|
return jsonify({'error': 'not found'}), 404
|
|
return jsonify(dict(zip([d[0] for d in cur.description], row)))
|
|
|
|
|
|
@app.route('/rest/v1/time_entries', methods=['POST'])
|
|
@require_auth
|
|
def create_time_entry():
|
|
db = get_db()
|
|
data = request.get_json() or {}
|
|
required = ('project_id',)
|
|
for f in required:
|
|
if f not in data:
|
|
return jsonify({'error': f'missing required field: {f}'}), 400
|
|
cur = db.cursor()
|
|
cols = ['project_id', 'date', 'ai_hours', 'human_hours', 'ai_rate_cents', 'description', 'source']
|
|
sql_cols = []
|
|
sql_vals = []
|
|
sql_params = []
|
|
for c in cols:
|
|
if c in data:
|
|
sql_cols.append(c)
|
|
sql_vals.append('%s')
|
|
sql_params.append(data[c])
|
|
cur.execute(
|
|
f"INSERT INTO time_entries ({', '.join(sql_cols)}) VALUES ({', '.join(sql_vals)}) RETURNING *",
|
|
sql_params
|
|
)
|
|
row = cur.fetchone()
|
|
db.commit()
|
|
return jsonify(dict(zip([d[0] for d in cur.description], row))), 201
|
|
|
|
|
|
@app.route('/rest/v1/time_entries/<uuid:time_entry_id>', methods=['PATCH'])
|
|
@require_auth
|
|
def update_time_entry(time_entry_id):
|
|
db = get_db()
|
|
data = request.get_json() or {}
|
|
if not data:
|
|
return jsonify({'error': 'no fields to update'}), 400
|
|
cur = db.cursor()
|
|
setters = [f"{k} = %s" for k in data]
|
|
cur.execute(
|
|
f"UPDATE time_entries SET {', '.join(setters)} WHERE id = %s RETURNING *",
|
|
list(data.values()) + [str(time_entry_id)]
|
|
)
|
|
row = cur.fetchone()
|
|
if not row:
|
|
return jsonify({'error': 'not found'}), 404
|
|
db.commit()
|
|
return jsonify(dict(zip([d[0] for d in cur.description], row)))
|
|
|
|
|
|
@app.route('/rest/v1/time_entries/<uuid:time_entry_id>', methods=['DELETE'])
|
|
@require_auth
|
|
def delete_time_entry(time_entry_id):
|
|
db = get_db()
|
|
cur = db.cursor()
|
|
cur.execute("DELETE FROM time_entries WHERE id = %s RETURNING id", (str(time_entry_id),))
|
|
if not cur.fetchone():
|
|
return jsonify({'error': 'not found'}), 404
|
|
db.commit()
|
|
return '', 204
|
|
|
|
|
|
# ══════════════════════════════════════════════════════════════════════════════
|
|
# REVENUE / ANALYTICS
|
|
# ══════════════════════════════════════════════════════════════════════════════
|
|
|
|
@app.route('/rest/v1/dashboard/product-revenue', methods=['GET'])
|
|
@require_auth
|
|
def dashboard_product_revenue():
|
|
"""Revenue/cost/margin across all products."""
|
|
db = get_db()
|
|
cur = db.cursor()
|
|
cur.execute("SELECT * FROM v_product_revenue ORDER BY total_revenue_cents DESC")
|
|
cols = [d[0] for d in cur.description]
|
|
return jsonify([dict(zip(cols, r)) for r in cur.fetchall()])
|
|
|
|
|
|
@app.route('/rest/v1/dashboard/project-summary', methods=['GET'])
|
|
@require_auth
|
|
def dashboard_project_summary():
|
|
"""Summary across all projects."""
|
|
db = get_db()
|
|
cur = db.cursor()
|
|
cur.execute("SELECT * FROM v_project_summary ORDER BY created_at DESC")
|
|
cols = [d[0] for d in cur.description]
|
|
return jsonify([dict(zip(cols, r)) for r in cur.fetchall()])
|
|
|
|
|
|
# ── Proposals / SOW Pipeline ─────────────────────────────────────────────────
|
|
|
|
@app.route('/rest/v1/proposals', methods=['GET'])
|
|
@require_auth
|
|
def list_proposals():
|
|
"""List all proposals with optional status filter."""
|
|
db = get_db()
|
|
cur = db.cursor()
|
|
status = request.args.get('status')
|
|
if status:
|
|
cur.execute("""
|
|
SELECT p.*, c.name as company_name, pr.name as project_name, pr.slug as project_slug
|
|
FROM proposals p
|
|
LEFT JOIN companies c ON p.company_id = c.id
|
|
LEFT JOIN projects pr ON p.project_id = pr.id
|
|
WHERE p.status = %s
|
|
ORDER BY p.created_at DESC
|
|
""", (status,))
|
|
else:
|
|
cur.execute("""
|
|
SELECT p.*, c.name as company_name, pr.name as project_name, pr.slug as project_slug
|
|
FROM proposals p
|
|
LEFT JOIN companies c ON p.company_id = c.id
|
|
LEFT JOIN projects pr ON p.project_id = pr.id
|
|
ORDER BY p.created_at DESC
|
|
""")
|
|
cols = [d[0] for d in cur.description]
|
|
rows = []
|
|
for r in cur.fetchall():
|
|
rows.append(dict(zip(cols, r)))
|
|
return jsonify(rows)
|
|
|
|
@app.route('/rest/v1/proposals', methods=['POST'])
|
|
@require_auth
|
|
def create_proposal():
|
|
data = request.json
|
|
db = get_db()
|
|
cur = db.cursor()
|
|
cur.execute("""
|
|
INSERT INTO proposals (project_id, company_id, name, sow_number, amount_cents, our_rate_cents, status, notes)
|
|
VALUES (%s, %s, %s, %s, %s, %s, %s, %s)
|
|
RETURNING *
|
|
""", (
|
|
data.get('project_id'),
|
|
data.get('company_id'),
|
|
data['name'],
|
|
data.get('sow_number'),
|
|
data['amount_cents'],
|
|
data.get('our_rate_cents'),
|
|
data.get('status', 'draft'),
|
|
data.get('notes', '')
|
|
))
|
|
cols = [d[0] for d in cur.description]
|
|
row = dict(zip(cols, cur.fetchone()))
|
|
db.commit()
|
|
return jsonify(row), 201
|
|
|
|
@app.route('/rest/v1/proposals/<uuid:proposal_id>', methods=['PATCH'])
|
|
@require_auth
|
|
def update_proposal(proposal_id):
|
|
"""Update proposal fields."""
|
|
data = request.json
|
|
if not data:
|
|
return jsonify({'error': 'no fields to update'}), 400
|
|
|
|
db = get_db()
|
|
cur = db.cursor()
|
|
|
|
# Build dynamic UPDATE from provided fields
|
|
allowed = ['name','sow_number','amount_cents','our_rate_cents','status',
|
|
'sent_at','decided_at','expires_at','notes']
|
|
updates = []
|
|
values = []
|
|
for col in allowed:
|
|
if col in data:
|
|
updates.append(f"{col} = %s")
|
|
values.append(data[col])
|
|
|
|
if not updates:
|
|
return jsonify({'error': 'no allowed fields to update'}), 400
|
|
|
|
values.append(str(proposal_id))
|
|
cur.execute(
|
|
f"UPDATE proposals SET {', '.join(updates)} WHERE id = %s RETURNING *",
|
|
tuple(values)
|
|
)
|
|
row = cur.fetchone()
|
|
db.commit()
|
|
if not row:
|
|
return jsonify({'error': 'not found'}), 404
|
|
cols = [d[0] for d in cur.description]
|
|
return jsonify(dict(zip(cols, row)))
|
|
|
|
@app.route('/rest/v1/dashboard/pipeline-proposals')
|
|
@require_auth
|
|
def dashboard_pipeline_proposals():
|
|
"""Active SOW proposals: draft + sent + negotiation."""
|
|
db = get_db()
|
|
cur = db.cursor()
|
|
cur.execute("""
|
|
SELECT
|
|
p.id,
|
|
p.name,
|
|
p.sow_number,
|
|
p.amount_cents,
|
|
p.our_rate_cents,
|
|
p.status,
|
|
p.sent_at,
|
|
c.name AS company_name,
|
|
pr.name AS project_name,
|
|
pr.slug AS project_slug
|
|
FROM proposals p
|
|
LEFT JOIN companies c ON p.company_id = c.id
|
|
LEFT JOIN projects pr ON p.project_id = pr.id
|
|
WHERE p.status IN ('draft', 'sent', 'negotiation')
|
|
ORDER BY p.created_at DESC
|
|
""")
|
|
cols = [d[0] for d in cur.description]
|
|
rows = [dict(zip(cols, r)) for r in cur.fetchall()]
|
|
# Add total
|
|
total = sum(r['amount_cents'] for r in rows)
|
|
return jsonify({'proposals': rows, 'total_pipeline_cents': total})
|
|
|
|
|
|
# ── LitterBox Analysis ─────────────────────────────────────────────────────────
|
|
|
|
@app.route('/rest/v1/litterbox/analyses', methods=['POST'])
|
|
@require_auth
|
|
def create_litterbox_analysis():
|
|
db = get_db()
|
|
cur = db.cursor()
|
|
data = request.get_json()
|
|
if not data:
|
|
return jsonify({'error': 'No JSON body'}), 400
|
|
cols = ['ir_case_id','engagement_id','case_id','md5','sha256','file_name','file_path','file_size_bytes','analysis_type','status']
|
|
vals = [data.get(c) for c in cols]
|
|
placeholders = ','.join(['%s'] * len(cols))
|
|
cur.execute(f"INSERT INTO litterbox_analyses ({','.join(cols)}) VALUES ({placeholders}) RETURNING *", vals)
|
|
db.commit()
|
|
row = dict(zip([d[0] for d in cur.description], cur.fetchone()))
|
|
return jsonify(row), 201
|
|
|
|
@app.route('/rest/v1/litterbox/analyses', methods=['GET'])
|
|
@require_auth
|
|
def list_litterbox_analyses():
|
|
db = get_db()
|
|
cur = db.cursor()
|
|
ir_case_id = request.args.get('ir_case_id')
|
|
limit = int(request.args.get('limit', 50))
|
|
offset = int(request.args.get('offset', 0))
|
|
if ir_case_id:
|
|
cur.execute('SELECT * FROM litterbox_analyses WHERE ir_case_id = %s ORDER BY created_at DESC LIMIT %s OFFSET %s', (str(ir_case_id), limit, offset))
|
|
else:
|
|
cur.execute('SELECT * FROM litterbox_analyses ORDER BY created_at DESC LIMIT %s OFFSET %s', (limit, offset))
|
|
rows = [dict(zip([d[0] for d in cur.description], r)) for r in cur.fetchall()]
|
|
return jsonify({'analyses': rows, 'count': len(rows)})
|
|
|
|
@app.route('/rest/v1/litterbox/analyses/<analysis_id>', methods=['GET'])
|
|
@require_auth
|
|
def get_litterbox_analysis(analysis_id):
|
|
db = get_db()
|
|
cur = db.cursor()
|
|
cur.execute('SELECT * FROM litterbox_analyses WHERE id = %s', (str(analysis_id),))
|
|
row = cur.fetchone()
|
|
if not row:
|
|
return jsonify({'error': 'Not found'}), 404
|
|
return jsonify(dict(zip([d[0] for d in cur.description], row)))
|
|
|
|
@app.route('/rest/v1/litterbox/analyses/<analysis_id>', methods=['PATCH'])
|
|
@require_auth
|
|
def update_litterbox_analysis(analysis_id):
|
|
db = get_db()
|
|
cur = db.cursor()
|
|
data = request.get_json()
|
|
if not data:
|
|
return jsonify({'error': 'No JSON body'}), 400
|
|
data.pop('id', None)
|
|
data.pop('created_at', None)
|
|
if data.get('status') == 'completed':
|
|
data['completed_at'] = 'now()'
|
|
data['updated_at'] = 'now()'
|
|
set_clauses = []
|
|
vals = []
|
|
jsonb_cols = {'result_json', 'yara_matches', 'static_analysis', 'dynamic_analysis', 'edr_data'}
|
|
for k, v in data.items():
|
|
if k in jsonb_cols and isinstance(v, (dict, list)):
|
|
set_clauses.append(f"{k} = %s::jsonb")
|
|
vals.append(json.dumps(v))
|
|
else:
|
|
set_clauses.append(f"{k} = %s")
|
|
vals.append(v)
|
|
cur.execute(f"UPDATE litterbox_analyses SET {','.join(set_clauses)} WHERE id = %s RETURNING *", vals + [str(analysis_id)])
|
|
db.commit()
|
|
row = cur.fetchone()
|
|
if not row:
|
|
return jsonify({'error': 'Not found'}), 404
|
|
return jsonify(dict(zip([d[0] for d in cur.description], row)))
|
|
|
|
@app.route('/rest/v1/litterbox/analyses/<analysis_id>/link-ir-case', methods=['POST'])
|
|
@require_auth
|
|
def link_litterbox_analysis_to_ir_case(analysis_id):
|
|
db = get_db()
|
|
cur = db.cursor()
|
|
data = request.get_json()
|
|
ir_case_id = data.get('ir_case_id')
|
|
if not ir_case_id:
|
|
return jsonify({'error': 'ir_case_id required'}), 400
|
|
cur.execute('UPDATE litterbox_analyses SET ir_case_id = %s, updated_at = now() WHERE id = %s RETURNING *', (str(ir_case_id), str(analysis_id)))
|
|
db.commit()
|
|
row = cur.fetchone()
|
|
if not row:
|
|
return jsonify({'error': 'Not found'}), 404
|
|
return jsonify(dict(zip([d[0] for d in cur.description], row)))
|
|
|
|
@app.route('/rest/v1/litterbox/analyses/<analysis_id>/results', methods=['GET'])
|
|
@require_auth
|
|
def get_litterbox_analysis_results(analysis_id):
|
|
db = get_db()
|
|
cur = db.cursor()
|
|
cur.execute('SELECT result_json, yara_matches, static_analysis, dynamic_analysis, edr_data, status, detection_score FROM litterbox_analyses WHERE id = %s', (str(analysis_id),))
|
|
row = cur.fetchone()
|
|
if not row:
|
|
return jsonify({'error': 'Not found'}), 404
|
|
return jsonify({
|
|
'result_json': row[0],
|
|
'yara_matches': row[1],
|
|
'static_analysis': row[2],
|
|
'dynamic_analysis': row[3],
|
|
'edr_data': row[4],
|
|
'status': row[5],
|
|
'detection_score': row[6]
|
|
})
|
|
|
|
|
|
# ── Bootstrap ─────────────────────────────────────────────────────────────────
|
|
|
|
if __name__ == '__main__':
|
|
import sys
|
|
port = int(sys.argv[1]) if len(sys.argv) > 1 else 3000
|
|
CORS(app)
|
|
app.run(host='0.0.0.0', port=port, debug=False)
|