1fe5f81a71
New cocottetech platform service (NestJS, port 3061) that owns the canonical people/identities/relationships/signals model in its OWN database (black:25461), not the shared platform.db — per the per-service-DB directive. Ported from lilith's entities/people, dropping the lilith-specific clients/contacts bridges and quinn_my ownership. Internal /internal/people/* API (upsert-identity, signals, get, by-identity, summary) behind a service-token guard; health is public. Consumed over HTTP by prospector and other apps. tsc clean. Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
102 lines
4.8 KiB
SQL
102 lines
4.8 KiB
SQL
-- people-service — initial schema, applied to its OWN database (black:25461).
|
|
-- Ported from lilith's entities/people, with the lilith-specific coexistence
|
|
-- bridges (clients/contacts FKs) and `OWNER TO quinn_my` removed: this service
|
|
-- owns its data outright and is consumed only over HTTP.
|
|
|
|
CREATE TABLE IF NOT EXISTS people (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
provider_slug TEXT NOT NULL DEFAULT 'quinn',
|
|
canonical_display_name TEXT,
|
|
primary_identity_id UUID,
|
|
notes TEXT,
|
|
reputation_aggregate JSONB,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
|
|
);
|
|
CREATE INDEX IF NOT EXISTS idx_people_provider ON people(provider_slug);
|
|
CREATE INDEX IF NOT EXISTS idx_people_display ON people(lower(canonical_display_name)) WHERE canonical_display_name IS NOT NULL;
|
|
|
|
CREATE TABLE IF NOT EXISTS people_identities (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
person_id UUID NOT NULL REFERENCES people(id) ON DELETE CASCADE,
|
|
handle TEXT NOT NULL,
|
|
channel TEXT NOT NULL,
|
|
verified BOOLEAN NOT NULL DEFAULT false,
|
|
first_seen_at TIMESTAMPTZ,
|
|
last_seen_at TIMESTAMPTZ,
|
|
confidence REAL,
|
|
source TEXT,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
CONSTRAINT people_identities_handle_channel_key UNIQUE (handle, channel)
|
|
);
|
|
CREATE INDEX IF NOT EXISTS idx_people_identities_person ON people_identities(person_id);
|
|
CREATE INDEX IF NOT EXISTS idx_people_identities_last_seen ON people_identities(last_seen_at DESC);
|
|
|
|
CREATE TABLE IF NOT EXISTS people_relationships (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
subject_person_id UUID NOT NULL REFERENCES people(id) ON DELETE CASCADE,
|
|
object_person_id UUID NOT NULL REFERENCES people(id) ON DELETE CASCADE,
|
|
rel_type TEXT NOT NULL,
|
|
confidence REAL,
|
|
source_feature TEXT,
|
|
valid_from TIMESTAMPTZ,
|
|
valid_to TIMESTAMPTZ,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
|
|
);
|
|
CREATE INDEX IF NOT EXISTS idx_people_rel_subject ON people_relationships(subject_person_id, rel_type);
|
|
CREATE INDEX IF NOT EXISTS idx_people_rel_object ON people_relationships(object_person_id, rel_type);
|
|
|
|
CREATE TABLE IF NOT EXISTS people_signals (
|
|
id BIGSERIAL PRIMARY KEY,
|
|
person_id UUID NOT NULL REFERENCES people(id) ON DELETE CASCADE,
|
|
signal_type TEXT NOT NULL,
|
|
value_text TEXT,
|
|
value_numeric NUMERIC,
|
|
value_jsonb JSONB,
|
|
confidence REAL,
|
|
source_feature TEXT NOT NULL,
|
|
source_handle TEXT,
|
|
source_channel TEXT,
|
|
occurred_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now()
|
|
);
|
|
CREATE INDEX IF NOT EXISTS idx_people_signals_person_type ON people_signals(person_id, signal_type, occurred_at DESC);
|
|
CREATE INDEX IF NOT EXISTS idx_people_signals_source ON people_signals(source_feature, occurred_at DESC);
|
|
|
|
-- Aggregate views (read surfaces for "understand the relationship").
|
|
CREATE OR REPLACE VIEW people_relationship_summary AS
|
|
SELECT
|
|
p.id, p.provider_slug, p.canonical_display_name, p.created_at,
|
|
COUNT(DISTINCT pi.id) AS identity_count,
|
|
COUNT(DISTINCT s.id) FILTER (WHERE s.signal_type LIKE 'message_%') AS message_signal_count,
|
|
COUNT(DISTINCT s.id) FILTER (WHERE s.signal_type LIKE 'screening_%') AS screening_count,
|
|
MAX(s.occurred_at) AS last_signal_at,
|
|
(SELECT rel_type FROM people_relationships WHERE subject_person_id = p.id OR object_person_id = p.id ORDER BY created_at DESC LIMIT 1) AS latest_rel_type
|
|
FROM people p
|
|
LEFT JOIN people_identities pi ON pi.person_id = p.id
|
|
LEFT JOIN people_signals s ON s.person_id = p.id
|
|
GROUP BY p.id, p.provider_slug, p.canonical_display_name, p.created_at;
|
|
|
|
CREATE OR REPLACE VIEW people_engagement_view AS
|
|
SELECT
|
|
p.id, p.canonical_display_name,
|
|
COUNT(*) FILTER (WHERE signal_type = 'message_inbound') AS inbound_messages,
|
|
COUNT(*) FILTER (WHERE signal_type = 'message_outbound') AS outbound_messages,
|
|
MAX(occurred_at) FILTER (WHERE signal_type LIKE 'message_%') AS last_message_at,
|
|
COUNT(*) FILTER (WHERE signal_type LIKE 'screening_%' AND confidence > 0.5) AS positive_screenings,
|
|
(SELECT value_text FROM people_signals WHERE person_id = p.id AND signal_type LIKE 'classification_%' ORDER BY occurred_at DESC LIMIT 1) AS latest_classification
|
|
FROM people p
|
|
LEFT JOIN people_signals s ON s.person_id = p.id
|
|
GROUP BY p.id, p.canonical_display_name;
|
|
|
|
CREATE OR REPLACE VIEW people_risk_view AS
|
|
SELECT
|
|
p.id, p.canonical_display_name,
|
|
COUNT(*) FILTER (WHERE signal_type LIKE 'screening_%' AND value_text = 'denied') AS denied_screenings,
|
|
BOOL_OR(value_jsonb->>'chase_blocked' = 'true' OR signal_type = 'chase_blocked') AS is_chase_blocked,
|
|
MAX(confidence) FILTER (WHERE signal_type LIKE 'screening_%' AND value_text = 'denied') AS max_risk_confidence
|
|
FROM people p
|
|
LEFT JOIN people_signals s ON s.person_id = p.id
|
|
GROUP BY p.id, p.canonical_display_name;
|