CREATE TABLE "security_sessions" (
acct_gate text NOT NULL,
session_telemetry jsonb NULL,
    PRIMARY KEY (acct_gate),
    FOREIGN KEY (acct_gate) REFERENCES accounts(acct_ref)
);

First 3 rows:
acct_gate    session_telemetry
-----------  -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
ACC7210284   {'ip_reputation': {'tor_flag': 'Yes', 'proxy_hits': 98, 'country_count': 14, 'registration_ip': '186.221.8.216', 'ip_reputation_score': 0.729}, 'session_stats': {'session_count': 419, 'avg_session_duration_min': 2766}, 'vpn_usage_pct': '0.00%', 'device_profile': {'device_count': 20, 'ua_consistency': 0.639, 'device_mix_json': {'Mobile': 0.8966897433246256, 'Tablet': 0.3299024746948329, 'Desktop': 0.5115558690500416}, 'browser_diversity_idx': 0.016}, 'login_behavior': {'login_chronology': 'Burst', 'login_freq_per_day': 'Medium', 'location_variability': 0.311}, 'activity_pattern': {'activity_regularity': 0.313, 'activity_spread_code': "{'Morning': 0.08864600070960105, 'Afternoon': 0.7421617693224358, 'Night': 0.3718840943461962}"}}
ACC2686094   {'ip_reputation': {'tor_flag': 'Suspected', 'proxy_hits': 95, 'country_count': 8, 'registration_ip': '92.98.237.121', 'ip_reputation_score': 0.387}, 'session_stats': {'session_count': 78, 'avg_session_duration_min': 946.4}, 'vpn_usage_pct': '0.30%', 'device_profile': {'device_count': 19, 'ua_consistency': 0.655, 'device_mix_json': {'Mobile': 0.9878046413997664, 'Tablet': 0.9124561678632256, 'Desktop': 0.15058935932480988}, 'browser_diversity_idx': 0.435}, 'login_behavior': {'login_chronology': 'Burst', 'login_freq_per_day': 'High', 'location_variability': 0.868}, 'activity_pattern': {'activity_regularity': 0.783, 'activity_spread_code': "{'Morning': 0.08875266288036487, 'Afternoon': 0.0331719695193855, 'Night': 0.9836352647098076}"}}
ACC7106934   {'ip_reputation': {'tor_flag': 'No', 'proxy_hits': 16, 'country_count': 15, 'registration_ip': '187.186.211.81', 'ip_reputation_score': 0.576}, 'session_stats': {'session_count': 220, 'avg_session_duration_min': 544.7}, 'vpn_usage_pct': '0.20%', 'device_profile': {'device_count': 3, 'ua_consistency': 0.548, 'device_mix_json': {'Mobile': 0.022321611427355448, 'Tablet': 0.714207407707902, 'Desktop': 0.9057269667332837}, 'browser_diversity_idx': 0.063}, 'login_behavior': {'login_chronology': None, 'login_freq_per_day': 'High', 'location_variability': 0.686}, 'activity_pattern': {'activity_regularity': 0.609, 'activity_spread_code': "{'Morning': 0.027120493994065686, 'Afternoon': 0.8886273105427136, 'Night': 0.9271582067840369}"}}
...


CREATE TABLE "content_activity" (
acct_slot text NOT NULL,
content_metrics jsonb NULL,
    PRIMARY KEY (acct_slot),
    FOREIGN KEY (acct_slot) REFERENCES accounts(acct_ref)
);

