Write to Fresh Jots from SQL — Postgres, MySQL, SQL Server

CREATE EXTENSION http;

-- Once, as a superuser. The setting persists across sessions.
ALTER DATABASE my_app SET app.fjots_token = 'mn_yourrealtokenhere';

SELECT status, content::text

FROM http_post(
  'https://freshjots.com/api/v1/notes/by-filename/db-events/append',
  jsonb_build_object('text', 'manual backup verified at ' || now())::text,
  'application/json',
  ARRAY[http_header('Authorization',
                    'Bearer ' || current_setting('app.fjots_token'))]
);

CREATE EXTENSION pg_net;
-- pg_net's worker process needs to be running — on Supabase it's
-- managed automatically; on self-hosted Postgres see the pg_net README.

CREATE OR REPLACE FUNCTION fjots_log_order()
RETURNS TRIGGER AS $$
BEGIN
  PERFORM net.http_post(
    url     := 'https://freshjots.com/api/v1/notes/by-filename/orders/append',
    body    := jsonb_build_object(
                 'text', format('order %s — %s @ %s',
                                NEW.id, NEW.customer_email, NEW.created_at)
               ),
    headers := jsonb_build_object(
                 'Authorization', 'Bearer ' || current_setting('app.fjots_token'),
                 'Content-Type',  'application/json'
               )
  );
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER fjots_log_order_trg
AFTER INSERT ON orders
FOR EACH ROWEXECUTE FUNCTION fjots_log_order();

-- Recent requests
SELECT id, status_code, error_msg, created
FROM net._http_response
ORDER BY created DESC
LIMIT 20;

-- Drop completed ones to keep the table small (pg_net has a cleanup
-- worker for this, but you can also do it manually):
SELECT net.http_collect_response(id) FROM net._http_response WHERE created < now() - interval '7 days';

-- MySQL syntax. PG/MSSQL: change types as appropriate.
CREATE TABLE fjots_outbox (
  id          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  filename    VARCHAR(255)    NOT NULL,
  body_text   TEXT            NOT NULL,
  created_at  TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP,
  sent_at     TIMESTAMP       NULL,
  last_error  TEXT            NULL,
  INDEX (sent_at, id)
);

DELIMITER $$
CREATE TRIGGER log_orders_to_fjots
AFTER INSERT ON orders
FOR EACH ROW
BEGIN
  INSERT INTO fjots_outbox (filename, body_text)
  VALUES (
    'orders',
    CONCAT('order ', NEW.id, ' — ', NEW.customer_email, ' @ ', NEW.created_at)  );
END$$
DELIMITER ;

# fjots_outbox_drainer.py
import os
import time
import freshjots  # pip install freshjots
import pymysql    # or psycopg / pyodbc / etc.

DB = pymysql.connect(host="db", user="…", password="…", database="my_app")
client = freshjots.Client()  # reads FRESHJOTS_TOKEN

def drain_once():
    with DB.cursor() as cur:
        cur.execute(
            "SELECT id, filename, body_text "
            "FROM fjots_outbox WHERE sent_at IS NULL "
            "ORDER BY id LIMIT 100 FOR UPDATE SKIP LOCKED"
        )
        rows = cur.fetchall()
        for row_id, filename, text in rows:
            try:
                client.append(filename, text)
                cur.execute("UPDATE fjots_outbox SET sent_at = NOW() WHERE id = %s", (row_id,))
            except freshjots.ApiError as e:
                cur.execute(
                    "UPDATE fjots_outbox SET last_error = %s WHERE id = %s",
                    (f"{e.status} {e.code}: {e}", row_id),
                )
        DB.commit()

while True:
    drain_once()
    time.sleep(2)

DECLARE @response NVARCHAR(MAX);
DECLARE @body NVARCHAR(MAX) = N'{"text":"backup completed"}';

EXEC sp_invoke_external_rest_endpoint
    @url     = N'https://freshjots.com/api/v1/notes/by-filename/db-events/append',
    @method  = 'POST',
    @headers = N'{"Authorization":"Bearer mn_yourtoken","Content-Type":"application/json"}',
    @payload = @body,
    @response = @response OUTPUT;


SELECT JSON_VALUE(@response, '$.response.status.http.code') AS http_code;

-- Postgres + pg_net
CREATE OR REPLACE FUNCTION fjots_audit_email_change()
RETURNS TRIGGER AS $$
BEGIN
  IF OLD.email IS DISTINCT FROM NEW.email THEN
    PERFORM net.http_post(
      url     := 'https://freshjots.com/api/v1/notes/by-filename/email-audit/append',
      body    := jsonb_build_object(
                   'text', format('user %s: %s → %s by %s @ %s',
                                  NEW.id, OLD.email, NEW.email,
                                  current_user, now())
                 ),
      headers := jsonb_build_object(
                   'Authorization', 'Bearer ' || current_setting('app.fjots_token'),
                   'Content-Type',  'application/json'
                 )
    );
  END IF;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER fjots_audit_email_change_trg

AFTER UPDATE ON users
FOR EACH ROW
EXECUTE FUNCTION fjots_audit_email_change();

SELECT cron.schedule('fjots-daily', '0 9 * * *', $$
  SELECT http_post(
    'https://freshjots.com/api/v1/notes/by-filename/daily-stats/append',
    jsonb_build_object(
      'text', format(
        'date=%s users=%s orders=%s revenue=%s',
        current_date,
        (SELECT count(*) FROM users WHERE created_at::date = current_date),
        (SELECT count(*) FROM orders WHERE created_at::date = current_date),
        (SELECT sum(amount) FROM orders WHERE created_at::date = current_date)
      )
    )::text,
    'application/json',
    ARRAY[http_header('Authorization',
                      'Bearer ' || current_setting('app.fjots_token'))]
  );$$);