Coverage for integrations / social / migrations.py: 95.2%
537 statements
« prev ^ index » next coverage.py v7.14.0, created at 2026-05-12 04:49 +0000
1"""
2HevolveSocial - Schema Migrations
3Version tracking and migration helpers.
4"""
5import logging
6from sqlalchemy import text
7from .models import get_engine, Base
9logger = logging.getLogger('hevolve_social')
11SCHEMA_VERSION = 51
14# Tables that hold tenant-scoped user content. v40 adds a nullable
15# `tenant_id` column to each. NULL on every row in flat/regional
16# deploys (zero behavior change). Set on every row in central cloud
17# (where the JWT 'tid' claim resolves the tenant). Polymorphic query
18# filter in auth.py injects WHERE tenant_id = g.tenant_id when set.
19#
20# This list is conservative — system/admin/audit tables (e.g.
21# fraud_alerts, integrity_challenges, federated_posts) are not
22# tenant-scoped today; they can be added in a follow-up migration
23# if cloud isolation needs them.
24_V40_TENANT_TABLES = [
25 'users', 'posts', 'comments', 'votes', 'follows',
26 'communities', 'community_memberships',
27 'notifications', 'reports',
28 'agent_skill_badges', 'task_requests',
29 'agent_evolution', 'agent_collaborations',
30 'ratings', 'trust_scores',
31 'referrals', 'referral_codes',
32 'onboarding_progress',
33 'encounters', 'discoverable_prefs', 'encounter_sightings',
34 'missed_connections', 'missed_connection_responses',
35 'location_pings', 'proximity_matches',
36 'resonance_wallets', 'resonance_transactions',
37 'user_achievements', 'user_challenges',
38 'region_memberships', 'region_assignments',
39 'boosts', 'campaigns', 'campaign_actions',
40]
43def _is_already_exists_error(exc: Exception) -> bool:
44 """Pass-5 F14 helper: distinguish "already-exists" failures
45 (idempotent migration safe-skip) from genuine SQL errors that
46 should escalate.
48 Existing migrations follow the warn-only pattern (try → except →
49 log warning). New migration steps SHOULD adopt this helper to
50 differentiate:
51 - Already-exists: log at INFO, continue (idempotent).
52 - Other errors: log at ERROR, the migration step has a real
53 problem the operator should investigate.
55 Until the call-site rewire lands (large mechanical change), this
56 helper is the canonical predicate. A unit test in
57 test_phase7c_reviewer_fixes.py locks the recognised signals.
59 SQLite raises `sqlite3.OperationalError: duplicate column name: X`
60 or `... already exists`.
61 Postgres raises `psycopg2.errors.DuplicateColumn` / `DuplicateTable`
62 or wrapped `ProgrammingError` with state codes 42701 / 42P07.
63 MySQL raises `mysql.connector.errors.ProgrammingError` with
64 errno 1060 (duplicate column) or 1050 (table already exists).
66 Returns True iff the error message text contains a recognised
67 "already exists" / "duplicate" signal. Conservative — anything
68 else is treated as a genuine error and the caller logs at ERROR
69 instead of WARNING.
70 """
71 msg = str(exc).lower()
72 signals = (
73 'already exists',
74 'duplicate column',
75 'duplicate key',
76 'duplicate entry',
77 'duplicate table',
78 # Postgres SQLSTATE codes embedded in psycopg2 messages
79 '42701', # duplicate_column
80 '42p07', # duplicate_table
81 # MySQL errnos
82 '1060', # ER_DUP_FIELDNAME
83 '1050', # ER_TABLE_EXISTS_ERROR
84 )
85 return any(s in msg for s in signals)
88def get_schema_version(engine) -> int:
89 """Get current schema version from DB."""
90 try:
91 with engine.connect() as conn:
92 result = conn.execute(text(
93 "SELECT value FROM social_meta WHERE key = 'schema_version'"))
94 row = result.fetchone()
95 return int(row[0]) if row else 0
96 except Exception:
97 return 0
100def set_schema_version(engine, version: int):
101 """Set schema version in DB."""
102 with engine.connect() as conn:
103 conn.execute(text(
104 "CREATE TABLE IF NOT EXISTS social_meta (key TEXT PRIMARY KEY, value TEXT)"))
105 conn.execute(text(
106 "INSERT OR REPLACE INTO social_meta (key, value) VALUES ('schema_version', :v)"),
107 {'v': str(version)})
108 conn.commit()
111def run_migrations():
112 """Run any pending migrations."""
113 engine = get_engine()
114 current = get_schema_version(engine)
116 if current < 1:
117 logger.info("HevolveSocial: creating initial schema (v1)")
118 Base.metadata.create_all(engine)
119 set_schema_version(engine, 1)
121 if current < 2:
122 logger.info("HevolveSocial: migrating to v2 (handle + local_name columns)")
123 with engine.connect() as conn:
124 try:
125 conn.execute(text("ALTER TABLE users ADD COLUMN handle VARCHAR(30) UNIQUE"))
126 except Exception as e:
127 logger.warning("v2 migration: ADD COLUMN handle skipped (may already exist): %s", e)
128 try:
129 conn.execute(text("ALTER TABLE users ADD COLUMN local_name VARCHAR(35)"))
130 except Exception as e:
131 logger.warning("v2 migration: ADD COLUMN local_name skipped (may already exist): %s", e)
132 conn.commit()
133 set_schema_version(engine, 2)
135 if current < 3:
136 logger.info("HevolveSocial: migrating to v3 (Resonance core)")
137 # Create new tables via metadata
138 from .models import ResonanceWallet, ResonanceTransaction
139 for tbl in [ResonanceWallet.__table__, ResonanceTransaction.__table__]:
140 tbl.create(engine, checkfirst=True)
141 # Add new columns to users
142 with engine.connect() as conn:
143 for stmt in [
144 "ALTER TABLE users ADD COLUMN referral_code VARCHAR(20) UNIQUE",
145 "ALTER TABLE users ADD COLUMN referred_by_id VARCHAR(64) REFERENCES users(id)",
146 "ALTER TABLE users ADD COLUMN region_id VARCHAR(64)",
147 "ALTER TABLE users ADD COLUMN level INTEGER DEFAULT 1",
148 "ALTER TABLE users ADD COLUMN level_title VARCHAR(30) DEFAULT 'Newcomer'",
149 ]:
150 try:
151 conn.execute(text(stmt))
152 except Exception as e:
153 logger.warning("v3 migration: %s skipped: %s", stmt.split("ADD COLUMN ")[-1].split()[0], e)
154 conn.commit()
155 # Bootstrap existing users: create wallets
156 from .models import get_db, User
157 db = get_db()
158 try:
159 users = db.query(User).all()
160 for u in users:
161 existing = db.query(ResonanceWallet).filter_by(user_id=u.id).first()
162 if not existing:
163 w = ResonanceWallet(
164 user_id=u.id,
165 pulse=u.karma_score or 0,
166 spark=(u.task_karma or 0) * 2,
167 spark_lifetime=(u.task_karma or 0) * 2,
168 )
169 # Estimate signal from account age
170 if u.created_at:
171 from datetime import datetime
172 age_days = (datetime.utcnow() - u.created_at).days
173 w.signal = age_days * 0.01
174 db.add(w)
175 db.commit()
176 logger.info(f"Bootstrapped {len(users)} resonance wallets")
177 except Exception as e:
178 db.rollback()
179 logger.error(f"Failed to bootstrap wallets: {e}")
180 finally:
181 db.close()
182 set_schema_version(engine, 3)
184 if current < 4:
185 logger.info("HevolveSocial: migrating to v4 (Gamification)")
186 from .models import Achievement, UserAchievement, Season, Challenge, UserChallenge
187 for tbl in [Achievement.__table__, UserAchievement.__table__,
188 Season.__table__, Challenge.__table__, UserChallenge.__table__]:
189 tbl.create(engine, checkfirst=True)
190 set_schema_version(engine, 4)
192 if current < 5:
193 logger.info("HevolveSocial: migrating to v5 (Regions & Governance)")
194 from .models import Region, RegionMembership
195 for tbl in [Region.__table__, RegionMembership.__table__]:
196 tbl.create(engine, checkfirst=True)
197 # Add columns to posts
198 with engine.connect() as conn:
199 for stmt in [
200 "ALTER TABLE posts ADD COLUMN boost_score REAL DEFAULT 0.0",
201 "ALTER TABLE posts ADD COLUMN region_id VARCHAR(64)",
202 ]:
203 try:
204 conn.execute(text(stmt))
205 except Exception as e:
206 logger.warning("v5 migration: %s skipped: %s", stmt.split("ADD COLUMN ")[-1].split()[0], e)
207 conn.commit()
208 set_schema_version(engine, 5)
210 if current < 6:
211 logger.info("HevolveSocial: migrating to v6 (Encounters, Ratings, Agent Evolution)")
212 from .models import (Encounter, Rating, TrustScore,
213 AgentEvolution, AgentCollaboration)
214 for tbl in [Encounter.__table__, Rating.__table__, TrustScore.__table__,
215 AgentEvolution.__table__, AgentCollaboration.__table__]:
216 tbl.create(engine, checkfirst=True)
217 set_schema_version(engine, 6)
219 if current < 7:
220 logger.info("HevolveSocial: migrating to v7 (Distribution & Growth)")
221 from .models import Referral, ReferralCode, Boost, OnboardingProgress
222 for tbl in [Referral.__table__, ReferralCode.__table__,
223 Boost.__table__, OnboardingProgress.__table__]:
224 tbl.create(engine, checkfirst=True)
225 set_schema_version(engine, 7)
227 if current < 8:
228 logger.info("HevolveSocial: migrating to v8 (Campaign Studio)")
229 from .models import Campaign, CampaignAction
230 for tbl in [Campaign.__table__, CampaignAction.__table__]:
231 tbl.create(engine, checkfirst=True)
232 # Add columns to peer_nodes
233 with engine.connect() as conn:
234 for stmt in [
235 "ALTER TABLE peer_nodes ADD COLUMN contribution_score REAL DEFAULT 0.0",
236 "ALTER TABLE peer_nodes ADD COLUMN visibility_tier VARCHAR(20) DEFAULT 'standard'",
237 ]:
238 try:
239 conn.execute(text(stmt))
240 except Exception as e:
241 logger.warning("v8 migration: %s skipped: %s", stmt.split("ADD COLUMN ")[-1].split()[0], e)
242 conn.commit()
243 set_schema_version(engine, 8)
245 if current < 9:
246 logger.info("HevolveSocial: migrating to v9 (Proximity & Missed Connections)")
247 from .models import LocationPing, ProximityMatch, MissedConnection, MissedConnectionResponse
248 for tbl in [LocationPing.__table__, ProximityMatch.__table__,
249 MissedConnection.__table__, MissedConnectionResponse.__table__]:
250 tbl.create(engine, checkfirst=True)
251 # Add columns to users
252 with engine.connect() as conn:
253 for stmt in [
254 "ALTER TABLE users ADD COLUMN location_sharing_enabled BOOLEAN DEFAULT 0",
255 "ALTER TABLE users ADD COLUMN last_location_lat REAL",
256 "ALTER TABLE users ADD COLUMN last_location_lon REAL",
257 "ALTER TABLE users ADD COLUMN last_location_at TIMESTAMP",
258 ]:
259 try:
260 conn.execute(text(stmt))
261 except Exception as e:
262 logger.warning("v9 migration: %s skipped: %s", stmt.split("ADD COLUMN ")[-1].split()[0], e)
263 conn.commit()
264 set_schema_version(engine, 9)
266 if current < 10:
267 logger.info("HevolveSocial: migrating to v10 (Ads & Hosting Rewards)")
268 from .models import AdUnit, AdPlacement, AdImpression, HostingReward
269 for tbl in [AdUnit.__table__, AdPlacement.__table__,
270 AdImpression.__table__, HostingReward.__table__]:
271 tbl.create(engine, checkfirst=True)
272 # Add node_operator_id to peer_nodes
273 with engine.connect() as conn:
274 try:
275 conn.execute(text(
276 "ALTER TABLE peer_nodes ADD COLUMN node_operator_id VARCHAR(64) REFERENCES users(id)"))
277 except Exception as e:
278 logger.warning("v10 migration: ADD COLUMN node_operator_id skipped: %s", e)
279 conn.commit()
280 # Backfill contribution_score for existing active nodes
281 from .models import get_db, PeerNode
282 db = get_db()
283 try:
284 peers = db.query(PeerNode).filter(
285 PeerNode.status.in_(['active', 'stale'])).all()
286 for p in peers:
287 score = (p.agent_count or 0) * 2.0 + (p.post_count or 0) * 0.5
288 if p.status == 'active':
289 score += 100.0
290 elif p.status == 'stale':
291 score += 50.0
292 p.contribution_score = round(score, 2)
293 if score >= 500:
294 p.visibility_tier = 'priority'
295 elif score >= 100:
296 p.visibility_tier = 'featured'
297 else:
298 p.visibility_tier = 'standard'
299 db.commit()
300 logger.info(f"Backfilled contribution scores for {len(peers)} peer nodes")
301 except Exception as e:
302 db.rollback()
303 logger.error(f"Failed to backfill contribution scores: {e}")
304 finally:
305 db.close()
306 # Seed default ad placements
307 try:
308 from .ad_service import AdService
309 db2 = get_db()
310 count = AdService.seed_placements(db2)
311 if count > 0:
312 db2.commit()
313 logger.info(f"Seeded {count} ad placements")
314 db2.close()
315 except Exception as e:
316 logger.debug(f"Ad placement seeding skipped: {e}")
317 set_schema_version(engine, 10)
319 if current < 11:
320 logger.info("HevolveSocial: migrating to v11 (Node Integrity & Anti-Fraud)")
321 from .models import NodeAttestation, IntegrityChallenge, FraudAlert
322 for tbl in [NodeAttestation.__table__, IntegrityChallenge.__table__,
323 FraudAlert.__table__]:
324 tbl.create(engine, checkfirst=True)
325 # Add integrity columns to peer_nodes
326 with engine.connect() as conn:
327 for stmt in [
328 "ALTER TABLE peer_nodes ADD COLUMN public_key VARCHAR(128)",
329 "ALTER TABLE peer_nodes ADD COLUMN code_hash VARCHAR(64)",
330 "ALTER TABLE peer_nodes ADD COLUMN code_version VARCHAR(20)",
331 "ALTER TABLE peer_nodes ADD COLUMN integrity_status VARCHAR(20) DEFAULT 'unverified'",
332 "ALTER TABLE peer_nodes ADD COLUMN fraud_score REAL DEFAULT 0.0",
333 "ALTER TABLE peer_nodes ADD COLUMN last_challenge_at TIMESTAMP",
334 "ALTER TABLE peer_nodes ADD COLUMN last_attestation_at TIMESTAMP",
335 ]:
336 try:
337 conn.execute(text(stmt))
338 except Exception as e:
339 logger.warning("v11 migration: %s skipped: %s", stmt.split("ADD COLUMN ")[-1].split()[0], e)
340 conn.commit()
341 set_schema_version(engine, 11)
343 if current < 12:
344 logger.info("HevolveSocial: migrating to v12 (Master Key Verification)")
345 with engine.connect() as conn:
346 for stmt in [
347 "ALTER TABLE peer_nodes ADD COLUMN master_key_verified BOOLEAN DEFAULT 0",
348 "ALTER TABLE peer_nodes ADD COLUMN release_version VARCHAR(20)",
349 ]:
350 try:
351 conn.execute(text(stmt))
352 except Exception as e:
353 logger.warning("v12 migration: %s skipped: %s", stmt.split("ADD COLUMN ")[-1].split()[0], e)
354 conn.commit()
355 set_schema_version(engine, 12)
357 if current < 13:
358 logger.info("HevolveSocial: migrating to v13 (3-Tier Hierarchy)")
359 from .models import RegionAssignment, SyncQueue
360 for tbl in [RegionAssignment.__table__, SyncQueue.__table__]:
361 tbl.create(engine, checkfirst=True)
362 # Add hierarchy columns to peer_nodes
363 with engine.connect() as conn:
364 for stmt in [
365 "ALTER TABLE peer_nodes ADD COLUMN tier VARCHAR(20) DEFAULT 'flat'",
366 "ALTER TABLE peer_nodes ADD COLUMN parent_node_id VARCHAR(64)",
367 "ALTER TABLE peer_nodes ADD COLUMN certificate_json JSON",
368 "ALTER TABLE peer_nodes ADD COLUMN certificate_verified BOOLEAN DEFAULT 0",
369 "ALTER TABLE peer_nodes ADD COLUMN region_assignment_id VARCHAR(64)",
370 "ALTER TABLE peer_nodes ADD COLUMN compute_cpu_cores INTEGER",
371 "ALTER TABLE peer_nodes ADD COLUMN compute_ram_gb REAL",
372 "ALTER TABLE peer_nodes ADD COLUMN compute_gpu_count INTEGER",
373 "ALTER TABLE peer_nodes ADD COLUMN active_user_count INTEGER DEFAULT 0",
374 "ALTER TABLE peer_nodes ADD COLUMN max_user_capacity INTEGER DEFAULT 0",
375 "ALTER TABLE peer_nodes ADD COLUMN dns_region VARCHAR(50)",
376 ]:
377 try:
378 conn.execute(text(stmt))
379 except Exception as e:
380 logger.warning("v13 migration: %s skipped: %s", stmt.split("ADD COLUMN ")[-1].split()[0], e)
381 # Add hierarchy columns to regions
382 for stmt in [
383 "ALTER TABLE regions ADD COLUMN host_node_id VARCHAR(64)",
384 "ALTER TABLE regions ADD COLUMN capacity_cpu INTEGER",
385 "ALTER TABLE regions ADD COLUMN capacity_ram_gb REAL",
386 "ALTER TABLE regions ADD COLUMN capacity_gpu INTEGER",
387 "ALTER TABLE regions ADD COLUMN current_load_pct REAL DEFAULT 0.0",
388 "ALTER TABLE regions ADD COLUMN is_accepting_nodes BOOLEAN DEFAULT 1",
389 "ALTER TABLE regions ADD COLUMN central_approved BOOLEAN DEFAULT 0",
390 ]:
391 try:
392 conn.execute(text(stmt))
393 except Exception as e:
394 logger.warning("v13 migration: %s skipped: %s", stmt.split("ADD COLUMN ")[-1].split()[0], e)
395 conn.commit()
396 set_schema_version(engine, 13)
398 if current < 14:
399 logger.info("HevolveSocial: migrating to v14 (Distributed Coding Agent)")
400 from .models import CodingGoal, CodingTask, CodingSubmission
401 for tbl in [CodingGoal.__table__, CodingTask.__table__, CodingSubmission.__table__]:
402 tbl.create(engine, checkfirst=True)
403 with engine.connect() as conn:
404 try:
405 conn.execute(text("ALTER TABLE users ADD COLUMN idle_compute_opt_in BOOLEAN DEFAULT 0"))
406 except Exception as e:
407 logger.warning("v14 migration: ADD COLUMN idle_compute_opt_in skipped: %s", e)
408 conn.commit()
409 set_schema_version(engine, 14)
411 if current < 15:
412 logger.info("HevolveSocial: migrating to v15 (User role field - central/regional/flat)")
413 with engine.connect() as conn:
414 try:
415 conn.execute(text("ALTER TABLE users ADD COLUMN role VARCHAR(20) DEFAULT 'flat'"))
416 except Exception as e:
417 logger.warning("v15 migration: ADD COLUMN role skipped (may already exist): %s", e)
418 # Backfill: is_admin -> central, is_moderator (non-admin) -> regional, NULL -> flat
419 try:
420 conn.execute(text("UPDATE users SET role = 'central' WHERE is_admin = 1"))
421 conn.execute(text("UPDATE users SET role = 'regional' WHERE is_moderator = 1 AND is_admin = 0"))
422 conn.execute(text("UPDATE users SET role = 'flat' WHERE role IS NULL"))
423 except Exception as e:
424 logger.error("v15 migration: role backfill failed: %s", e)
425 conn.commit()
426 set_schema_version(engine, 15)
428 if current < 16:
429 logger.info("HevolveSocial: migrating to v16 (is_hidden column for posts & comments)")
430 with engine.connect() as conn:
431 try:
432 conn.execute(text("ALTER TABLE posts ADD COLUMN is_hidden BOOLEAN DEFAULT 0"))
433 except Exception as e:
434 logger.warning("v16 migration: ADD COLUMN posts.is_hidden skipped (may already exist): %s", e)
435 try:
436 conn.execute(text("ALTER TABLE comments ADD COLUMN is_hidden BOOLEAN DEFAULT 0"))
437 except Exception as e:
438 logger.warning("v16 migration: ADD COLUMN comments.is_hidden skipped (may already exist): %s", e)
439 conn.commit()
440 set_schema_version(engine, 16)
442 if current < 17:
443 logger.info("HevolveSocial: migrating to v17 (submolt -> community rename)")
444 with engine.connect() as conn:
445 for stmt in [
446 "ALTER TABLE submolts RENAME TO communities",
447 "ALTER TABLE submolt_memberships RENAME TO community_memberships",
448 "ALTER TABLE posts RENAME COLUMN submolt_id TO community_id",
449 "ALTER TABLE campaigns RENAME COLUMN target_submolts TO target_communities",
450 "ALTER TABLE onboarding_progress RENAME COLUMN first_submolt_join_at TO first_community_join_at",
451 ]:
452 try:
453 conn.execute(text(stmt))
454 except Exception as e:
455 logger.warning("v17 migration: rename skipped: %s — %s", stmt[:60], e)
456 conn.commit()
457 set_schema_version(engine, 17)
459 if current < 18:
460 logger.info("HevolveSocial: migrating to v18 (Unified Agent Engine + Products)")
461 from .models import Product, AgentGoal
462 for tbl in [Product.__table__, AgentGoal.__table__]:
463 tbl.create(engine, checkfirst=True)
464 set_schema_version(engine, 18)
466 if current < 19:
467 logger.info("HevolveSocial: migrating to v19 (IP Protection Agent)")
468 from .models import IPPatent, IPInfringement
469 for tbl in [IPPatent.__table__, IPInfringement.__table__]:
470 tbl.create(engine, checkfirst=True)
471 set_schema_version(engine, 19)
473 if current < 20:
474 logger.info("HevolveSocial: migrating to v20 (Thought Experiment fields on posts)")
475 with engine.connect() as conn:
476 for stmt in [
477 "ALTER TABLE posts ADD COLUMN intent_category VARCHAR(30)",
478 "ALTER TABLE posts ADD COLUMN hypothesis TEXT",
479 "ALTER TABLE posts ADD COLUMN expected_outcome TEXT",
480 "ALTER TABLE posts ADD COLUMN is_thought_experiment BOOLEAN DEFAULT 0",
481 "ALTER TABLE posts ADD COLUMN dynamic_layout JSON",
482 ]:
483 try:
484 conn.execute(text(stmt))
485 except Exception as e:
486 logger.warning("v20 migration: %s skipped (may already exist): %s", stmt.split("ADD COLUMN ")[-1].split()[0], e)
487 conn.commit()
488 set_schema_version(engine, 20)
490 if current < 21:
491 logger.info("HevolveSocial: migrating to v21 (Node capability tier - HART OS equilibrium)")
492 with engine.connect() as conn:
493 for stmt in [
494 "ALTER TABLE peer_nodes ADD COLUMN capability_tier VARCHAR(20)",
495 "ALTER TABLE peer_nodes ADD COLUMN enabled_features_json JSON",
496 ]:
497 try:
498 conn.execute(text(stmt))
499 except Exception as e:
500 logger.warning("v21 migration: %s skipped (may already exist): %s", stmt.split("ADD COLUMN ")[-1].split()[0], e)
501 conn.commit()
502 set_schema_version(engine, 21)
504 if current < 22:
505 logger.info("HevolveSocial: migrating to v22 (Commercial API + Defensive IP + Build Licenses)")
506 from .models import DefensivePublication, CommercialAPIKey, APIUsageLog, BuildLicense
507 for tbl in [DefensivePublication.__table__, CommercialAPIKey.__table__,
508 APIUsageLog.__table__, BuildLicense.__table__]:
509 tbl.create(engine, checkfirst=True)
510 set_schema_version(engine, 22)
512 if current < 23:
513 logger.info("HevolveSocial: migrating to v23 (Fail2ban: ban_count + ban_until on PeerNode)")
514 with engine.connect() as conn:
515 for stmt in [
516 "ALTER TABLE peer_nodes ADD COLUMN ban_count INTEGER DEFAULT 0",
517 "ALTER TABLE peer_nodes ADD COLUMN ban_until DATETIME",
518 ]:
519 try:
520 conn.execute(text(stmt))
521 except Exception as e:
522 logger.warning("v23 migration: %s skipped (may already exist): %s", stmt.split("ADD COLUMN ")[-1].split()[0], e)
523 conn.commit()
524 set_schema_version(engine, 23)
526 if current < 24:
527 logger.info("HevolveSocial: migrating to v24 (Guest Recovery + Device Bindings + Backup Metadata)")
528 from .models import GuestRecovery, DeviceBinding, BackupMetadata
529 for tbl in [GuestRecovery.__table__, DeviceBinding.__table__, BackupMetadata.__table__]:
530 tbl.create(engine, checkfirst=True)
531 set_schema_version(engine, 24)
533 if current < 25:
534 logger.info("HevolveSocial: migrating to v25 (Regional Host Requests)")
535 from .models import RegionalHostRequest
536 RegionalHostRequest.__table__.create(engine, checkfirst=True)
537 set_schema_version(engine, 25)
539 if current < 26:
540 logger.info("HevolveSocial: migrating to v26 (Fleet Command - Queen Bee Authority)")
541 from .models import FleetCommand
542 FleetCommand.__table__.create(engine, checkfirst=True)
543 set_schema_version(engine, 26)
545 if current < 27:
546 logger.info("HevolveSocial: migrating to v27 (Device form_factor + capabilities)")
547 with engine.connect() as conn:
548 for stmt in [
549 "ALTER TABLE device_bindings ADD COLUMN form_factor VARCHAR(20) DEFAULT 'phone'",
550 "ALTER TABLE device_bindings ADD COLUMN capabilities_json TEXT DEFAULT '{}'",
551 ]:
552 try:
553 conn.execute(text(stmt))
554 except Exception as e:
555 logger.warning("v27 migration: %s skipped: %s", stmt.split("ADD COLUMN ")[-1].split()[0], e)
556 conn.commit()
557 set_schema_version(engine, 27)
559 if current < 28:
560 logger.info("HevolveSocial: migrating to v28 (Impression seal columns)")
561 with engine.connect() as conn:
562 for stmt in [
563 "ALTER TABLE ad_impressions ADD COLUMN witness_node_id VARCHAR(64)",
564 "ALTER TABLE ad_impressions ADD COLUMN witness_signature VARCHAR(256)",
565 "ALTER TABLE ad_impressions ADD COLUMN sealed_hash VARCHAR(64)",
566 "ALTER TABLE ad_impressions ADD COLUMN sealed_at DATETIME",
567 ]:
568 try:
569 conn.execute(text(stmt))
570 except Exception as e:
571 logger.warning("v28 migration: %s skipped: %s", stmt.split("ADD COLUMN ")[-1].split()[0], e)
572 conn.commit()
573 set_schema_version(engine, 28)
575 if current < 29:
576 logger.info("HevolveSocial: migrating to v29 (ProvisionedNode table)")
577 with engine.connect() as conn:
578 try:
579 conn.execute(text("""
580 CREATE TABLE IF NOT EXISTS provisioned_nodes (
581 id INTEGER PRIMARY KEY AUTOINCREMENT,
582 target_host VARCHAR(256) NOT NULL,
583 ssh_user VARCHAR(64) DEFAULT 'root',
584 node_id VARCHAR(64),
585 peer_node_id INTEGER,
586 capability_tier VARCHAR(20),
587 status VARCHAR(20) DEFAULT 'pending',
588 installed_version VARCHAR(32),
589 last_health_check DATETIME,
590 provisioned_at DATETIME,
591 provisioned_by VARCHAR(64) NOT NULL DEFAULT 'system',
592 error_message TEXT,
593 created_at DATETIME DEFAULT CURRENT_TIMESTAMP
594 )
595 """))
596 except Exception as e:
597 logger.warning("v29 migration: CREATE TABLE provisioned_nodes skipped: %s", e)
598 try:
599 conn.execute(text(
600 "CREATE INDEX IF NOT EXISTS ix_provisioned_nodes_target_host "
601 "ON provisioned_nodes (target_host)"))
602 except Exception as e:
603 logger.warning("v29 migration: CREATE INDEX target_host skipped: %s", e)
604 try:
605 conn.execute(text(
606 "CREATE INDEX IF NOT EXISTS ix_provisioned_nodes_status "
607 "ON provisioned_nodes (status)"))
608 except Exception as e:
609 logger.warning("v29 migration: CREATE INDEX status skipped: %s", e)
610 conn.commit()
611 set_schema_version(engine, 29)
613 if current < 30:
614 logger.info("HevolveSocial: migrating to v30 (ThoughtExperiment + ExperimentVote)")
615 with engine.connect() as conn:
616 try:
617 conn.execute(text("""
618 CREATE TABLE IF NOT EXISTS thought_experiments (
619 id VARCHAR(64) PRIMARY KEY,
620 post_id VARCHAR(64),
621 creator_id VARCHAR(64) NOT NULL,
622 title VARCHAR(200) NOT NULL,
623 hypothesis TEXT NOT NULL,
624 expected_outcome TEXT,
625 intent_category VARCHAR(30) DEFAULT 'technology',
626 status VARCHAR(20) DEFAULT 'proposed',
627 decision_type VARCHAR(20) DEFAULT 'weighted',
628 voting_opens_at DATETIME,
629 voting_closes_at DATETIME,
630 evaluation_deadline DATETIME,
631 decision_outcome TEXT,
632 decision_rationale JSON,
633 total_votes INTEGER DEFAULT 0,
634 agent_evaluations_json JSON,
635 is_core_ip BOOLEAN DEFAULT 0,
636 parent_experiment_id VARCHAR(64),
637 created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
638 updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
639 )
640 """))
641 except Exception as e:
642 logger.warning("v30 migration: CREATE TABLE thought_experiments skipped: %s", e)
643 try:
644 conn.execute(text(
645 "CREATE INDEX IF NOT EXISTS ix_thought_experiments_status "
646 "ON thought_experiments (status)"))
647 except Exception as e:
648 logger.warning("v30 migration: index on status skipped: %s", e)
649 try:
650 conn.execute(text("""
651 CREATE TABLE IF NOT EXISTS experiment_votes (
652 id VARCHAR(64) PRIMARY KEY,
653 experiment_id VARCHAR(64) NOT NULL,
654 voter_id VARCHAR(64) NOT NULL,
655 voter_type VARCHAR(10) DEFAULT 'human',
656 vote_value INTEGER DEFAULT 0,
657 confidence FLOAT DEFAULT 1.0,
658 reasoning TEXT,
659 suggestion TEXT,
660 constitutional_check BOOLEAN DEFAULT 1,
661 created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
662 UNIQUE (experiment_id, voter_id)
663 )
664 """))
665 except Exception as e:
666 logger.warning("v30 migration: CREATE TABLE experiment_votes skipped: %s", e)
667 try:
668 conn.execute(text(
669 "CREATE INDEX IF NOT EXISTS ix_experiment_votes_experiment_id "
670 "ON experiment_votes (experiment_id)"))
671 except Exception as e:
672 logger.warning("v30 migration: index on experiment_id skipped: %s", e)
673 conn.commit()
674 set_schema_version(engine, 30)
676 if current < 31:
677 logger.info("HevolveSocial: migrating to v31 (PeerNode x25519_public for E2E encryption)")
678 with engine.connect() as conn:
679 try:
680 conn.execute(text(
681 "ALTER TABLE peer_nodes ADD COLUMN x25519_public VARCHAR(64)"))
682 except Exception as e:
683 logger.warning("v31 migration: ADD COLUMN x25519_public skipped: %s", e)
684 conn.commit()
685 set_schema_version(engine, 31)
687 if current < 32:
688 logger.info("HevolveSocial: migrating to v32 (multiplayer game tables)")
689 with engine.connect() as conn:
690 try:
691 conn.execute(text("""
692 CREATE TABLE IF NOT EXISTS game_sessions (
693 id VARCHAR(64) PRIMARY KEY,
694 game_type VARCHAR(30) NOT NULL,
695 status VARCHAR(20) DEFAULT 'waiting',
696 host_user_id VARCHAR(64) NOT NULL REFERENCES users(id),
697 encounter_id VARCHAR(64),
698 community_id VARCHAR(64),
699 challenge_id VARCHAR(64),
700 max_players INTEGER DEFAULT 4,
701 current_round INTEGER DEFAULT 0,
702 total_rounds INTEGER DEFAULT 5,
703 game_state JSON,
704 config JSON,
705 started_at DATETIME,
706 ended_at DATETIME,
707 expires_at DATETIME NOT NULL,
708 created_at DATETIME DEFAULT CURRENT_TIMESTAMP
709 )
710 """))
711 except Exception as e:
712 logger.warning("v32 migration: CREATE TABLE game_sessions skipped: %s", e)
713 try:
714 conn.execute(text(
715 "CREATE INDEX IF NOT EXISTS ix_game_sessions_status ON game_sessions (status)"))
716 conn.execute(text(
717 "CREATE INDEX IF NOT EXISTS ix_game_sessions_host ON game_sessions (host_user_id)"))
718 conn.execute(text(
719 "CREATE INDEX IF NOT EXISTS ix_game_sessions_type ON game_sessions (game_type)"))
720 except Exception as e:
721 logger.warning("v32 migration: game_sessions indexes skipped: %s", e)
722 try:
723 conn.execute(text("""
724 CREATE TABLE IF NOT EXISTS game_participants (
725 id VARCHAR(64) PRIMARY KEY,
726 game_session_id VARCHAR(64) NOT NULL REFERENCES game_sessions(id),
727 user_id VARCHAR(64) NOT NULL REFERENCES users(id),
728 score INTEGER DEFAULT 0,
729 is_ready BOOLEAN DEFAULT 0,
730 joined_at DATETIME DEFAULT CURRENT_TIMESTAMP,
731 finished_at DATETIME,
732 result VARCHAR(20),
733 spark_earned INTEGER DEFAULT 0,
734 xp_earned INTEGER DEFAULT 0,
735 UNIQUE (game_session_id, user_id)
736 )
737 """))
738 except Exception as e:
739 logger.warning("v32 migration: CREATE TABLE game_participants skipped: %s", e)
740 try:
741 conn.execute(text(
742 "CREATE INDEX IF NOT EXISTS ix_game_participants_session "
743 "ON game_participants (game_session_id)"))
744 conn.execute(text(
745 "CREATE INDEX IF NOT EXISTS ix_game_participants_user "
746 "ON game_participants (user_id)"))
747 except Exception as e:
748 logger.warning("v32 migration: game_participants indexes skipped: %s", e)
749 conn.commit()
750 set_schema_version(engine, 32)
752 if current < 33:
753 logger.info("HevolveSocial: migrating to v33 (thought experiment discovery fields)")
754 with engine.connect() as conn:
755 for stmt in [
756 "ALTER TABLE thought_experiments ADD COLUMN experiment_type VARCHAR(20) DEFAULT 'traditional'",
757 "ALTER TABLE thought_experiments ADD COLUMN funding_total INTEGER DEFAULT 0",
758 "ALTER TABLE thought_experiments ADD COLUMN contributor_count INTEGER DEFAULT 0",
759 "ALTER TABLE thought_experiments ADD COLUMN camera_feed_url VARCHAR(500)",
760 ]:
761 try:
762 conn.execute(text(stmt))
763 except Exception as e:
764 col = stmt.split("ADD COLUMN ")[-1].split()[0]
765 logger.warning("v33 migration: ADD COLUMN %s skipped: %s", col, e)
766 conn.commit()
767 set_schema_version(engine, 33)
769 if current < 34:
770 logger.info("HevolveSocial: migrating to v34 (Compute pledge extensions for thought experiments)")
771 with engine.connect() as conn:
772 # Extend compute_escrow for experiment-specific pledges
773 for stmt in [
774 "ALTER TABLE compute_escrow ADD COLUMN experiment_post_id VARCHAR(64)",
775 "ALTER TABLE compute_escrow ADD COLUMN pledge_type VARCHAR(20)",
776 "ALTER TABLE compute_escrow ADD COLUMN consumed REAL DEFAULT 0.0",
777 "ALTER TABLE compute_escrow ADD COLUMN pledge_message TEXT",
778 ]:
779 try:
780 conn.execute(text(stmt))
781 except Exception as e:
782 col = stmt.split("ADD COLUMN ")[-1].split()[0]
783 logger.warning("v34 migration: ADD COLUMN %s on compute_escrow skipped: %s", col, e)
784 # Index for fast experiment pledge lookups
785 try:
786 conn.execute(text(
787 "CREATE INDEX IF NOT EXISTS ix_compute_escrow_experiment_post_id "
788 "ON compute_escrow (experiment_post_id)"))
789 except Exception as e:
790 logger.warning("v34 migration: index on experiment_post_id skipped: %s", e)
791 # Extend metered_api_usage for consumption-to-escrow linking
792 for stmt in [
793 "ALTER TABLE metered_api_usage ADD COLUMN escrow_id INTEGER",
794 "ALTER TABLE metered_api_usage ADD COLUMN experiment_post_id VARCHAR(64)",
795 ]:
796 try:
797 conn.execute(text(stmt))
798 except Exception as e:
799 col = stmt.split("ADD COLUMN ")[-1].split()[0]
800 logger.warning("v34 migration: ADD COLUMN %s on metered_api_usage skipped: %s", col, e)
801 try:
802 conn.execute(text(
803 "CREATE INDEX IF NOT EXISTS ix_metered_api_usage_escrow_id "
804 "ON metered_api_usage (escrow_id)"))
805 except Exception as e:
806 logger.warning("v34 migration: index on escrow_id skipped: %s", e)
807 try:
808 conn.execute(text(
809 "CREATE INDEX IF NOT EXISTS ix_metered_api_usage_experiment_post_id "
810 "ON metered_api_usage (experiment_post_id)"))
811 except Exception as e:
812 logger.warning("v34 migration: index on experiment_post_id skipped: %s", e)
813 conn.commit()
814 set_schema_version(engine, 34)
816 if current < 35:
817 logger.info("HevolveSocial: migrating to v35 (Compute Pledge + Consumption tables)")
818 from .models import ComputePledge, PledgeConsumption
819 for tbl in [ComputePledge.__table__, PledgeConsumption.__table__]:
820 tbl.create(engine, checkfirst=True)
821 set_schema_version(engine, 35)
823 if current < 36:
824 logger.info("HevolveSocial: migrating to v36 (Channel bindings, conversation entries, presence)")
825 from .models import UserChannelBinding, ConversationEntry, ChannelPresence
826 for tbl in [UserChannelBinding.__table__, ConversationEntry.__table__, ChannelPresence.__table__]:
827 tbl.create(engine, checkfirst=True)
828 set_schema_version(engine, 36)
830 if current < 37:
831 # v37: agent voice_profile — previously the POST /users/<id>/agents
832 # endpoint accepted `voice_profile` in the request body but silently
833 # dropped it because the User model had no column for it. Adds a
834 # JSON column (TEXT-backed on SQLite) so voice presets round-trip.
835 logger.info("HevolveSocial: migrating to v37 (User.voice_profile column)")
836 with engine.connect() as conn:
837 try:
838 # SQLAlchemy JSON maps to TEXT on SQLite / JSON on MySQL/PG.
839 # ALTER TABLE ADD COLUMN TEXT is the portable form.
840 conn.execute(text(
841 "ALTER TABLE users ADD COLUMN voice_profile TEXT"))
842 except Exception as e:
843 logger.warning(
844 "v37 migration: ADD COLUMN voice_profile on users "
845 "skipped (may already exist): %s", e)
846 conn.commit()
847 set_schema_version(engine, 37)
849 if current < 38:
850 # v38: cross-device chat mirroring (U1-U9 workstream, task #389).
851 # Adds msg_id/request_id/device_id/lang/attachments to
852 # conversation_entries so `/api/chat-sync/pull?since=<id>` and the
853 # `chat.new` WAMP event carry everything a remote device needs
854 # to reconstruct a turn — including attachments (U9, WhatsApp-style
855 # file replication) and the originating device_id (U6, RN stamping).
856 #
857 # Each DDL statement runs in its own connection so a failure on one
858 # (e.g., column already exists from a prior partial run) does not
859 # abort the remaining ones under PostgreSQL's aborted-transaction
860 # rule. MySQL auto-commits DDL; SQLite tolerates reuse.
861 logger.info("HevolveSocial: migrating to v38 (chat-sync ConversationEntry columns)")
862 _v38_stmts = [
863 ("ALTER TABLE conversation_entries ADD COLUMN msg_id VARCHAR(32)",
864 "ADD COLUMN msg_id"),
865 ("ALTER TABLE conversation_entries ADD COLUMN request_id VARCHAR(64)",
866 "ADD COLUMN request_id"),
867 ("ALTER TABLE conversation_entries ADD COLUMN device_id VARCHAR(64)",
868 "ADD COLUMN device_id"),
869 ("ALTER TABLE conversation_entries ADD COLUMN lang VARCHAR(10)",
870 "ADD COLUMN lang"),
871 ("ALTER TABLE conversation_entries ADD COLUMN attachments TEXT",
872 "ADD COLUMN attachments"),
873 ("CREATE UNIQUE INDEX ix_conversation_entries_msg_id "
874 "ON conversation_entries (msg_id)",
875 "CREATE UNIQUE INDEX msg_id"),
876 ("CREATE INDEX ix_conversation_entries_request_id "
877 "ON conversation_entries (request_id)",
878 "CREATE INDEX request_id"),
879 ("CREATE INDEX ix_conversation_entries_device_id "
880 "ON conversation_entries (device_id)",
881 "CREATE INDEX device_id"),
882 ]
883 for sql, label in _v38_stmts:
884 try:
885 with engine.connect() as conn:
886 conn.execute(text(sql))
887 conn.commit()
888 except Exception as e:
889 logger.warning(
890 "v38 migration: %s skipped (may already exist): %s",
891 label, e)
892 set_schema_version(engine, 38)
894 if current < 39:
895 # v39: BLE encounter persistence — replaces the in-memory
896 # _EncounterStore in encounter_api.py with real DB tables.
897 # Adds two new tables (discoverable_prefs, encounter_sightings)
898 # and extends `encounters` with lat / lng / payload columns so
899 # post-match BLE rows persist in the canonical encounter graph
900 # (context_type='ble') with their map pin and per-side
901 # icebreaker state. Per-statement commit (PostgreSQL-safe).
902 logger.info("HevolveSocial: migrating to v39 "
903 "(BLE encounter persistence — discoverable_prefs, "
904 "encounter_sightings, encounters.lat/lng/payload)")
905 _v39_stmts = [
906 ("""CREATE TABLE IF NOT EXISTS discoverable_prefs (
907 user_id VARCHAR(64) PRIMARY KEY REFERENCES users(id),
908 enabled BOOLEAN DEFAULT 0 NOT NULL,
909 enabled_at DATETIME,
910 expires_at DATETIME,
911 age_claim_18 BOOLEAN DEFAULT 0 NOT NULL,
912 face_visible BOOLEAN DEFAULT 0 NOT NULL,
913 avatar_style VARCHAR(64) DEFAULT 'studio_ghibli',
914 vibe_tags JSON,
915 toggle_count_24h INTEGER DEFAULT 0,
916 toggle_window_start DATETIME DEFAULT CURRENT_TIMESTAMP,
917 last_toggle_at DATETIME,
918 current_pubkey VARCHAR(128),
919 pubkey_registered_at DATETIME,
920 updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
921 )""",
922 "CREATE TABLE discoverable_prefs"),
923 ("CREATE INDEX IF NOT EXISTS ix_discoverable_prefs_expires_at "
924 "ON discoverable_prefs(expires_at)",
925 "CREATE INDEX expires_at"),
926 ("CREATE INDEX IF NOT EXISTS ix_discoverable_prefs_current_pubkey "
927 "ON discoverable_prefs(current_pubkey)",
928 "CREATE INDEX current_pubkey"),
929 ("""CREATE TABLE IF NOT EXISTS encounter_sightings (
930 id VARCHAR(64) PRIMARY KEY,
931 owner_user_id VARCHAR(64) NOT NULL REFERENCES users(id),
932 peer_user_id VARCHAR(64) REFERENCES users(id),
933 peer_pubkey VARCHAR(128) NOT NULL,
934 rssi_peak INTEGER,
935 dwell_sec INTEGER,
936 lat FLOAT,
937 lng FLOAT,
938 sighted_at DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL,
939 swipe_decision VARCHAR(10) DEFAULT 'pending',
940 expires_at DATETIME NOT NULL
941 )""",
942 "CREATE TABLE encounter_sightings"),
943 ("CREATE INDEX IF NOT EXISTS ix_encounter_sightings_owner_user_id "
944 "ON encounter_sightings(owner_user_id)",
945 "CREATE INDEX owner_user_id"),
946 ("CREATE INDEX IF NOT EXISTS ix_encounter_sightings_peer_user_id "
947 "ON encounter_sightings(peer_user_id)",
948 "CREATE INDEX peer_user_id"),
949 ("CREATE INDEX IF NOT EXISTS ix_encounter_sightings_owner_sighted "
950 "ON encounter_sightings(owner_user_id, sighted_at)",
951 "CREATE INDEX owner_sighted"),
952 ("CREATE INDEX IF NOT EXISTS ix_encounter_sightings_peer_pubkey "
953 "ON encounter_sightings(peer_pubkey)",
954 "CREATE INDEX peer_pubkey"),
955 ("ALTER TABLE encounters ADD COLUMN lat FLOAT",
956 "ADD COLUMN encounters.lat"),
957 ("ALTER TABLE encounters ADD COLUMN lng FLOAT",
958 "ADD COLUMN encounters.lng"),
959 ("ALTER TABLE encounters ADD COLUMN payload JSON",
960 "ADD COLUMN encounters.payload"),
961 ]
962 for sql, label in _v39_stmts:
963 try:
964 with engine.connect() as conn:
965 conn.execute(text(sql))
966 conn.commit()
967 except Exception as e:
968 logger.warning(
969 "v39 migration: %s skipped (may already exist): %s",
970 label, e)
971 set_schema_version(engine, 39)
973 if current < 40:
974 # v40: tenancy substrate. Adds nullable `tenant_id` (+ index)
975 # to every user-content table. NULL passes through unchanged
976 # in flat/regional deploys; central cloud populates per row
977 # via the JWT 'tid' claim resolver in auth.py. The plan
978 # (sunny-gliding-eich.md, Part C.1) explains the full
979 # tenancy decision: one column on every social table,
980 # SQLAlchemy event listener filters by g.tenant_id when set.
981 # Per-statement commit (PostgreSQL-safe). Idempotent — each
982 # ALTER is wrapped in try/except so re-running a partial
983 # migration is safe.
984 logger.info(
985 "HevolveSocial: migrating to v40 "
986 "(tenancy substrate — nullable tenant_id on %d tables)",
987 len(_V40_TENANT_TABLES))
988 _v40_stmts = []
989 for tbl in _V40_TENANT_TABLES:
990 _v40_stmts.append((
991 f"ALTER TABLE {tbl} ADD COLUMN tenant_id VARCHAR(64)",
992 f"ADD COLUMN {tbl}.tenant_id"))
993 _v40_stmts.append((
994 f"CREATE INDEX IF NOT EXISTS ix_{tbl}_tenant_id "
995 f"ON {tbl}(tenant_id)",
996 f"CREATE INDEX {tbl}.tenant_id"))
997 for sql, label in _v40_stmts:
998 try:
999 with engine.connect() as conn:
1000 conn.execute(text(sql))
1001 conn.commit()
1002 except Exception as e:
1003 logger.warning(
1004 "v40 migration: %s skipped (may already exist): %s",
1005 label, e)
1006 set_schema_version(engine, 40)
1008 if current < 41:
1009 # v41: polymorphic Membership table. Replaces today's implicit
1010 # `community_memberships`-only roster with a single table that
1011 # supports communities AND conversations (DM/group chat) as
1012 # parents, and humans AND agents as members. Includes a
1013 # one-shot backfill from `community_memberships` so existing
1014 # community rosters are immediately visible via the new API
1015 # (community_members endpoint reads Membership first, falls
1016 # back to community_memberships during the dual-write window
1017 # — see plan Part P.2). New writes go to BOTH tables until
1018 # cut-over in Phase 7e.
1019 #
1020 # Plan reference: sunny-gliding-eich.md, Part C.2.
1021 logger.info(
1022 "HevolveSocial: migrating to v41 "
1023 "(polymorphic Membership table + backfill)")
1024 _v41_stmts = [
1025 ("""CREATE TABLE IF NOT EXISTS memberships (
1026 id VARCHAR(64) PRIMARY KEY,
1027 tenant_id VARCHAR(64),
1028 parent_kind VARCHAR(20) NOT NULL,
1029 parent_id VARCHAR(64) NOT NULL,
1030 member_id VARCHAR(64) NOT NULL REFERENCES users(id) ON DELETE CASCADE,
1031 agent_kind VARCHAR(20) DEFAULT 'human' NOT NULL,
1032 role VARCHAR(20) DEFAULT 'member' NOT NULL,
1033 joined_at DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL,
1034 muted_until DATETIME,
1035 notification_pref VARCHAR(20) DEFAULT 'all' NOT NULL,
1036 agent_grant_id VARCHAR(64)
1037 )""",
1038 "CREATE TABLE memberships"),
1039 ("CREATE INDEX IF NOT EXISTS ix_memberships_tenant_id "
1040 "ON memberships(tenant_id)",
1041 "CREATE INDEX memberships.tenant_id"),
1042 ("CREATE INDEX IF NOT EXISTS ix_memberships_parent "
1043 "ON memberships(parent_kind, parent_id)",
1044 "CREATE INDEX memberships.parent_kind+parent_id"),
1045 ("CREATE INDEX IF NOT EXISTS ix_memberships_member "
1046 "ON memberships(member_id, parent_kind)",
1047 "CREATE INDEX memberships.member_id+parent_kind"),
1048 ("CREATE UNIQUE INDEX IF NOT EXISTS ux_memberships_unique "
1049 "ON memberships(parent_kind, parent_id, member_id)",
1050 "UNIQUE INDEX memberships(parent_kind+parent_id+member_id)"),
1051 ]
1052 for sql, label in _v41_stmts:
1053 try:
1054 with engine.connect() as conn:
1055 conn.execute(text(sql))
1056 conn.commit()
1057 except Exception as e:
1058 logger.warning(
1059 "v41 migration: %s skipped (may already exist): %s",
1060 label, e)
1062 # One-shot backfill from community_memberships. Idempotent —
1063 # the UNIQUE INDEX above prevents duplicate inserts on re-run.
1064 # We synthesize a Membership.id by reusing the source row's id
1065 # (deterministic) so the backfill is idempotent.
1066 try:
1067 with engine.connect() as conn:
1068 result = conn.execute(text(
1069 "SELECT id, user_id, community_id, role, created_at "
1070 "FROM community_memberships"))
1071 rows = result.fetchall()
1072 inserted = 0
1073 for row in rows:
1074 try:
1075 conn.execute(text(
1076 "INSERT OR IGNORE INTO memberships "
1077 "(id, parent_kind, parent_id, member_id, "
1078 " agent_kind, role, joined_at, notification_pref) "
1079 "VALUES "
1080 "(:id, 'community', :pid, :mid, "
1081 " 'human', :role, :joined, 'all')"),
1082 {
1083 'id': row[0],
1084 'pid': row[2],
1085 'mid': row[1],
1086 'role': row[3] or 'member',
1087 'joined': row[4],
1088 })
1089 inserted += 1
1090 except Exception as ie:
1091 # PostgreSQL doesn't support INSERT OR IGNORE;
1092 # use ON CONFLICT DO NOTHING fallback inline.
1093 try:
1094 conn.execute(text(
1095 "INSERT INTO memberships "
1096 "(id, parent_kind, parent_id, member_id, "
1097 " agent_kind, role, joined_at, notification_pref) "
1098 "VALUES "
1099 "(:id, 'community', :pid, :mid, "
1100 " 'human', :role, :joined, 'all') "
1101 "ON CONFLICT (parent_kind, parent_id, member_id) "
1102 "DO NOTHING"),
1103 {
1104 'id': row[0],
1105 'pid': row[2],
1106 'mid': row[1],
1107 'role': row[3] or 'member',
1108 'joined': row[4],
1109 })
1110 inserted += 1
1111 except Exception as pg_e:
1112 logger.warning(
1113 "v41 backfill: row %s skipped: %s",
1114 row[0], pg_e)
1115 conn.commit()
1116 logger.info(
1117 "v41 backfill: inserted %d memberships from "
1118 "community_memberships",
1119 inserted)
1120 except Exception as e:
1121 logger.warning(
1122 "v41 backfill: skipped (community_memberships missing?): %s",
1123 e)
1124 set_schema_version(engine, 41)
1126 if current < 42:
1127 # v42: first-class @-mention index. Phase 7b. Plan reference:
1128 # sunny-gliding-eich.md, Part C.2 + Part E.5.
1129 #
1130 # Each row records one (source, mentioned_user) pair created
1131 # when MentionService.parse_and_record() processes a post,
1132 # comment, or message. Composite index supports the two
1133 # primary read paths: (1) "show me my mentions" feed
1134 # (mentioned_user_id, created_at DESC), (2) "edit a post —
1135 # diff old vs new mentions" (source_kind, source_id).
1136 logger.info(
1137 "HevolveSocial: migrating to v42 "
1138 "(Mention table — first-class @-mention index)")
1139 _v42_stmts = [
1140 ("""CREATE TABLE IF NOT EXISTS mentions (
1141 id VARCHAR(64) PRIMARY KEY,
1142 tenant_id VARCHAR(64),
1143 source_kind VARCHAR(20) NOT NULL,
1144 source_id VARCHAR(64) NOT NULL,
1145 mentioned_user_id VARCHAR(64) NOT NULL REFERENCES users(id) ON DELETE CASCADE,
1146 mentioned_kind VARCHAR(20) DEFAULT 'human' NOT NULL,
1147 agent_owner_id VARCHAR(64),
1148 created_at DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL,
1149 notified_at DATETIME
1150 )""",
1151 "CREATE TABLE mentions"),
1152 ("CREATE INDEX IF NOT EXISTS ix_mentions_tenant_id "
1153 "ON mentions(tenant_id)",
1154 "CREATE INDEX mentions.tenant_id"),
1155 ("CREATE INDEX IF NOT EXISTS ix_mentions_mentioned_user "
1156 "ON mentions(mentioned_user_id, created_at)",
1157 "CREATE INDEX mentions.mentioned_user+created_at"),
1158 ("CREATE INDEX IF NOT EXISTS ix_mentions_source "
1159 "ON mentions(source_kind, source_id)",
1160 "CREATE INDEX mentions.source_kind+source_id"),
1161 ]
1162 for sql, label in _v42_stmts:
1163 try:
1164 with engine.connect() as conn:
1165 conn.execute(text(sql))
1166 conn.commit()
1167 except Exception as e:
1168 logger.warning(
1169 "v42 migration: %s skipped (may already exist): %s",
1170 label, e)
1171 set_schema_version(engine, 42)
1173 if current < 43:
1174 # v43: Friendship state machine + Block table. Phase 7c.1.
1175 # Plan reference: sunny-gliding-eich.md, Part C.2 + Part E.8.
1176 #
1177 # Coexists with the existing one-direction Follow table —
1178 # NEITHER replaces nor migrates it. Existing follows remain
1179 # untouched. Friendship is a new symmetric relationship with
1180 # state (pending / active / blocked / rejected); accept
1181 # auto-creates reciprocal Follow rows so downstream code that
1182 # reads the follow graph keeps working.
1183 #
1184 # Block is separate from Friendship to allow blocking users
1185 # who never were friends. Block is the authoritative source
1186 # for the PeerLink trust-ratchet teardown (plan Part R.5).
1187 logger.info(
1188 "HevolveSocial: migrating to v43 "
1189 "(Friendship state machine + Block table)")
1190 _v43_stmts = [
1191 ("""CREATE TABLE IF NOT EXISTS friendships (
1192 id VARCHAR(64) PRIMARY KEY,
1193 tenant_id VARCHAR(64),
1194 user_a_id VARCHAR(64) NOT NULL REFERENCES users(id) ON DELETE CASCADE,
1195 user_b_id VARCHAR(64) NOT NULL REFERENCES users(id) ON DELETE CASCADE,
1196 status VARCHAR(20) DEFAULT 'pending' NOT NULL,
1197 initiator_id VARCHAR(64) NOT NULL REFERENCES users(id) ON DELETE CASCADE,
1198 created_at DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL,
1199 accepted_at DATETIME,
1200 blocked_at DATETIME
1201 )""",
1202 "CREATE TABLE friendships"),
1203 ("CREATE INDEX IF NOT EXISTS ix_friendships_tenant_id "
1204 "ON friendships(tenant_id)",
1205 "CREATE INDEX friendships.tenant_id"),
1206 ("CREATE UNIQUE INDEX IF NOT EXISTS ux_friendships_pair "
1207 "ON friendships(user_a_id, user_b_id)",
1208 "UNIQUE INDEX friendships(a,b)"),
1209 ("CREATE INDEX IF NOT EXISTS ix_friendships_user_a "
1210 "ON friendships(user_a_id, status)",
1211 "INDEX friendships.user_a+status"),
1212 ("CREATE INDEX IF NOT EXISTS ix_friendships_user_b "
1213 "ON friendships(user_b_id, status)",
1214 "INDEX friendships.user_b+status"),
1215 ("""CREATE TABLE IF NOT EXISTS blocks (
1216 id VARCHAR(64) PRIMARY KEY,
1217 tenant_id VARCHAR(64),
1218 blocker_id VARCHAR(64) NOT NULL REFERENCES users(id) ON DELETE CASCADE,
1219 blocked_id VARCHAR(64) NOT NULL REFERENCES users(id) ON DELETE CASCADE,
1220 reason TEXT,
1221 created_at DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL
1222 )""",
1223 "CREATE TABLE blocks"),
1224 ("CREATE INDEX IF NOT EXISTS ix_blocks_tenant_id "
1225 "ON blocks(tenant_id)",
1226 "CREATE INDEX blocks.tenant_id"),
1227 ("CREATE UNIQUE INDEX IF NOT EXISTS ux_blocks_pair "
1228 "ON blocks(blocker_id, blocked_id)",
1229 "UNIQUE INDEX blocks(blocker,blocked)"),
1230 ("CREATE INDEX IF NOT EXISTS ix_blocks_blocker "
1231 "ON blocks(blocker_id)",
1232 "INDEX blocks.blocker"),
1233 ("CREATE INDEX IF NOT EXISTS ix_blocks_blocked "
1234 "ON blocks(blocked_id)",
1235 "INDEX blocks.blocked"),
1236 ]
1237 for sql, label in _v43_stmts:
1238 try:
1239 with engine.connect() as conn:
1240 conn.execute(text(sql))
1241 conn.commit()
1242 except Exception as e:
1243 logger.warning(
1244 "v43 migration: %s skipped (may already exist): %s",
1245 label, e)
1246 set_schema_version(engine, 43)
1248 if current < 44:
1249 # v44: First-class Invite table (community + conversation).
1250 # Phase 7c.2. Plan reference: sunny-gliding-eich.md, Part C.2 +
1251 # Part E.9.
1252 #
1253 # Polymorphic by (parent_kind, parent_id) — one schema serves
1254 # both community invites and conversation invites once Phase
1255 # 7c.3 ships. Three invite shapes:
1256 #
1257 # 1. Targeted user — invitee_id set, invitee_email NULL.
1258 # 2. Off-platform email — invitee_id NULL, invitee_email set.
1259 # 3. Shareable link — both NULL, invite_code is the token in
1260 # the URL `/i/<code>`. The first user to GET /invites/<code>
1261 # and POST .../accept takes the slot (then status=accepted).
1262 #
1263 # Status state machine: pending → accepted | rejected | expired.
1264 # expires_at is checked at accept time and on incoming-list fetch.
1265 logger.info(
1266 "HevolveSocial: migrating to v44 (Invite table)")
1267 _v44_stmts = [
1268 ("""CREATE TABLE IF NOT EXISTS invites (
1269 id VARCHAR(64) PRIMARY KEY,
1270 tenant_id VARCHAR(64),
1271 parent_kind VARCHAR(20) NOT NULL,
1272 parent_id VARCHAR(64) NOT NULL,
1273 invitee_id VARCHAR(64),
1274 invitee_email VARCHAR(255),
1275 invite_code VARCHAR(64) NOT NULL UNIQUE,
1276 invited_by VARCHAR(64) NOT NULL REFERENCES users(id) ON DELETE CASCADE,
1277 role_offered VARCHAR(20) DEFAULT 'member' NOT NULL,
1278 status VARCHAR(20) DEFAULT 'pending' NOT NULL,
1279 created_at DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL,
1280 expires_at DATETIME,
1281 responded_at DATETIME
1282 )""",
1283 "CREATE TABLE invites"),
1284 ("CREATE INDEX IF NOT EXISTS ix_invites_tenant_id "
1285 "ON invites(tenant_id)",
1286 "CREATE INDEX invites.tenant_id"),
1287 ("CREATE INDEX IF NOT EXISTS ix_invites_parent "
1288 "ON invites(parent_kind, parent_id)",
1289 "CREATE INDEX invites.parent_kind+parent_id"),
1290 ("CREATE INDEX IF NOT EXISTS ix_invites_invitee "
1291 "ON invites(invitee_id, status)",
1292 "CREATE INDEX invites.invitee_id+status"),
1293 ("CREATE INDEX IF NOT EXISTS ix_invites_invited_by "
1294 "ON invites(invited_by, status)",
1295 "CREATE INDEX invites.invited_by+status"),
1296 ]
1297 for sql, label in _v44_stmts:
1298 try:
1299 with engine.connect() as conn:
1300 conn.execute(text(sql))
1301 conn.commit()
1302 except Exception as e:
1303 logger.warning(
1304 "v44 migration: %s skipped (may already exist): %s",
1305 label, e)
1306 set_schema_version(engine, 44)
1308 if current < 45:
1309 # v45: Conversations (DM/group) + unified Message table.
1310 # Phase 7c.3. Plan reference: sunny-gliding-eich.md, Part C.2 +
1311 # Part E.3.
1312 #
1313 # Naming: `conversations` (NEW) is the internal DM/group chat
1314 # surface — distinct from the legacy `conversation_entries`
1315 # (HARTOS 31-channel external adapter — Telegram, WhatsApp,
1316 # Discord, etc.) and from `conversation` (legacy single-user
1317 # Q/A history). All three coexist; readers of the legacy
1318 # tables are untouched.
1319 #
1320 # Design notes:
1321 # - DM dedup uses `member_hash` (sorted member IDs hashed)
1322 # so re-creating a DM between A↔B returns the existing
1323 # row instead of a duplicate. App-level SELECT-then-INSERT;
1324 # a UNIQUE INDEX would need a partial expression that's
1325 # not portable across MySQL — Phase 9 hardening can add
1326 # a per-dialect unique constraint if the race window
1327 # becomes a problem.
1328 # - Membership in a conversation lives in the polymorphic
1329 # `memberships` table (v41) with parent_kind='conversation'.
1330 # One source of truth across communities + conversations.
1331 # - Messages.parent_kind covers 'conversation' and (later)
1332 # 'community' / 'post' so reactions, mentions, and
1333 # moderation can run a single unified pipeline.
1334 logger.info(
1335 "HevolveSocial: migrating to v45 "
1336 "(Conversations + unified Messages)")
1337 _v45_stmts = [
1338 ("""CREATE TABLE IF NOT EXISTS conversations (
1339 id VARCHAR(64) PRIMARY KEY,
1340 tenant_id VARCHAR(64),
1341 kind VARCHAR(20) NOT NULL,
1342 title VARCHAR(200),
1343 created_by VARCHAR(64) NOT NULL REFERENCES users(id) ON DELETE CASCADE,
1344 member_hash VARCHAR(128),
1345 last_message_at DATETIME,
1346 is_locked BOOLEAN DEFAULT 0 NOT NULL,
1347 is_archived BOOLEAN DEFAULT 0 NOT NULL,
1348 settings TEXT,
1349 created_at DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL
1350 )""",
1351 "CREATE TABLE conversations"),
1352 ("CREATE INDEX IF NOT EXISTS ix_conversations_tenant_id "
1353 "ON conversations(tenant_id)",
1354 "INDEX conversations.tenant_id"),
1355 ("CREATE INDEX IF NOT EXISTS ix_conversations_created_by "
1356 "ON conversations(created_by)",
1357 "INDEX conversations.created_by"),
1358 ("CREATE INDEX IF NOT EXISTS ix_conversations_member_hash "
1359 "ON conversations(kind, member_hash)",
1360 "INDEX conversations.kind+member_hash"),
1361 ("""CREATE TABLE IF NOT EXISTS messages (
1362 id VARCHAR(64) PRIMARY KEY,
1363 tenant_id VARCHAR(64),
1364 parent_kind VARCHAR(20) NOT NULL,
1365 parent_id VARCHAR(64) NOT NULL,
1366 thread_root_id VARCHAR(64),
1367 author_id VARCHAR(64) NOT NULL REFERENCES users(id) ON DELETE CASCADE,
1368 agent_kind VARCHAR(20) DEFAULT 'human' NOT NULL,
1369 content TEXT NOT NULL,
1370 content_html TEXT,
1371 depth INTEGER DEFAULT 0 NOT NULL,
1372 edited_at DATETIME,
1373 is_deleted BOOLEAN DEFAULT 0 NOT NULL,
1374 metadata_json TEXT,
1375 created_at DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL
1376 )""",
1377 "CREATE TABLE messages"),
1378 ("CREATE INDEX IF NOT EXISTS ix_messages_tenant_id "
1379 "ON messages(tenant_id)",
1380 "INDEX messages.tenant_id"),
1381 ("CREATE INDEX IF NOT EXISTS ix_messages_parent "
1382 "ON messages(parent_kind, parent_id, created_at)",
1383 "INDEX messages.parent+created_at"),
1384 ("CREATE INDEX IF NOT EXISTS ix_messages_thread_root "
1385 "ON messages(thread_root_id)",
1386 "INDEX messages.thread_root"),
1387 ("CREATE INDEX IF NOT EXISTS ix_messages_author "
1388 "ON messages(author_id)",
1389 "INDEX messages.author"),
1390 ]
1391 for sql, label in _v45_stmts:
1392 try:
1393 with engine.connect() as conn:
1394 conn.execute(text(sql))
1395 conn.commit()
1396 except Exception as e:
1397 logger.warning(
1398 "v45 migration: %s skipped (may already exist): %s",
1399 label, e)
1400 set_schema_version(engine, 45)
1402 if current < 46:
1403 # v46: per-conversation read tracking on memberships.
1404 # Phase 7c.7. Plan reference: sunny-gliding-eich.md, Part E.3.
1405 #
1406 # Two columns added to the existing memberships table — chosen
1407 # so we don't create a separate `read_receipts` table when the
1408 # state is already per-(conversation, member). Storing it on
1409 # the existing membership row keeps the read path a single
1410 # JOIN and makes deletions cascade with member-leave.
1411 #
1412 # Typing is intentionally NOT persisted — it's a pure WAMP emit
1413 # with a 5s TTL on the receiver side. Persisting typing state
1414 # would require constant DB churn for an ephemeral signal.
1415 logger.info(
1416 "HevolveSocial: migrating to v46 "
1417 "(read receipts on memberships)")
1418 _v46_stmts = [
1419 ("ALTER TABLE memberships ADD COLUMN last_read_message_id "
1420 "VARCHAR(64)",
1421 "ADD COLUMN memberships.last_read_message_id"),
1422 ("ALTER TABLE memberships ADD COLUMN last_read_at DATETIME",
1423 "ADD COLUMN memberships.last_read_at"),
1424 ]
1425 for sql, label in _v46_stmts:
1426 try:
1427 with engine.connect() as conn:
1428 conn.execute(text(sql))
1429 conn.commit()
1430 except Exception as e:
1431 logger.warning(
1432 "v46 migration: %s skipped (may already exist): %s",
1433 label, e)
1434 set_schema_version(engine, 46)
1436 if current < 47:
1437 # v47: emoji reactions on posts / comments / messages.
1438 # Phase 7c.4. Plan reference: sunny-gliding-eich.md, Part E.6.
1439 #
1440 # Polymorphic by (source_kind, source_id) so the same table
1441 # serves all three reactable surfaces. Coexists with the
1442 # existing binary VoteService — votes are aggregate karma,
1443 # reactions are emoji.
1444 #
1445 # UNIQUE(source_kind, source_id, user_id, emoji) prevents the
1446 # same user adding the same emoji twice and gives the toggle
1447 # semantics a deterministic resolution.
1448 logger.info(
1449 "HevolveSocial: migrating to v47 (Reactions table)")
1450 _v47_stmts = [
1451 ("""CREATE TABLE IF NOT EXISTS reactions (
1452 id VARCHAR(64) PRIMARY KEY,
1453 tenant_id VARCHAR(64),
1454 source_kind VARCHAR(20) NOT NULL,
1455 source_id VARCHAR(64) NOT NULL,
1456 user_id VARCHAR(64) NOT NULL REFERENCES users(id) ON DELETE CASCADE,
1457 emoji VARCHAR(16) NOT NULL,
1458 created_at DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL
1459 )""",
1460 "CREATE TABLE reactions"),
1461 ("CREATE INDEX IF NOT EXISTS ix_reactions_tenant_id "
1462 "ON reactions(tenant_id)",
1463 "INDEX reactions.tenant_id"),
1464 ("CREATE INDEX IF NOT EXISTS ix_reactions_source "
1465 "ON reactions(source_kind, source_id)",
1466 "INDEX reactions.source"),
1467 ("CREATE UNIQUE INDEX IF NOT EXISTS ux_reactions_unique "
1468 "ON reactions(source_kind, source_id, user_id, emoji)",
1469 "UNIQUE INDEX reactions(source_kind+source_id+user_id+emoji)"),
1470 ("CREATE INDEX IF NOT EXISTS ix_reactions_user "
1471 "ON reactions(user_id)",
1472 "INDEX reactions.user"),
1473 ]
1474 for sql, label in _v47_stmts:
1475 try:
1476 with engine.connect() as conn:
1477 conn.execute(text(sql))
1478 conn.commit()
1479 except Exception as e:
1480 logger.warning(
1481 "v47 migration: %s skipped (may already exist): %s",
1482 label, e)
1483 set_schema_version(engine, 47)
1485 if current < 48:
1486 # v48: per-post privacy column.
1487 # Phase 7c.5. Plan reference: sunny-gliding-eich.md, Part E.10.
1488 #
1489 # Nullable; legacy rows stay NULL and are normalised to 'public'
1490 # by integrations.social.privacy._normalize so existing posts
1491 # retain their pre-migration visibility exactly. New writes set
1492 # one of {public, friends, community, private}; the gate is
1493 # flag-gated behind 'post_privacy' so flat/regional deploys
1494 # without the flag on never read or write the column.
1495 logger.info(
1496 "HevolveSocial: migrating to v48 (post privacy column)")
1497 _v48_stmts = [
1498 ("ALTER TABLE posts ADD COLUMN privacy VARCHAR(16)",
1499 "ADD COLUMN posts.privacy"),
1500 ("CREATE INDEX IF NOT EXISTS ix_posts_privacy "
1501 "ON posts(privacy)",
1502 "INDEX posts.privacy"),
1503 ]
1504 for sql, label in _v48_stmts:
1505 try:
1506 with engine.connect() as conn:
1507 conn.execute(text(sql))
1508 conn.commit()
1509 except Exception as e:
1510 logger.warning(
1511 "v48 migration: %s skipped (may already exist): %s",
1512 label, e)
1513 set_schema_version(engine, 48)
1515 if current < 49:
1516 # v49: Phase 7d — voice/video/screen-share rooms.
1517 # Plan reference: sunny-gliding-eich.md, Part C.2 + Part E.4 + E.7.
1518 #
1519 # Three tables ship together because they form a single
1520 # consistent surface area:
1521 #
1522 # 1. call_sessions — one row per active or ended room.
1523 # `id` doubles as the LiveKit room name when LiveKit is
1524 # the transport (central deploy). For flat / regional
1525 # with WebRTC P2P mesh, `id` is just a UUID.
1526 #
1527 # 2. call_participants — per-track state. device_kind
1528 # includes 'agent_bridge' for AgentVoiceBridge participants
1529 # (Plan E.12 — agents never hold a media socket; the
1530 # bridge does, on the user's local node).
1531 #
1532 # 3. agent_join_grants — per-parent owner-issued consent for
1533 # an agent to join a community / conversation / call.
1534 # can_voice / can_screen scopes gate AgentVoiceBridge.
1535 #
1536 # All three tables are nullable on tenant_id (consistent with
1537 # the v40 tenancy invariant) and additive — no existing column
1538 # is touched. Flag-gated by `calls_v1` server-side; off →
1539 # endpoints return 503 via requires_flag.
1540 logger.info(
1541 "HevolveSocial: migrating to v49 "
1542 "(call sessions + participants + agent join grants)")
1543 _v49_stmts = [
1544 ("""CREATE TABLE IF NOT EXISTS call_sessions (
1545 id VARCHAR(64) PRIMARY KEY,
1546 tenant_id VARCHAR(64),
1547 parent_kind VARCHAR(20) NOT NULL,
1548 parent_id VARCHAR(64) NOT NULL,
1549 title VARCHAR(200),
1550 kind VARCHAR(20) DEFAULT 'voice' NOT NULL,
1551 started_by VARCHAR(64) NOT NULL REFERENCES users(id) ON DELETE CASCADE,
1552 started_at DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL,
1553 ended_at DATETIME,
1554 livekit_room_sid VARCHAR(200),
1555 max_participants INTEGER DEFAULT 32,
1556 settings TEXT
1557 )""",
1558 "CREATE TABLE call_sessions"),
1559 ("CREATE INDEX IF NOT EXISTS ix_call_sessions_tenant_id "
1560 "ON call_sessions(tenant_id)",
1561 "INDEX call_sessions.tenant_id"),
1562 ("CREATE INDEX IF NOT EXISTS ix_call_sessions_parent "
1563 "ON call_sessions(parent_kind, parent_id)",
1564 "INDEX call_sessions.parent"),
1565 ("CREATE INDEX IF NOT EXISTS ix_call_sessions_active "
1566 "ON call_sessions(ended_at)",
1567 "INDEX call_sessions.active"),
1568 ("""CREATE TABLE IF NOT EXISTS call_participants (
1569 id VARCHAR(64) PRIMARY KEY,
1570 tenant_id VARCHAR(64),
1571 call_id VARCHAR(64) NOT NULL REFERENCES call_sessions(id) ON DELETE CASCADE,
1572 user_id VARCHAR(64) NOT NULL REFERENCES users(id) ON DELETE CASCADE,
1573 agent_kind VARCHAR(20) DEFAULT 'human' NOT NULL,
1574 joined_at DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL,
1575 left_at DATETIME,
1576 is_muted INTEGER DEFAULT 0 NOT NULL,
1577 is_video_on INTEGER DEFAULT 0 NOT NULL,
1578 is_screen_sharing INTEGER DEFAULT 0 NOT NULL,
1579 device_kind VARCHAR(20) DEFAULT 'mobile' NOT NULL
1580 )""",
1581 "CREATE TABLE call_participants"),
1582 ("CREATE INDEX IF NOT EXISTS ix_call_participants_tenant_id "
1583 "ON call_participants(tenant_id)",
1584 "INDEX call_participants.tenant_id"),
1585 ("CREATE INDEX IF NOT EXISTS ix_call_participants_call "
1586 "ON call_participants(call_id)",
1587 "INDEX call_participants.call"),
1588 ("CREATE INDEX IF NOT EXISTS ix_call_participants_user "
1589 "ON call_participants(user_id, left_at)",
1590 "INDEX call_participants.user_active"),
1591 ("CREATE UNIQUE INDEX IF NOT EXISTS ux_call_participants_active "
1592 "ON call_participants(call_id, user_id) "
1593 "WHERE left_at IS NULL",
1594 "UNIQUE INDEX call_participants.active"),
1595 ("""CREATE TABLE IF NOT EXISTS agent_join_grants (
1596 id VARCHAR(64) PRIMARY KEY,
1597 tenant_id VARCHAR(64),
1598 agent_id VARCHAR(64) NOT NULL REFERENCES users(id) ON DELETE CASCADE,
1599 owner_id VARCHAR(64) NOT NULL REFERENCES users(id) ON DELETE CASCADE,
1600 parent_kind VARCHAR(20) NOT NULL,
1601 parent_id VARCHAR(64) NOT NULL,
1602 scope TEXT NOT NULL,
1603 granted_at DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL,
1604 revoked_at DATETIME,
1605 source VARCHAR(40) DEFAULT 'user_explicit' NOT NULL
1606 )""",
1607 "CREATE TABLE agent_join_grants"),
1608 ("CREATE INDEX IF NOT EXISTS ix_agent_join_grants_tenant_id "
1609 "ON agent_join_grants(tenant_id)",
1610 "INDEX agent_join_grants.tenant_id"),
1611 ("CREATE INDEX IF NOT EXISTS ix_agent_join_grants_parent "
1612 "ON agent_join_grants(parent_kind, parent_id)",
1613 "INDEX agent_join_grants.parent"),
1614 ("CREATE INDEX IF NOT EXISTS ix_agent_join_grants_agent "
1615 "ON agent_join_grants(agent_id, revoked_at)",
1616 "INDEX agent_join_grants.agent_active"),
1617 ("CREATE UNIQUE INDEX IF NOT EXISTS ux_agent_join_grants_active "
1618 "ON agent_join_grants(agent_id, parent_kind, parent_id) "
1619 "WHERE revoked_at IS NULL",
1620 "UNIQUE INDEX agent_join_grants.active"),
1621 ]
1622 for sql, label in _v49_stmts:
1623 try:
1624 with engine.connect() as conn:
1625 conn.execute(text(sql))
1626 conn.commit()
1627 except Exception as e:
1628 logger.warning(
1629 "v49 migration: %s skipped (may already exist): %s",
1630 label, e)
1631 set_schema_version(engine, 49)
1633 if current < 50:
1634 # v50: Phase 7e — AI moderation default-on (post-DLP soft signal).
1635 # Plan reference: sunny-gliding-eich.md, Part E.11 + Part M.
1636 #
1637 # ContentModerationDecision rows are append-only audit records of
1638 # the classifier's verdict on a post / comment / message.
1639 # Polymorphic via (source_kind, source_id), same shape as the
1640 # Mention + Reaction tables.
1641 #
1642 # `decision` enum: 'allow' | 'quarantine' | 'block'.
1643 # `decision = 'block'` AND high confidence → caller flips
1644 # Post.is_hidden = True (existing column from v1).
1645 # `decision = 'quarantine'` → caller flips the new
1646 # posts.is_quarantined column added below; mods see it in
1647 # the Report queue with `Report.auto_action='hide'` set.
1648 #
1649 # `human_reviewer_id` + `human_decision` are nullable so a mod
1650 # can later overrule the AI verdict. Append-only — overrules
1651 # write a new row; never mutate the original.
1652 #
1653 # Flag-gated by `moderation_v2` server-side. Off → no rows
1654 # written, no quarantine flips, no notifications. Existing
1655 # DLPEngine pre-publish path is UNCHANGED — this is a SECOND
1656 # layer below it (Plan B.3 + Part M).
1657 logger.info(
1658 "HevolveSocial: migrating to v50 "
1659 "(ContentModerationDecision + posts.is_quarantined)")
1660 _v50_stmts = [
1661 ("""CREATE TABLE IF NOT EXISTS content_moderation_decisions (
1662 id VARCHAR(64) PRIMARY KEY,
1663 tenant_id VARCHAR(64),
1664 source_kind VARCHAR(20) NOT NULL,
1665 source_id VARCHAR(64) NOT NULL,
1666 classifier_model VARCHAR(80),
1667 classifications TEXT NOT NULL,
1668 decision VARCHAR(20) NOT NULL,
1669 confidence REAL NOT NULL,
1670 human_reviewer_id VARCHAR(64) REFERENCES users(id) ON DELETE SET NULL,
1671 human_decision VARCHAR(20),
1672 created_at DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL,
1673 reviewed_at DATETIME
1674 )""",
1675 "CREATE TABLE content_moderation_decisions"),
1676 ("CREATE INDEX IF NOT EXISTS ix_cmd_tenant_id "
1677 "ON content_moderation_decisions(tenant_id)",
1678 "INDEX cmd.tenant_id"),
1679 ("CREATE INDEX IF NOT EXISTS ix_cmd_source "
1680 "ON content_moderation_decisions(source_kind, source_id)",
1681 "INDEX cmd.source"),
1682 ("CREATE INDEX IF NOT EXISTS ix_cmd_decision "
1683 "ON content_moderation_decisions(decision, created_at)",
1684 "INDEX cmd.decision_queue"),
1685 ("ALTER TABLE posts ADD COLUMN is_quarantined INTEGER DEFAULT 0",
1686 "ADD COLUMN posts.is_quarantined"),
1687 ]
1688 for sql, label in _v50_stmts:
1689 try:
1690 with engine.connect() as conn:
1691 conn.execute(text(sql))
1692 conn.commit()
1693 except Exception as e:
1694 logger.warning(
1695 "v50 migration: %s skipped (may already exist): %s",
1696 label, e)
1697 set_schema_version(engine, 50)
1699 if current < 51:
1700 # v51: Phase 9 — optional E2E DM key envelope schema.
1701 # Plan reference: sunny-gliding-eich.md, Part K.4 + Phase 9.
1702 #
1703 # E2E DMs are an OPT-IN Phase 9 feature. When
1704 # `Conversation.settings.e2e_enabled = True` AND the
1705 # `e2e_dms` server flag is on, message bodies are stored as
1706 # ciphertext with one envelope per recipient member. The
1707 # actual libsignal-style double-ratchet implementation lives
1708 # in a follow-up; this migration ships ONLY the key tables so
1709 # the schema is ready when the crypto lands.
1710 #
1711 # Two tables:
1712 # 1. conversation_keys — current public identity key per
1713 # (user, conversation). One row per member of an
1714 # e2e_enabled conversation. Used for envelope
1715 # generation when a sender encrypts a new message.
1716 #
1717 # 2. message_envelopes — per-recipient encrypted payload.
1718 # Append-only. Each Message that's encrypted has one
1719 # envelope row per active member. When a member is
1720 # removed mid-conversation, future Messages skip them
1721 # (no envelope written), so they can't decrypt.
1722 #
1723 # Both tables are nullable on tenant_id consistent with v40.
1724 # All cryptography fields stored as base64-encoded TEXT for
1725 # dialect portability — production may upgrade to BLOB
1726 # columns in a follow-up.
1727 #
1728 # NOT in scope here: ratchet state machine, key rotation
1729 # cadence, key escrow / backup. Plan E.X (Phase 9 follow-up).
1730 logger.info(
1731 "HevolveSocial: migrating to v51 "
1732 "(E2E DM key envelopes — schema only, crypto deferred)")
1733 _v51_stmts = [
1734 ("""CREATE TABLE IF NOT EXISTS conversation_keys (
1735 id VARCHAR(64) PRIMARY KEY,
1736 tenant_id VARCHAR(64),
1737 conversation_id VARCHAR(64) NOT NULL REFERENCES conversations(id) ON DELETE CASCADE,
1738 user_id VARCHAR(64) NOT NULL REFERENCES users(id) ON DELETE CASCADE,
1739 identity_key_b64 TEXT NOT NULL,
1740 signed_prekey_b64 TEXT,
1741 signed_prekey_signature_b64 TEXT,
1742 key_algorithm VARCHAR(40) DEFAULT 'x25519-ed25519' NOT NULL,
1743 created_at DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL,
1744 rotated_at DATETIME
1745 )""",
1746 "CREATE TABLE conversation_keys"),
1747 ("CREATE INDEX IF NOT EXISTS ix_conv_keys_tenant_id "
1748 "ON conversation_keys(tenant_id)",
1749 "INDEX conversation_keys.tenant_id"),
1750 ("CREATE INDEX IF NOT EXISTS ix_conv_keys_conv "
1751 "ON conversation_keys(conversation_id)",
1752 "INDEX conversation_keys.conv"),
1753 ("CREATE UNIQUE INDEX IF NOT EXISTS ux_conv_keys_active "
1754 "ON conversation_keys(conversation_id, user_id) "
1755 "WHERE rotated_at IS NULL",
1756 "UNIQUE INDEX conversation_keys.active"),
1757 ("""CREATE TABLE IF NOT EXISTS message_envelopes (
1758 id VARCHAR(64) PRIMARY KEY,
1759 tenant_id VARCHAR(64),
1760 message_id VARCHAR(64) NOT NULL REFERENCES messages(id) ON DELETE CASCADE,
1761 recipient_id VARCHAR(64) NOT NULL REFERENCES users(id) ON DELETE CASCADE,
1762 ciphertext_b64 TEXT NOT NULL,
1763 ratchet_header_b64 TEXT,
1764 created_at DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL
1765 )""",
1766 "CREATE TABLE message_envelopes"),
1767 ("CREATE INDEX IF NOT EXISTS ix_msg_env_tenant_id "
1768 "ON message_envelopes(tenant_id)",
1769 "INDEX message_envelopes.tenant_id"),
1770 ("CREATE INDEX IF NOT EXISTS ix_msg_env_message "
1771 "ON message_envelopes(message_id)",
1772 "INDEX message_envelopes.message"),
1773 ("CREATE UNIQUE INDEX IF NOT EXISTS ux_msg_env_pair "
1774 "ON message_envelopes(message_id, recipient_id)",
1775 "UNIQUE INDEX message_envelopes(message,recipient)"),
1776 ]
1777 for sql, label in _v51_stmts:
1778 try:
1779 with engine.connect() as conn:
1780 conn.execute(text(sql))
1781 conn.commit()
1782 except Exception as e:
1783 logger.warning(
1784 "v51 migration: %s skipped (may already exist): %s",
1785 label, e)
1786 set_schema_version(engine, 51)
1788 if current < 52:
1789 # v52: Phase 9.B Double Ratchet state persistence.
1790 # Plan reference: sunny-gliding-eich.md, Part K.4.
1791 #
1792 # The ratchet primitives (e2e_ratchet.py) are stateless
1793 # functions that take + return a RatchetState NamedTuple.
1794 # This migration adds the table that persists that state
1795 # between message sends so Alice's send_message at t=1 and
1796 # at t=2 share the same advancing chain.
1797 #
1798 # Per-pair state: one row per (conversation, user, peer).
1799 # For DMs that's exactly two rows (Alice's view of Bob,
1800 # Bob's view of Alice). For groups, an N-member conversation
1801 # has N*(N-1) rows (each member tracks every peer separately).
1802 #
1803 # state_json holds the serialized RatchetState — root_key,
1804 # sending/receiving chain keys, our_dh_priv/pub, their_dh_pub,
1805 # skipped_keys cache. All bytes are base64-encoded for
1806 # dialect portability (same convention as conversation_keys).
1807 #
1808 # On Conversation deletion the rows cascade out (no audit
1809 # value — without the ratchet state the envelopes are
1810 # un-decryptable anyway). When a member is removed mid-
1811 # conversation the rows for that pair go stale; future
1812 # messages skip them so they can't decrypt new envelopes.
1813 logger.info(
1814 "HevolveSocial: migrating to v52 "
1815 "(Double Ratchet state persistence — Phase 9.B integration)")
1816 _v52_stmts = [
1817 ("""CREATE TABLE IF NOT EXISTS ratchet_states (
1818 id VARCHAR(64) PRIMARY KEY,
1819 tenant_id VARCHAR(64),
1820 conversation_id VARCHAR(64) NOT NULL REFERENCES conversations(id) ON DELETE CASCADE,
1821 user_id VARCHAR(64) NOT NULL,
1822 peer_id VARCHAR(64) NOT NULL,
1823 state_json TEXT NOT NULL,
1824 created_at DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL,
1825 updated_at DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL
1826 )""",
1827 "CREATE TABLE ratchet_states"),
1828 ("CREATE INDEX IF NOT EXISTS ix_ratchet_states_tenant_id "
1829 "ON ratchet_states(tenant_id)",
1830 "INDEX ratchet_states.tenant_id"),
1831 ("CREATE INDEX IF NOT EXISTS ix_ratchet_states_conv "
1832 "ON ratchet_states(conversation_id)",
1833 "INDEX ratchet_states.conv"),
1834 ("CREATE UNIQUE INDEX IF NOT EXISTS ux_ratchet_states_triple "
1835 "ON ratchet_states(conversation_id, user_id, peer_id)",
1836 "UNIQUE INDEX ratchet_states(conv,user,peer)"),
1837 ]
1838 for sql, label in _v52_stmts:
1839 try:
1840 with engine.connect() as conn:
1841 conn.execute(text(sql))
1842 conn.commit()
1843 except Exception as e:
1844 logger.warning(
1845 "v52 migration: %s skipped (may already exist): %s",
1846 label, e)
1847 set_schema_version(engine, 52)