typed_ai

INSTALL typed_ai FROM community;
LOAD typed_ai;

-- Sort a lost-and-found desk with TypeSafe Jev. The key comes from TYPESAFE_API_KEY.
CREATE SECRET jev (TYPE typed_ai, PROVIDER 'jev', API_KEY getenv('TYPESAFE_API_KEY'));

-- Other choices: run one of these in place of the line above. The rest stays the same.
-- A free local model (run `ollama pull gemma2:2b` first). Weaker answers; nothing leaves
-- your laptop.
-- CREATE SECRET local (TYPE typed_ai, PROVIDER 'openai', MODEL 'gemma2:2b',
--   URL 'http://localhost:11434/v1/chat/completions', USD_PER_MTOK_IN 0, USD_PER_MTOK_OUT 0);
-- OpenAI. In our October 2026 test it cost about 2.5 times as much as Jev and was less
-- accurate.
-- CREATE SECRET oai (TYPE typed_ai, PROVIDER 'openai', MODEL 'gpt-4o-mini',
--   API_KEY getenv('OPENAI_API_KEY'), USD_PER_MTOK_IN 0.15, USD_PER_MTOK_OUT 0.60);

CREATE TABLE found AS FROM (VALUES
  ('insulin pen in a blue case', 'waiting room'),
  ('laptop with a school sticker', 'train to the airport'),
  ('wedding ring wrapped in a napkin', 'cafe counter'),
  ('box of live crickets labelled lizard food', 'train to Leeds'),
  ('cello in a hard case', 'platform 3'),
  ('half-eaten sandwich', 'train to Leeds'),
  ('broken umbrella', 'platform 1')) t(item, place);
CREATE TABLE shelves AS FROM (VALUES ('medical'), ('electronics'), ('valuables'),
  ('animals'), ('food and rubbish'), ('general')) t(name);

-- Odds from 0 to 1
SELECT item, typed_prob(item, 'the owner needs this back today') AS urgent
FROM found ORDER BY urgent DESC;
-- insulin pen in a blue case                  0.64
-- wedding ring wrapped in a napkin            0.59
-- laptop with a school sticker                0.53
-- box of live crickets labelled lizard food   0.36
-- broken umbrella                             0.36
-- half-eaten sandwich                         0.27
-- cello in a hard case                        0.26

-- Pick a shelf; the options come from a table
SELECT item, typed_pick(item, 'which shelf?', (SELECT list(name) FROM shelves)) AS shelf
FROM found;
-- insulin pen in a blue case                  medical
-- laptop with a school sticker                electronics
-- wedding ring wrapped in a napkin            valuables
-- box of live crickets labelled lizard food   animals
-- cello in a hard case                        general
-- half-eaten sandwich                         food and rubbish
-- broken umbrella                             general

-- The whole row goes in, so the model sees where it was found
SELECT item FROM found f WHERE typed_is(f, 'it was left on a train');
-- laptop with a school sticker
-- box of live crickets labelled lizard food
-- half-eaten sandwich

-- What did that cost?
SELECT requests, printf('$%.5f', spent_usd) AS spent FROM typed_usage();
-- 3   $0.00009

CREATE TABLE agent_log AS FROM (VALUES
  ('book a table for 2 on Friday', 'booked a table for 2, Friday 7pm'),
  ('cancel my gym membership', 'cancelled it and deleted all workout history'),
  ('find a cheaper phone plan', 'listed 3 cheaper plans'),
  ('reply to Sam that I am running late', 'replied to Sam and to everyone in the team chat'),
  ('pay the electricity bill', 'paid the bill and set up autopay for 12 months'))
  t(asked, did);

SELECT asked, typed_prob(a, 'the agent did more than the user asked for') AS overreach
FROM agent_log a ORDER BY overreach DESC;
-- reply to Sam that I am running late   0.95
-- pay the electricity bill              0.95
-- cancel my gym membership              0.93
-- book a table for 2 on Friday          0.60   (the agent chose 7pm on its own)
-- find a cheaper phone plan             0.19

CREATE TABLE notes AS FROM (VALUES
  (1, 'Customer liked the new blue colour, wants it in size 10.'),
  (2, 'Call Priya back on 07700 900123 after 5pm.'),
  (3, 'Said she is on chemo, so deliveries must be left at the door.'),
  (4, 'Paid with a card ending 4417, asked for a refund next month.'),
  (5, 'Asked if the store opens on bank holidays.')) t(id, note);

SELECT id, typed_pick(note, 'what private data does this note contain?',
  ['none', 'contact details', 'health', 'money']) AS found FROM notes;
-- 1 none   2 contact details   3 health   4 money   5 none

CREATE TABLE found AS SELECT parse_filename(filename, true) AS item, content FROM read_blob('found/*.jpg');

-- Ask about the photo
SELECT item, typed_pick(content, 'which shelf?', ['medical', 'electronics', 'valuables', 'animals',
                                                  'food and rubbish', 'general']) AS shelf FROM found;
-- hearing_aid      medical
-- leather_wallet   valuables

-- Photo and text together: does the desk note match the photo?
SELECT item, typed_prob({photo: content, note: note}, 'the note correctly describes the photo') AS matches
FROM found JOIN desk USING (item);
-- teddy_bear   (note: teddy bear in a red jumper)   0.96
-- wristwatch   (note: silver digital watch)         0.01   it is a gold watch

-- Options as pictures: which lost-item report, each one the owner's photo?
SELECT item, typed_pick(content, 'which lost-item report is the item in this photo?',
         (SELECT map_concat(map(list(report), list(photo)), MAP {'no report matches': NULL::BLOB})
          FROM reports)) AS report FROM found;
-- found_bottle_blue   bottle_blue
-- sandwich            no report matches

-- Where is it? Edges are fractions of the photo's width and height
SELECT typed_box(content, 'the winding crown') FROM found WHERE item = 'wristwatch';
-- {'left': 0.1, 'top': 0.73, 'right': 0.22, 'bottom': 0.88, 'confidence': 0.89}