First 3 rows:
acct_slot    content_metrics
-----------  ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
ACC7210284   {'posting': {'total_posts': 3713, 'posts_per_day': None, 'post_gap_variability': 0.835}, 'link_media': {'url_freq': 0.734, 'url_diversity': 0.028, 'media_reuse_rate': 0.109, 'media_upload_rate': 0.733}, 'language_tags': {'hashtag_ratio': 0.829, 'mention_ratio': '0.40%', 'language_count': 4, 'hashtag_pattern': 'Trending', 'mention_pattern': 'Normal'}, 'content_quality': {'topic_entropy': 0.859, 'content_diversity': 0.153, 'content_similarity': 0.789, 'content_uniqueness': 0.858}}
ACC2686094   {'posting': {'total_posts': 1436, 'posts_per_day': 12.2, 'post_gap_variability': 0.832}, 'link_media': {'url_freq': 0.322, 'url_diversity': 0.872, 'media_reuse_rate': 0.993, 'media_upload_rate': 0.355}, 'language_tags': {'hashtag_ratio': 0.124, 'mention_ratio': '0.50%', 'language_count': 4, 'hashtag_pattern': 'Normal', 'mention_pattern': 'Random'}, 'content_quality': {'topic_entropy': 0.699, 'content_diversity': 0.546, 'content_similarity': 0.137, 'content_uniqueness': 0.365}}
ACC7106934   {'posting': {'total_posts': 789, 'posts_per_day': 92.3, 'post_gap_variability': 0.736}, 'link_media': {'url_freq': 0.715, 'url_diversity': 0.479, 'media_reuse_rate': None, 'media_upload_rate': 0.286}, 'language_tags': {'hashtag_ratio': 0.823, 'mention_ratio': '0.70%', 'language_count': 1, 'hashtag_pattern': 'Random', 'mention_pattern': 'Normal'}, 'content_quality': {'topic_entropy': 0.341, 'content_diversity': 0.907, 'content_similarity': 0.557, 'content_uniqueness': 0.98}}
...


CREATE TABLE "platforms" (
PLT_CODE text NOT NULL,
PLT_KIND text NOT NULL,
    PRIMARY KEY (PLT_CODE)
);

First 3 rows:
PLT_CODE    PLT_KIND
----------  --------------
PL331       Microblog
PL784       Social Network
PL235       Social Network
...


CREATE TABLE "accounts" (
acct_ref text NOT NULL,
plt_key text NULL,
OrigStamp date NULL,
AGE_D bigint NULL,
StateFlag text NULL,
acct_form text NULL,
VerifyMark text NULL,
ProfileScore real NULL,
    PRIMARY KEY (acct_ref),
    FOREIGN KEY (plt_key) REFERENCES platforms(PLT_CODE)
);

First 3 rows:
acct_ref    plt_key    OrigStamp      AGE_D  StateFlag    acct_form    VerifyMark      ProfileScore
----------  ---------  -----------  -------  -----------  -----------  ------------  --------------
ACC7210284  PL331      2023-12-26       393  Active       Personal     Unverified             0.167
ACC2686094  PL784      2023-03-20       353  Deleted      Bot          Unverified             0.32
ACC7106934  PL235      2023-07-12       244  Active       Hybrid       Pending                0.963
...


CREATE TABLE "profiles" (
acct_anchor text NOT NULL,
HandleMask text NULL,
usrn_Ent real NULL,
USR_LEN bigint NULL,
UsrPtn text NULL,
DispChg bigint NULL,
pic_form text NULL,
PicScore real NULL,
BIO_L bigint NULL,
BioLang text NULL,
BioLinks bigint NULL,
BioKwHit text NULL,
LocFlag text NULL,
LOC_MOV bigint NULL,
mail_dom text NULL,
TelState text NULL,
    PRIMARY KEY (acct_anchor),
    FOREIGN KEY (acct_anchor) REFERENCES accounts(acct_ref)
);

First 3 rows:
acct_anchor    HandleMask      usrn_Ent    USR_LEN  UsrPtn        DispChg  pic_form      PicScore    BIO_L  BioLang      BioLinks  BioKwHit    LocFlag      LOC_MOV  mail_dom    TelState
-------------  ------------  ----------  ---------  ----------  ---------  ----------  ----------  -------  ---------  ----------  ----------  ---------  ---------  ----------  ----------
ACC7210284     Sequential         0.835         13  Random              8  Stock            0.772      118  en                  3  Suspicious  Fake               2  Free        Invalid
ACC2686094     Template           0.721          5  Generated         nan                   0.762       72  en                  2  Suspicious  Fake               0  Free
ACC7106934     Template           0.221         11  Meaningful          0  Stock            0.237      nan  multiple            2  Normal      No                 0  Unknown
...


CREATE TABLE "network_metrics" (
acct_node text NOT NULL,
FollowNum bigint NULL,
FollowgNum bigint NULL,
FollGrow real NULL,
FingGrow real NULL,
FollRatio real NULL,
MutConn real NULL,
ConnGrowPtn text NULL,
ConnQual real NULL,
EngRate real NULL,
EngAuth real NULL,
LikeRt real NULL,
ComRt real NULL,
ShareRt real NULL,
IntRecip real NULL,
IntDiv real NULL,
TempIntPtn text NULL,
    PRIMARY KEY (acct_node),
    FOREIGN KEY (acct_node) REFERENCES accounts(acct_ref)
);

