66 lines
2.0 KiB
PL/PgSQL
66 lines
2.0 KiB
PL/PgSQL
BEGIN;
|
|
|
|
DO $$
|
|
BEGIN
|
|
IF NOT EXISTS (SELECT 1 FROM pg_roles WHERE rolname = 'interaction_role') THEN
|
|
CREATE ROLE interaction_role LOGIN;
|
|
END IF;
|
|
END $$;
|
|
|
|
CREATE SCHEMA IF NOT EXISTS interaction AUTHORIZATION interaction_role;
|
|
REVOKE ALL ON SCHEMA interaction FROM PUBLIC;
|
|
|
|
SET ROLE interaction_role;
|
|
|
|
CREATE TABLE IF NOT EXISTS interaction.conversations (
|
|
conversation_id TEXT PRIMARY KEY,
|
|
actor_id TEXT NOT NULL,
|
|
principal_id TEXT NOT NULL,
|
|
channel TEXT NOT NULL,
|
|
state TEXT NOT NULL,
|
|
created_at_unix_millis BIGINT NOT NULL,
|
|
updated_at_unix_millis BIGINT NOT NULL,
|
|
CHECK (channel = 'WEB_CHAT'),
|
|
CHECK (state IN ('OPEN','ARCHIVED'))
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS interaction.utterances (
|
|
utterance_id TEXT PRIMARY KEY,
|
|
conversation_id TEXT NOT NULL,
|
|
actor_id TEXT NOT NULL,
|
|
principal_id TEXT NOT NULL,
|
|
channel TEXT NOT NULL,
|
|
received_at_unix_millis BIGINT NOT NULL,
|
|
correlation_id TEXT NOT NULL,
|
|
content TEXT NOT NULL,
|
|
content_ref TEXT NOT NULL,
|
|
speech_act TEXT NOT NULL,
|
|
interpretation_state TEXT NOT NULL,
|
|
corrects_utterance_id TEXT NOT NULL DEFAULT '',
|
|
CHECK (channel = 'WEB_CHAT'),
|
|
CHECK (content <> ''),
|
|
CHECK (content_ref <> ''),
|
|
CHECK (speech_act IN ('DIRECTIVE','ASSERTION','DECISION','PREFERENCE','QUESTION','HYPOTHESIS','CORRECTION','REQUEST_TO_INVESTIGATE','CONFIRMATION','REJECTION','CANCELLATION','CONVERSATIONAL','UNKNOWN')),
|
|
CHECK (interpretation_state IN ('RECORDED','INTERPRETED'))
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS interaction.original_human_intents (
|
|
intent_id TEXT PRIMARY KEY,
|
|
utterance_id TEXT NOT NULL,
|
|
recorded_at_unix_millis BIGINT NOT NULL,
|
|
text TEXT NOT NULL,
|
|
CHECK (text <> '')
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS interaction.relations (
|
|
relation_id TEXT PRIMARY KEY,
|
|
from_utterance_id TEXT NOT NULL,
|
|
to_utterance_id TEXT NOT NULL,
|
|
kind TEXT NOT NULL,
|
|
recorded_at_unix_millis BIGINT NOT NULL,
|
|
CHECK (kind IN ('CORRECTS','RESPONDS_TO','REQUESTS_COGNITION','COGNITION_FALLBACK'))
|
|
);
|
|
|
|
RESET ROLE;
|
|
COMMIT;
|