CREATE TABLE npa.user_personalization_profile ( -- IDENTITY user_id uuid PRIMARY KEY, profile_version text NOT NULL, refreshed_at timestamptz NOT NULL, -- LAYER A: STABLE IDENTITY country text, app_language text, article_language text, pro_tier text, pro_payment_cadence text, purchase_platform text, device_platform text, pro_industry text, pro_job_title text, pro_categories text[], pro_sub_topics jsonb, pro_publishers jsonb, pro_schedule jsonb, gender text, age int, occupation text, bio text, profile_photo_uploaded bool, acquisition_cohort text, utm_subcohort text, watchlist_tickers jsonb, -- resolved through Quartr cognitive_archetype jsonb, -- {primary, secondary, tertiary, axis_scores, evidence_count, confidence} reddit_connected bool DEFAULT false, reddit_subreddits_subscribed jsonb, reddit_topical_interests jsonb, reddit_engagement_archetype text, x_connected bool DEFAULT false, x_following_handles text[], x_following_topical_clusters jsonb, cluster_id text, -- ~21 cohorts cover 97% of paying base -- LAYER B: ACTIVE INTENT top_topics jsonb, -- [{topic, confidence, source, languages, evidence_count, last_event}] top_publishers_by_topic jsonb, top_categories jsonb, per_topic_language_affinity jsonb, reaction_summary jsonb, cross_source_compare_events jsonb, chat_topic_vector vector(3072), -- pgvector, text-embedding-3-large chat_volume_30d int, chat_question_examples jsonb, chat_use_mode text, chat_languages text[], chat_grounding_rate real, chat_features_used text[], chat_peak_hours_local int[], cross_modal_topics jsonb, aspirational_topics jsonb, -- declared but not revealed revealed_only_topics jsonb, -- behavioral but not declared social_only_topics jsonb, -- in social mirror, not in chat / reactions upcoming_events jsonb, -- watchlist joined to quartr_event + chat-derived events reading_dwell_summary jsonb, -- per-topic dwell, completion, repeat visits topic_dimension_preferences jsonb, -- per-topic 3-level category mix, format mix, sentiment tilt un_likes jsonb, slow_toggle_signals jsonb, abandoned_topics jsonb, push_categories_engaged jsonb, push_categories_ignored jsonb, push_optimal_send_times int[], lifecycle_stage text, lifecycle_stage_changed_at timestamptz, lifecycle_stage_history jsonb, feature_first_use jsonb, feature_last_use jsonb, -- COMPRESSED REPRESENTATION prompt_text_summary text, -- pre-rendered ~500-token natural-language summary prompt_text_summary_built_at timestamptz, -- METADATA + GATING signal_density_score real, cluster_blend_weight real, -- 0..1 mix of cluster vs self at read time cluster_profile_version text, data_freshness jsonb, data_provenance jsonb, user_overridden_fields jsonb, -- PRIVACY / OPT-OUT (EU "right to object") personalization_opt_out bool DEFAULT false, personalization_opt_out_surfaces text[], social_consent_at timestamptz, social_consent_scope text[], social_signal_provenance jsonb ); -- Vector + lifecycle + cohort + density indexes CREATE INDEX ON npa.user_personalization_profile USING ivfflat (chat_topic_vector vector_cosine_ops); CREATE INDEX ON npa.user_personalization_profile (lifecycle_stage, lifecycle_stage_changed_at); CREATE INDEX ON npa.user_personalization_profile (acquisition_cohort); CREATE INDEX ON npa.user_personalization_profile (signal_density_score); CREATE INDEX ON npa.user_personalization_profile (cluster_id);