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.freshjots_token = 'mn_yourrealtokenhere';

SELECT status, content::text

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

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 freshjots_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.freshjots_token'),
                 'Content-Type',  'application/json'
               )
  );
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER freshjots_log_order_trg
AFTER INSERT ON orders
FOR EACH ROWEXECUTE FUNCTION freshjots_log_order();

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

-- Prune old responses (keep last 7 days). Plain DELETE — no need to call
-- net.http_collect_response, which exists to retrieve a single response by
-- id and remove it as a side-effect (useful when you want to read the
-- result), not as a bulk-cleanup primitive.
DELETE FROM net._http_response WHERE created < now() - interval '7 days';

SELECT cron.schedule('pg_net-prune', '17 3 * * *', $$ DELETE FROM net._http_response WHERE created < now() - interval '7 days'; $$);

-- MySQL syntax. PG/MSSQL: change types as appropriate.
CREATE TABLE freshjots_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,
  attempts    INT             NOT NULL DEFAULT 0,
  last_error  TEXT            NULL,
  INDEX (sent_at, id)
);

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

# freshjots_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 freshjots_outbox WHERE sent_at IS NULL AND attempts < 20 "
            "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 freshjots_outbox SET sent_at = NOW() WHERE id = %s", (row_id,))
            except freshjots.ApiError as e:
                cur.execute(
                    "UPDATE freshjots_outbox "
                    "SET attempts = attempts + 1, last_error = %s WHERE id = %s",
                    (f"{e.status} {e.code}: {e}", row_id),
                )
            DB.commit()  # release this row's lock before the next HTTP call

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 freshjots_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.freshjots_token'),
                   'Content-Type',  'application/json'
                 )
    );
  END IF;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER freshjots_audit_email_change_trg

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

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