xiaoshu-app/scripts/sql/word-audio.sql

-- Server-only cache. Provider work runs outside short SQL transactions.
CREATE TABLE IF NOT EXISTS "WordAudioCache" (
 "objectId" text PRIMARY KEY, company text NOT NULL, word text NOT NULL, phonetic text NOT NULL,
 voice text NOT NULL, status text NOT NULL DEFAULT 'pending', url text NOT NULL DEFAULT '',
 audio text NOT NULL DEFAULT '', lease text, "leaseUntil" timestamptz,
 "retryAfter" timestamptz, "lastError" text, "createdAt" timestamptz NOT NULL DEFAULT now(),
 "updatedAt" timestamptz NOT NULL DEFAULT now()
);
CREATE TABLE IF NOT EXISTS "WordAudioAttempt" (
 "objectId" text PRIMARY KEY, company text NOT NULL, actor text NOT NULL, "cacheId" text NOT NULL,
 "providerCall" boolean NOT NULL, status text NOT NULL DEFAULT 'started',
 "createdAt" timestamptz NOT NULL DEFAULT now(), "updatedAt" timestamptz NOT NULL DEFAULT now()
);
-- Parse may have registered these tables before this migration; apply defaults there too.
ALTER TABLE "WordAudioCache" ALTER COLUMN status SET DEFAULT 'pending', ALTER COLUMN url SET DEFAULT '', ALTER COLUMN audio SET DEFAULT '', ALTER COLUMN "createdAt" SET DEFAULT now(), ALTER COLUMN "updatedAt" SET DEFAULT now();
ALTER TABLE "WordAudioAttempt" ALTER COLUMN status SET DEFAULT 'started', ALTER COLUMN "createdAt" SET DEFAULT now(), ALTER COLUMN "updatedAt" SET DEFAULT now();
UPDATE "WordAudioCache" SET status=COALESCE(status,'pending'),url=COALESCE(url,''),audio=COALESCE(audio,'');
CREATE INDEX IF NOT EXISTS word_audio_attempt_usage ON "WordAudioAttempt" (company,"createdAt",actor);
CREATE INDEX IF NOT EXISTS word_audio_leases ON "WordAudioCache" (company,"leaseUntil");
CREATE OR REPLACE FUNCTION xs_word_audio_claim(co text, cache_id text, actor_id text, token text,
 word_text text, phonetic_text text, voice_text text, daily_limit int, minute_limit int, concurrency_limit int)
RETURNS jsonb LANGUAGE plpgsql AS $$
DECLARE r "WordAudioCache"; n int;
BEGIN
 PERFORM pg_advisory_xact_lock(hashtext('word-audio:'||co));
 INSERT INTO "WordAudioCache" ("objectId",company,word,phonetic,voice)
 VALUES(cache_id,co,word_text,phonetic_text,voice_text) ON CONFLICT DO NOTHING;
 SELECT * INTO r FROM "WordAudioCache" WHERE "objectId"=cache_id AND company=co FOR UPDATE;
 IF NOT FOUND THEN RAISE EXCEPTION 'audio cache tenant mismatch'; END IF;
 IF r.status='ready' THEN RETURN jsonb_build_object('state','ready','url',r.url); END IF;
 IF r."leaseUntil">now() THEN RETURN jsonb_build_object('state','pending'); END IF;
 IF r."retryAfter">now() THEN RETURN jsonb_build_object('state','retry'); END IF;
 SELECT count(*) INTO n FROM "WordAudioAttempt" WHERE company=co AND actor=actor_id AND "createdAt">now()-interval '1 minute';
 IF n>=minute_limit THEN RETURN jsonb_build_object('state','limited'); END IF;
 SELECT count(*) INTO n FROM "WordAudioCache" WHERE company=co AND "leaseUntil">now();
 IF n>=concurrency_limit THEN RETURN jsonb_build_object('state','pending'); END IF;
 IF r.audio='' THEN
  SELECT count(*) INTO n FROM "WordAudioAttempt" WHERE company=co AND "providerCall" AND "createdAt">=date_trunc('day',now() AT TIME ZONE 'Asia/Shanghai') AT TIME ZONE 'Asia/Shanghai';
  IF n>=daily_limit THEN RETURN jsonb_build_object('state','quota'); END IF;
 END IF;
 UPDATE "WordAudioCache" SET status='generating',lease=token,"leaseUntil"=now()+interval '90 seconds',"updatedAt"=now() WHERE "objectId"=cache_id;
 INSERT INTO "WordAudioAttempt" ("objectId",company,actor,"cacheId","providerCall") VALUES(token,co,actor_id,cache_id,r.audio='');
 RETURN jsonb_build_object('state','claimed','audio',r.audio);
END $$;