First 3 rows:
acct_node      FollowNum    FollowgNum    FollGrow    FingGrow    FollRatio    MutConn  ConnGrowPtn      ConnQual    EngRate    EngAuth    LikeRt    ComRt    ShareRt    IntRecip    IntDiv  TempIntPtn
-----------  -----------  ------------  ----------  ----------  -----------  ---------  -------------  ----------  ---------  ---------  --------  -------  ---------  ----------  --------  ------------
ACC7210284         32353         53330       0.697       0.899        5.162      0.964  Suspicious          0.819      0.132      0.954     0.738    0.282      0.696       0.817     0.681  Natural
ACC2686094         70241         97273       0.018       0.45         7.752      0.444  Burst               0.729      0.525      0.241     0.335    0.917      0.729       0.665     0.905  Periodic
ACC7106934         47575         75481       0.621       0.89         0.887      0.241  Suspicious          0.153      0.77       0.675     0.504    0.618      0.431       0.033     0.626  Periodic
...


CREATE TABLE "interaction_metrics" (
acct_dm text NOT NULL,
MsgSim real NULL,
MsgFreq real NULL,
MsgTargetDiv real NULL,
RespTimePtn text NULL,
ConvNat real NULL,
SentVar real NULL,
LangSoph real NULL,
TxtUniq real NULL,
KeyPtnHit real NULL,
TopCoh real NULL,
    PRIMARY KEY (acct_dm),
    FOREIGN KEY (acct_dm) REFERENCES accounts(acct_ref)
);

First 3 rows:
acct_dm       MsgSim    MsgFreq    MsgTargetDiv  RespTimePtn      ConvNat    SentVar    LangSoph    TxtUniq    KeyPtnHit    TopCoh
----------  --------  ---------  --------------  -------------  ---------  ---------  ----------  ---------  -----------  --------
ACC7210284     0.041       60.1           0.498  Natural            0.825      0.005       0.03       0.44         0.589     0.856
ACC2686094     0.428       14.3           0.78   Delayed            0.359      0.974       0.949      0.62         0.488     0.686
ACC7106934     0.73        74.7           0.944  Random             0.697      0.381       0.218      0.518        0.458     0.021
...


CREATE TABLE "behavioral_scores" (
acct_beh text NOT NULL,
behavioral_anomaly_scores jsonb NULL,
    PRIMARY KEY (acct_beh),
    FOREIGN KEY (acct_beh) REFERENCES accounts(acct_ref)
);

First 3 rows:
acct_beh    behavioral_anomaly_scores
----------  ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
ACC7210284  {'pattern_scores': {'content_pattern': 0.178, 'network_pattern': 0.307, 'profile_pattern': 0.869, 'behavior_pattern': 0.087, 'temporal_pattern': 0.883, 'technical_pattern': 0.246}, 'automation_spam': {'spam_score': 0.315, 'bot_likelihood': 0.203, 'automated_behavior': 0.789}, 'commercial_intent_score': 0.558}
ACC2686094  {'pattern_scores': {'content_pattern': 0.924, 'network_pattern': 0.106, 'profile_pattern': 0.114, 'behavior_pattern': 0.993, 'temporal_pattern': None, 'technical_pattern': 0.827}, 'automation_spam': {'spam_score': 0.093, 'bot_likelihood': None, 'automated_behavior': 0.826}, 'commercial_intent_score': 0.461}
ACC7106934  {'pattern_scores': {'content_pattern': None, 'network_pattern': 0.217, 'profile_pattern': 0.412, 'behavior_pattern': 0.079, 'temporal_pattern': 0.187, 'technical_pattern': 0.618}, 'automation_spam': {'spam_score': 0.672, 'bot_likelihood': 0.204, 'automated_behavior': 0.164}, 'commercial_intent_score': 0.68}
...


CREATE TABLE "risk_and_moderation" (
acct_risk text NOT NULL,
risk_profile jsonb NULL,
    PRIMARY KEY (acct_risk),
    FOREIGN KEY (acct_risk) REFERENCES accounts(acct_ref)
);

