forked from sagnik/Project_Velocity
351 lines
19 KiB
SQL
351 lines
19 KiB
SQL
-- ────────────────────────────────────────────────────────────────────────────
|
|
-- Oracle Schema Extension v2 — Multi-Surface Platform and Oracle Expansion
|
|
-- Date: 2026-04-18
|
|
-- Author: Velocity Platform Team
|
|
-- Depends on: schema_oracle.sql (must be applied first)
|
|
-- PostgreSQL 14+ required · UUID via pgcrypto already enabled
|
|
-- ────────────────────────────────────────────────────────────────────────────
|
|
|
|
-- ─── 1. Oracle Template Taxonomy ─────────────────────────────────────────────
|
|
|
|
CREATE TABLE IF NOT EXISTS oracle_template_chapters (
|
|
chapter_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
tenant_id TEXT NOT NULL,
|
|
name TEXT NOT NULL,
|
|
description TEXT,
|
|
sort_order INTEGER NOT NULL DEFAULT 0,
|
|
is_active BOOLEAN NOT NULL DEFAULT TRUE,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS oracle_template_subchapters (
|
|
subchapter_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
chapter_id UUID NOT NULL REFERENCES oracle_template_chapters(chapter_id) ON DELETE CASCADE,
|
|
tenant_id TEXT NOT NULL,
|
|
name TEXT NOT NULL,
|
|
description TEXT,
|
|
sort_order INTEGER NOT NULL DEFAULT 0,
|
|
is_active BOOLEAN NOT NULL DEFAULT TRUE,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS oracle_template_seed_examples (
|
|
example_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
template_id UUID NOT NULL REFERENCES oracle_component_templates(template_id) ON DELETE CASCADE,
|
|
chapter_id UUID REFERENCES oracle_template_chapters(chapter_id),
|
|
subchapter_id UUID REFERENCES oracle_template_subchapters(subchapter_id),
|
|
title TEXT NOT NULL,
|
|
example_json JSONB NOT NULL,
|
|
quality_notes TEXT,
|
|
is_canonical BOOLEAN NOT NULL DEFAULT FALSE,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
|
);
|
|
|
|
-- Extend oracle_component_templates with chapter/subchapter linkage
|
|
-- (additive columns — does not alter existing rows)
|
|
ALTER TABLE oracle_component_templates
|
|
ADD COLUMN IF NOT EXISTS chapter_id UUID REFERENCES oracle_template_chapters(chapter_id),
|
|
ADD COLUMN IF NOT EXISTS subchapter_id UUID REFERENCES oracle_template_subchapters(subchapter_id),
|
|
ADD COLUMN IF NOT EXISTS json_template JSONB,
|
|
ADD COLUMN IF NOT EXISTS description TEXT;
|
|
|
|
-- ─── 2. Kimi Synthetic Data Jobs ─────────────────────────────────────────────
|
|
|
|
CREATE TABLE IF NOT EXISTS oracle_synthetic_generation_jobs (
|
|
job_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
tenant_id TEXT NOT NULL,
|
|
template_id UUID NOT NULL REFERENCES oracle_component_templates(template_id),
|
|
chapter_id UUID REFERENCES oracle_template_chapters(chapter_id),
|
|
subchapter_id UUID REFERENCES oracle_template_subchapters(subchapter_id),
|
|
model TEXT NOT NULL DEFAULT 'kimi',
|
|
status TEXT NOT NULL DEFAULT 'pending'
|
|
CHECK (status IN ('pending','running','completed','failed','cancelled')),
|
|
requested_count INTEGER NOT NULL DEFAULT 10,
|
|
accepted_count INTEGER NOT NULL DEFAULT 0,
|
|
error_message TEXT,
|
|
started_at TIMESTAMPTZ,
|
|
completed_at TIMESTAMPTZ,
|
|
created_by TEXT NOT NULL,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
|
);
|
|
|
|
-- ─── 3. Inventory Pipeline ───────────────────────────────────────────────────
|
|
|
|
CREATE TABLE IF NOT EXISTS inventory_import_batches (
|
|
batch_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
tenant_id TEXT NOT NULL,
|
|
source_type TEXT NOT NULL CHECK (source_type IN ('csv','json','api_push','manual')),
|
|
submitted_by TEXT NOT NULL,
|
|
status TEXT NOT NULL DEFAULT 'pending'
|
|
CHECK (status IN ('pending','validating','processing','completed','failed','partial')),
|
|
total_rows INTEGER NOT NULL DEFAULT 0,
|
|
accepted_rows INTEGER NOT NULL DEFAULT 0,
|
|
rejected_rows INTEGER NOT NULL DEFAULT 0,
|
|
error_summary JSONB NOT NULL DEFAULT '[]'::JSONB,
|
|
source_file_ref TEXT,
|
|
started_at TIMESTAMPTZ,
|
|
completed_at TIMESTAMPTZ,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS inventory_properties (
|
|
property_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
tenant_id TEXT NOT NULL,
|
|
batch_id UUID REFERENCES inventory_import_batches(batch_id),
|
|
source_id TEXT, -- external source identifier
|
|
project_name TEXT NOT NULL,
|
|
developer_name TEXT NOT NULL,
|
|
location JSONB NOT NULL DEFAULT '{}'::JSONB, -- {city, district, lat, lng}
|
|
property_type TEXT NOT NULL, -- apartment, villa, penthouse, plot, etc.
|
|
price_bands JSONB NOT NULL DEFAULT '[]'::JSONB, -- [{minAED, maxAED, unitType}]
|
|
unit_mix JSONB NOT NULL DEFAULT '[]'::JSONB, -- [{bedrooms, count, sizeSqft}]
|
|
amenities TEXT[] NOT NULL DEFAULT '{}',
|
|
status TEXT NOT NULL DEFAULT 'active'
|
|
CHECK (status IN ('active','archived','draft','under_review')),
|
|
validation_state JSONB NOT NULL DEFAULT '{}'::JSONB,
|
|
ingested_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS inventory_media_assets (
|
|
media_asset_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
property_id UUID NOT NULL REFERENCES inventory_properties(property_id) ON DELETE CASCADE,
|
|
tenant_id TEXT NOT NULL,
|
|
media_type TEXT NOT NULL CHECK (media_type IN ('image','video','floorplan','brochure','360','vr')),
|
|
url TEXT NOT NULL,
|
|
thumbnail_url TEXT,
|
|
sort_order INTEGER NOT NULL DEFAULT 0,
|
|
metadata JSONB NOT NULL DEFAULT '{}'::JSONB,
|
|
uploaded_by TEXT NOT NULL,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
|
);
|
|
|
|
-- ─── 4. Edge Communication Events ────────────────────────────────────────────
|
|
|
|
CREATE TABLE IF NOT EXISTS edge_communication_events (
|
|
event_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
tenant_id TEXT NOT NULL,
|
|
lead_id TEXT NOT NULL,
|
|
channel TEXT NOT NULL
|
|
CHECK (channel IN ('pstn','whatsapp_message','whatsapp_voice',
|
|
'whatsapp_video','email','facebook_message',
|
|
'instagram_message','in_app_voip','manual_note')),
|
|
direction TEXT NOT NULL CHECK (direction IN ('inbound','outbound')),
|
|
provider TEXT, -- twilio, vonage, meta, etc.
|
|
capture_mode TEXT NOT NULL
|
|
CHECK (capture_mode IN ('direct_api','provider_routed','operator_import','operator_note')),
|
|
consent_state TEXT NOT NULL DEFAULT 'unknown'
|
|
CHECK (consent_state IN ('unknown','granted','denied','not_required')),
|
|
timestamp TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
duration_seconds INTEGER,
|
|
summary TEXT,
|
|
raw_reference TEXT, -- provider message/call ID
|
|
recording_ref TEXT, -- storage path or URL
|
|
provider_metadata JSONB NOT NULL DEFAULT '{}'::JSONB,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS edge_communication_memory_facts (
|
|
fact_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
tenant_id TEXT NOT NULL,
|
|
lead_id TEXT NOT NULL,
|
|
event_id UUID REFERENCES edge_communication_events(event_id),
|
|
fact_type TEXT NOT NULL
|
|
CHECK (fact_type IN ('promise','preference','follow_up_date',
|
|
'objection','interest_signal','budget','timeline',
|
|
'constraint','decision_maker_note','custom')),
|
|
fact_text TEXT NOT NULL,
|
|
effective_date DATE,
|
|
confidence NUMERIC(4,3) NOT NULL DEFAULT 1.0 CHECK (confidence BETWEEN 0 AND 1),
|
|
extracted_from TEXT NOT NULL
|
|
CHECK (extracted_from IN ('transcript','message_thread','operator_note','import')),
|
|
is_confirmed BOOLEAN NOT NULL DEFAULT FALSE,
|
|
confirmed_by TEXT,
|
|
confirmed_at TIMESTAMPTZ,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
|
);
|
|
|
|
-- ─── 5. Transcription Jobs and Segments ──────────────────────────────────────
|
|
|
|
CREATE TABLE IF NOT EXISTS edge_transcription_jobs (
|
|
transcription_job_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
tenant_id TEXT NOT NULL,
|
|
event_id UUID NOT NULL REFERENCES edge_communication_events(event_id) ON DELETE CASCADE,
|
|
media_type TEXT NOT NULL CHECK (media_type IN ('audio','video')),
|
|
status TEXT NOT NULL DEFAULT 'pending'
|
|
CHECK (status IN ('pending','queued','processing','completed','failed')),
|
|
transcript_ref TEXT, -- storage path to diarized JSON
|
|
provider TEXT NOT NULL DEFAULT 'nemoclaw',
|
|
consent_state TEXT NOT NULL DEFAULT 'unknown'
|
|
CHECK (consent_state IN ('unknown','granted','denied')),
|
|
speaker_count INTEGER,
|
|
word_count INTEGER,
|
|
language TEXT NOT NULL DEFAULT 'en',
|
|
error_message TEXT,
|
|
started_at TIMESTAMPTZ,
|
|
completed_at TIMESTAMPTZ,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS edge_transcript_segments (
|
|
segment_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
transcription_job_id UUID NOT NULL REFERENCES edge_transcription_jobs(transcription_job_id) ON DELETE CASCADE,
|
|
event_id UUID NOT NULL REFERENCES edge_communication_events(event_id),
|
|
speaker_label TEXT NOT NULL, -- SPEAKER_00, SPEAKER_01, etc.
|
|
start_ms INTEGER NOT NULL,
|
|
end_ms INTEGER NOT NULL,
|
|
text TEXT NOT NULL,
|
|
confidence NUMERIC(4,3) NOT NULL DEFAULT 1.0,
|
|
is_agent_turn BOOLEAN NOT NULL DEFAULT FALSE,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
|
);
|
|
|
|
-- ─── 6. User Calendar Events ─────────────────────────────────────────────────
|
|
|
|
CREATE TABLE IF NOT EXISTS user_calendar_events (
|
|
calendar_event_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
tenant_id TEXT NOT NULL,
|
|
owner_user_id TEXT NOT NULL,
|
|
lead_id TEXT,
|
|
source_event_id UUID REFERENCES edge_communication_events(event_id),
|
|
title TEXT NOT NULL,
|
|
description TEXT,
|
|
start_at TIMESTAMPTZ NOT NULL,
|
|
end_at TIMESTAMPTZ NOT NULL,
|
|
all_day BOOLEAN NOT NULL DEFAULT FALSE,
|
|
status TEXT NOT NULL DEFAULT 'confirmed'
|
|
CHECK (status IN ('tentative','confirmed','done','cancelled')),
|
|
reminder_minutes INTEGER[] NOT NULL DEFAULT '{15}'::INTEGER[],
|
|
created_by TEXT NOT NULL
|
|
CHECK (created_by IN ('user','nemoclaw_suggested','operator_import')),
|
|
is_nemoclaw_confirmed BOOLEAN NOT NULL DEFAULT FALSE,
|
|
location TEXT,
|
|
metadata JSONB NOT NULL DEFAULT '{}'::JSONB,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
|
);
|
|
|
|
-- ─── 7. Insight Recommendations ──────────────────────────────────────────────
|
|
|
|
CREATE TABLE IF NOT EXISTS insight_recommendations (
|
|
recommendation_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
tenant_id TEXT NOT NULL,
|
|
lead_id TEXT NOT NULL,
|
|
source_event_id UUID REFERENCES edge_communication_events(event_id),
|
|
recommendation_type TEXT NOT NULL
|
|
CHECK (recommendation_type IN ('follow_up_call','send_message',
|
|
'schedule_meeting','update_crm',
|
|
'update_qd_score','send_property_info',
|
|
'escalate','custom')),
|
|
summary TEXT NOT NULL,
|
|
suggested_action TEXT NOT NULL,
|
|
target_system TEXT NOT NULL
|
|
CHECK (target_system IN ('crm','calendar','qd_score','whatsapp','email','operator')),
|
|
status TEXT NOT NULL DEFAULT 'pending'
|
|
CHECK (status IN ('pending','accepted','dismissed','acted_upon')),
|
|
confidence NUMERIC(4,3) NOT NULL DEFAULT 0.8,
|
|
acted_by TEXT,
|
|
acted_at TIMESTAMPTZ,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
|
);
|
|
|
|
-- ─── 8. Admin Action Events ───────────────────────────────────────────────────
|
|
|
|
CREATE TABLE IF NOT EXISTS admin_action_events (
|
|
action_event_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
tenant_id TEXT NOT NULL,
|
|
action_id TEXT NOT NULL UNIQUE, -- idempotency key from client
|
|
action_type TEXT NOT NULL
|
|
CHECK (action_type IN (
|
|
'user_create','user_deactivate','user_role_change',
|
|
'tenant_config_update','inventory_batch_approve',
|
|
'inventory_batch_reject','template_publish','template_archive',
|
|
'synthetic_job_trigger','synthetic_job_cancel',
|
|
'system_health_check','queue_drain','debug_event_export',
|
|
'install_register','install_deregister'
|
|
)),
|
|
target_type TEXT NOT NULL,
|
|
target_id TEXT NOT NULL,
|
|
requested_by TEXT NOT NULL,
|
|
payload JSONB NOT NULL DEFAULT '{}'::JSONB,
|
|
status TEXT NOT NULL DEFAULT 'pending'
|
|
CHECK (status IN ('pending','processing','completed','failed','rejected')),
|
|
result_message TEXT,
|
|
result_artifacts JSONB NOT NULL DEFAULT '[]'::JSONB,
|
|
executed_at TIMESTAMPTZ,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
|
);
|
|
|
|
-- ─── 9. Surface Sessions (cross-surface telemetry) ───────────────────────────
|
|
|
|
CREATE TABLE IF NOT EXISTS surface_sessions (
|
|
session_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
tenant_id TEXT NOT NULL,
|
|
user_id TEXT NOT NULL,
|
|
surface_type TEXT NOT NULL
|
|
CHECK (surface_type IN ('webos','ipad','android_tablet',
|
|
'iphone_edge','android_phone_edge')),
|
|
app_version TEXT NOT NULL,
|
|
started_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
ended_at TIMESTAMPTZ,
|
|
last_active_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
screen_sequence TEXT[] NOT NULL DEFAULT '{}',
|
|
metadata JSONB NOT NULL DEFAULT '{}'::JSONB
|
|
);
|
|
|
|
-- ─── Indexes ──────────────────────────────────────────────────────────────────
|
|
|
|
-- Template taxonomy
|
|
CREATE INDEX IF NOT EXISTS idx_tmpl_chapters_tenant ON oracle_template_chapters(tenant_id, is_active);
|
|
CREATE INDEX IF NOT EXISTS idx_tmpl_subchapters_chapter ON oracle_template_subchapters(chapter_id, is_active);
|
|
CREATE INDEX IF NOT EXISTS idx_tmpl_seed_examples_template ON oracle_template_seed_examples(template_id);
|
|
CREATE INDEX IF NOT EXISTS idx_tmpl_seed_examples_chapter ON oracle_template_seed_examples(chapter_id);
|
|
|
|
-- Synthetic jobs
|
|
CREATE INDEX IF NOT EXISTS idx_synthetic_jobs_tenant ON oracle_synthetic_generation_jobs(tenant_id, status);
|
|
CREATE INDEX IF NOT EXISTS idx_synthetic_jobs_template ON oracle_synthetic_generation_jobs(template_id);
|
|
|
|
-- Inventory
|
|
CREATE INDEX IF NOT EXISTS idx_inv_batches_tenant ON inventory_import_batches(tenant_id, status);
|
|
CREATE INDEX IF NOT EXISTS idx_inv_props_tenant ON inventory_properties(tenant_id, status);
|
|
CREATE INDEX IF NOT EXISTS idx_inv_props_batch ON inventory_properties(batch_id);
|
|
CREATE INDEX IF NOT EXISTS idx_inv_media_property ON inventory_media_assets(property_id);
|
|
|
|
-- Edge communication
|
|
CREATE INDEX IF NOT EXISTS idx_edge_events_lead ON edge_communication_events(tenant_id, lead_id, timestamp DESC);
|
|
CREATE INDEX IF NOT EXISTS idx_edge_events_channel ON edge_communication_events(channel, timestamp DESC);
|
|
CREATE INDEX IF NOT EXISTS idx_edge_memory_lead ON edge_communication_memory_facts(tenant_id, lead_id, created_at DESC);
|
|
CREATE INDEX IF NOT EXISTS idx_edge_memory_event ON edge_communication_memory_facts(event_id);
|
|
|
|
-- Transcription
|
|
CREATE INDEX IF NOT EXISTS idx_transcription_jobs_event ON edge_transcription_jobs(event_id);
|
|
CREATE INDEX IF NOT EXISTS idx_transcription_jobs_status ON edge_transcription_jobs(tenant_id, status);
|
|
CREATE INDEX IF NOT EXISTS idx_transcript_segments_job ON edge_transcript_segments(transcription_job_id, start_ms);
|
|
|
|
-- Calendar
|
|
CREATE INDEX IF NOT EXISTS idx_calendar_events_owner ON user_calendar_events(tenant_id, owner_user_id, start_at);
|
|
CREATE INDEX IF NOT EXISTS idx_calendar_events_lead ON user_calendar_events(lead_id, start_at);
|
|
|
|
-- Insights
|
|
CREATE INDEX IF NOT EXISTS idx_insights_lead ON insight_recommendations(tenant_id, lead_id, created_at DESC);
|
|
CREATE INDEX IF NOT EXISTS idx_insights_status ON insight_recommendations(status, created_at DESC);
|
|
|
|
-- Admin
|
|
CREATE INDEX IF NOT EXISTS idx_admin_actions_tenant ON admin_action_events(tenant_id, created_at DESC);
|
|
CREATE INDEX IF NOT EXISTS idx_admin_actions_type ON admin_action_events(action_type, status);
|
|
|
|
-- Surface sessions
|
|
CREATE INDEX IF NOT EXISTS idx_surface_sessions_user ON surface_sessions(tenant_id, user_id, started_at DESC);
|
|
CREATE INDEX IF NOT EXISTS idx_surface_sessions_type ON surface_sessions(surface_type, started_at DESC);
|