testdrive-agencymeeting.sql

DROP TABLE IF EXISTS "testdrive";

CREATE TABLE IF NOT EXISTS "testdrive" (
   "id" TEXT NOT NULL,
   "steps" OBJECT(STRICT) AS (
      "transcribe" TEXT,
      "parse" TEXT,
      "load" TEXT
   ),
   "timeline" OBJECT(STRICT) AS (
      "transcribe" TIMESTAMP WITH TIME ZONE,
      "parse" TIMESTAMP WITH TIME ZONE,
      "load" TIMESTAMP WITH TIME ZONE
   ),
   "transcription" OBJECT(STRICT) AS (
      "uniqueId" TEXT,
      "downloader" TEXT
   ),
   "meetingId" TEXT,
   "meetingDate" TIMESTAMP WITH TIME ZONE,
   "publishedDate" TIMESTAMP WITH TIME ZONE,
   "keywords" ARRAY(TEXT),
   "links" ARRAY(OBJECT(STRICT) AS (
      "url" TEXT
   )),
   "sourceType" TEXT,
   "sourceUrl" TEXT,
   "videoURLs" ARRAY(TEXT),
   "title" TEXT,
   "description" TEXT,
   "agency" OBJECT(STRICT) AS (
      "agencyId" BIGINT,
      "name" TEXT,
      "stateCode" TEXT
   ),
   "documents" ARRAY(OBJECT(STRICT) AS (
      "url" TEXT,
      "requestURL" TEXT,
      "type" TEXT,
      "contentType" TEXT,
      "contentLength" BIGINT,
      "filename" TEXT
   )),
   "createdAt" TIMESTAMP WITH TIME ZONE,
   "updatedAt" TIMESTAMP WITH TIME ZONE,
   PRIMARY KEY ("id")
);

INSERT INTO "testdrive" ("meetingId","agency","createdAt","meetingDate","sourceType","sourceUrl","steps","title","updatedAt","videoURLs","timeline","transcription","id") VALUES (
   'swagit-coronadousdca-10102023-517',
   {
    "agencyId" = 72611,
    "name" = 'Coronado Unified School District',
    "stateCode" = 'CA'
   },
   '2023-10-10T20:26:19.172Z',
   '2023-10-05T12:00:00.000Z',
   'swagit',
   'http://coronadousdca.swagit.com/specialty',
   { "load" = 'complete', "parse" = 'incomplete', "transcribe" = 'complete' },
   'KCMS 10-5 Broadcast',
   '2023-10-10T21:20:53.694Z',
   [
    'https://admin-v2.swagit.com/podcasts/2023/10/10/10102023-518.360.mp4',
    'https://stream.us-central1-b.swagit.com/vod/_definst_/admin-v2/2023/10/10/10102023-518.360.mp4/playlist.m3u8'
   ],
   {
    "transcribe" = '2023-10-10T21:10:00.642Z',
    "load" = '2023-10-10T21:20:53.694Z'
   },
   { "uniqueId" = '6525b448c484e452fe48df08' },
   '6525b36b3d9cfcf8ec8f732b'
)
ON CONFLICT (id) DO UPDATE SET 
   "updatedAt" = '2023-10-10T20:30:00.748Z',
   "timeline" = { "transcribe" = '2023-10-10T20:30:00.748Z' },
   "transcription" = { "uniqueId" = '6525b448c484e452fe48df08' },
   "steps"['transcribe'] = 'pending';