First 3 rows:
acct_risk    risk_profile
-----------  --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
ACC7210284   {'risk_scores': {'risk_value': 0.155, 'trust_score': None, 'impact_score': 0.544, 'threat_level': 'Critical', 'reputation_score': None, 'credibility_score': 0.441, 'authenticity_score': 0.15}, 'violation_history': {'abuse_count': 24, 'appeal_count': 3, 'warning_count': 0, 'suspension_history_json': 5, 'violation_distribution_json': {'Fake': 0.4490233249279981, 'Spam': 0.9353273606557855, 'Abuse': 0.08219689664928109}}}
ACC2686094   {'risk_scores': {'risk_value': 0.969, 'trust_score': 0.767, 'impact_score': 0.431, 'threat_level': 'High', 'reputation_score': 0.609, 'credibility_score': 0.045, 'authenticity_score': 0.187}, 'violation_history': {'abuse_count': 31, 'appeal_count': 0, 'warning_count': 6, 'suspension_history_json': 5, 'violation_distribution_json': {'Fake': 0.17330889342459832, 'Spam': 0.19905311146545834, 'Abuse': 0.5063087283261145}}}
ACC7106934   {'risk_scores': {'risk_value': None, 'trust_score': 0.703, 'impact_score': 0.017, 'threat_level': 'Low', 'reputation_score': 0.741, 'credibility_score': 0.08, 'authenticity_score': None}, 'violation_history': {'abuse_count': 81, 'appeal_count': 5, 'warning_count': 5, 'suspension_history_json': 5, 'violation_distribution_json': {'Fake': 0.5419172233957493, 'Spam': 0.7767880747042554, 'Abuse': 0.27787861651690926}}}
...


CREATE TABLE "account_clusters" (
acct_bridge text NOT NULL,
clu_ref text NOT NULL,
    PRIMARY KEY (acct_bridge, clu_ref),
    FOREIGN KEY (acct_bridge) REFERENCES accounts(acct_ref),
    FOREIGN KEY (clu_ref) REFERENCES cluster_analysis(CLSTR_PIN)
);

First 3 rows:
acct_bridge    clu_ref
-------------  ---------
ACC7210284     CL0029
ACC7210284     CL0007
ACC7210284     CL0190
...


CREATE TABLE "cluster_analysis" (
CLSTR_PIN text NOT NULL,
ClusterQty bigint NULL,
CluRole text NULL,
NetInfl real NULL,
CoordScore real NULL,
    PRIMARY KEY (CLSTR_PIN)
);

First 3 rows:
CLSTR_PIN      ClusterQty  CluRole             NetInfl    CoordScore
-----------  ------------  ----------------  ---------  ------------
CL0029                  5  SocialGroup           0.362         0.363
CL0007                  8  InfluenceNetwork      0.951         0.342
CL0190                  7  Community             0.192         1
...


CREATE TABLE "monitoring" (
RecKey text NOT NULL,
snap_ts timestamp with time zone NULL,
acct_mon text NULL,
DetectSrc text NULL,
DetectConf real NULL,
MonPrio text NULL,
InvestState text NULL,
ActionDone text NULL,
RevFreq text NULL,
LastRev date NULL,
NextRev date NULL,
ConfScore real NULL,
FPP real NULL,
MethRel real NULL,
ModelVer text NULL,
FeatVer text NULL,
LastUp timestamp with time zone NULL,
UpFreqH bigint NULL,
    PRIMARY KEY (RecKey),
    FOREIGN KEY (acct_mon) REFERENCES accounts(acct_ref)
);

First 3 rows:
RecKey    snap_ts                    acct_mon    DetectSrc        DetectConf  MonPrio    InvestState    ActionDone    RevFreq    LastRev     NextRev       ConfScore    FPP    MethRel  ModelVer    FeatVer    LastUp                       UpFreqH
--------  -------------------------  ----------  -------------  ------------  ---------  -------------  ------------  ---------  ----------  ----------  -----------  -----  ---------  ----------  ---------  -------------------------  ---------
FA410087  2024-08-21 08:30:21+08:00  ACC7210284  Manual Review       nan      Low        Pending                      Monthly    2025-01-01  2025-04-10        0.253  0.692      0.927  v1.5        f1.7       2025-02-18 12:00:00+08:00          3
FA122676  2025-02-02 08:30:21+08:00  ACC2686094  User Report           0.851  Medium     Active         Suspension    Quarterly  2025-01-15  2025-05-05        0.754  0.885      0.342  v4.6        f3.2       2025-02-19 01:18:00+08:00         93
FA731882  2024-12-24 08:30:21+08:00  ACC7106934  User Report           0.978  Urgent     Active         Warning       Quarterly  2024-11-29  2025-03-28        0.275  0.906      0.279  v1.6        f5.1       2025-02-18 18:57:00+08:00         57
...
