tjkw UdZddlmZddlZddlZddlZddlmZmZddlm Z ddl m Z m Z iZ ded<iZd ed <d ZdmdZdndodZdpdqdZdrdZdsdZdtdZdud Zdd!dvd#Zd$d%d&d'd(dwd*Zdxd+Zdyd,Zdzd-Zd{d/Zd|d1Zdd2d}d6Zd~d8Z dndd9Z!ddddddddddd: ddFZ"ddGZ#dnddIZ$dddJdddKdLddTZ%dnddUZ&ddVZ'ddWZ(ddXZ)ddZZ*d[d\dd_Z+dd`Z,ddddddaddbZ-ddcZ.ddfZ/ddgZ0ddiZ1ddkZ2ddlZ3dS)zKSaaS multi-tenant schema and helpers (Postgres or SQLite via app.database).) annotationsN)datetimetimezone)Any)get_connection table_columnsz'dict[str, tuple[float, dict[str, Any]]] _AGENT_CACHEz-dict[str, tuple[float, list[dict[str, Any]]]] _DOCS_CACHEg>@returnstrcbtjtjSN)rnowrutc isoformat'/var/www/html/CA-Chatbot/app/saas/db.py_nowrs < % % / / 1 11ragent_id str | NoneNonecv|rt|ddStdSr)r popclearrs rinvalidate_agent_cachers>4(((((rorg_idc|rA|r?t|d|dt|ddnh|rM|d}ttD]2}||rt|d3nt ddlm}ddlm}|r|r||d|dn|s||||dS#t$rYdSwxYw)N:r)_JOBS_LIST_CACHE)invalidate_knowledge_cache) r rlist startswithrapp.saas.kb_answerr!app.saas.knowledge_stater" Exception)rrprefixkeyr!r"s rinvalidate_docs_cacher*sp ( 6..H..5556 d++++  $$ + +C~~f%% +T*** +   777777GGGGGG  %h %  F!7!7X!7!7 > > > > %  " " $ $ $""6844444      s-AC== D  D rowrcftt|tr|dn|dS)Nidr)r isinstancedict)r+s r_row_idr07s+ JsD11=s4yys1v > >>rct5}|d t|d}d|vrd|vr|dn#t$rYnwxYwt|d}d|vr|d|d d D]4\}}||vr+|d |d |t|d}5t|d }d|vr|dd|dD}|D]}|d|f}|r.|d|f} | p(|d|f} | r$|dt| f dddn #1swxYwYddlm } | dS)Nu -- saas_users (not "users") — avoids collision with chat-companion on shared Postgres CREATE TABLE IF NOT EXISTS saas_users ( id TEXT PRIMARY KEY, email TEXT NOT NULL UNIQUE, password_hash TEXT NOT NULL, full_name TEXT, created_at TEXT NOT NULL ); CREATE TABLE IF NOT EXISTS orgs ( id TEXT PRIMARY KEY, name TEXT NOT NULL, slug TEXT NOT NULL UNIQUE, plan TEXT NOT NULL DEFAULT 'free', kb_mode TEXT NOT NULL DEFAULT 'tenant', created_at TEXT NOT NULL ); CREATE TABLE IF NOT EXISTS memberships ( id TEXT PRIMARY KEY, user_id TEXT NOT NULL REFERENCES saas_users(id), org_id TEXT NOT NULL REFERENCES orgs(id), role TEXT NOT NULL DEFAULT 'owner', created_at TEXT NOT NULL, UNIQUE(user_id, org_id) ); CREATE TABLE IF NOT EXISTS kb_documents ( id TEXT PRIMARY KEY, org_id TEXT NOT NULL REFERENCES orgs(id), filename TEXT NOT NULL, content_type TEXT, status TEXT NOT NULL DEFAULT 'pending', chunk_count INTEGER DEFAULT 0, error TEXT, uploaded_by TEXT REFERENCES saas_users(id), created_at TEXT NOT NULL, updated_at TEXT NOT NULL ); CREATE TABLE IF NOT EXISTS api_keys ( id TEXT PRIMARY KEY, org_id TEXT NOT NULL REFERENCES orgs(id), name TEXT NOT NULL, key_prefix TEXT NOT NULL, key_hash TEXT NOT NULL, created_by TEXT REFERENCES saas_users(id), created_at TEXT NOT NULL, last_used TEXT, revoked INTEGER DEFAULT 0 ); CREATE TABLE IF NOT EXISTS agents ( id TEXT PRIMARY KEY, org_id TEXT NOT NULL REFERENCES orgs(id), name TEXT NOT NULL, description TEXT, avatar_path TEXT, kb_mode TEXT NOT NULL DEFAULT 'tenant', welcome_message TEXT, is_main INTEGER NOT NULL DEFAULT 0, created_by TEXT REFERENCES saas_users(id), created_at TEXT NOT NULL, updated_at TEXT NOT NULL ); CREATE INDEX IF NOT EXISTS idx_memberships_user ON memberships(user_id); CREATE INDEX IF NOT EXISTS idx_memberships_org ON memberships(org_id); CREATE INDEX IF NOT EXISTS idx_kb_docs_org ON kb_documents(org_id); CREATE INDEX IF NOT EXISTS idx_api_keys_org ON api_keys(org_id); CREATE INDEX IF NOT EXISTS idx_agents_org ON agents(org_id); users full_nameemaila INSERT INTO saas_users(id, email, password_hash, full_name, created_at) SELECT id, email, password_hash, full_name, created_at FROM users WHERE id NOT IN (SELECT id FROM saas_users) kb_documentsrz1ALTER TABLE kb_documents ADD COLUMN agent_id TEXTzFCREATE INDEX IF NOT EXISTS idx_kb_docs_agent ON kb_documents(agent_id))) progress_pctINTEGER DEFAULT 0)progress_stageTEXT) page_countr7)pages_processedr7)chunks_createdr7)embeddings_doner7)vectors_storedr7z$ALTER TABLE kb_documents ADD COLUMN  agentsis_mainz@ALTER TABLE agents ADD COLUMN is_main INTEGER NOT NULL DEFAULT 0c,g|]}t|Sr)r0.0rs r z$init_saas_tables..sVVV!71::VVVrzSELECT id FROM orgsz>SELECT id FROM agents WHERE org_id = ? AND is_main = 1 LIMIT 1z_SELECT id FROM agents WHERE org_id = ? AND lower(name) = 'alex' ORDER BY created_at ASC LIMIT 1zFSELECT id FROM agents WHERE org_id = ? ORDER BY created_at ASC LIMIT 19UPDATE agents SET is_main = 1, name = 'Alex' WHERE id = ?r)init_fdi_tables) r executescriptrexecuter'fetchallfetchoner0app.fdi.schemarH) connlegacycolscoldecl agent_colsorg_idsoidmainalexpickrHs rinit_saas_tablesrY;s   ET H J J J X "411Ff$$F):):     D T>22 T ! ! LLL M M M T    ; ;IC$ PCPP$PPQQQ$T>::"422 J & & LL[ \ \ \VVt||4I'J'J'S'S'U'UVVV  C<<Phjj  <<qhjj 4<<Xhjj   OT]]$! eEEEEEEEEEEEEEEEP/.....Os5G+-AG+ A!G+ A!!E=G++G/2G/r4 password_hashr3org_namedict[str, Any]c ttj}ttj}ttj}t}dd|Ddpd}|dddt jd} t5} | d|| |||f| d||| d d |f| d |||d |fdddn #1swxYwYt|d d|d dd|| |||| d d d d S)Nc3jK|].}|r|ndV/dS)-N)isalnumlower)rDcs r z'create_user_with_org..s;LLaQYY[[9 cLLLLLLrr`org(zZINSERT INTO saas_users(id, email, password_hash, full_name, created_at) VALUES (?,?,?,?,?)zPINSERT INTO orgs(id, name, slug, plan, kb_mode, created_at) VALUES (?,?,?,?,?,?)freetenantzQINSERT INTO memberships(id, user_id, org_id, role, created_at) VALUES (?,?,?,?,?)ownerAlex*Your main accounting and finance assistantlHello! I'm Alex — your accounting assistant. Ask about spend, merchants, or upload documents in Knowledge.Trname description created_bykb_modewelcome_messagerA) user_idr4r3rr[org_slugrolerrplan) r uuiduuid4rjoinstripsecrets token_hexrrJrb create_agent) r4rZr3r[rtr membership_idr slug_baseslugrNs rcreate_user_with_orgrs'$*,,G   F %%M &&CLL8LLLLLRRSVWW`[`In 5 5w033 5 5D     T h ekkmm))++]IOOEKKMM4G4G4I4IV[\\\" G (g 'y$ ' ''rct5}|d|f}|rt|ndcdddS#1swxYwYdS)Nz%SELECT * FROM saas_users WHERE id = ?rrJrLr/)rtrNr+s rget_user_by_idr/s   *TllBWJOOXXZZ)tCyyyT******************.P&&&AQ&&&rrrJrK)rtrNrowss rrrDs    'T|| J    (** '&&&& ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' '5AAArrc|dvrtdt5}|d||fddddS#1swxYwYdS)N>ricombinedplatformz-kb_mode must be platform, tenant, or combinedz(UPDATE orgs SET kb_mode = ? WHERE id = ?) ValueErrorrrJ)rrrrNs rset_org_kb_moderSs888HIII   TT ?'6ARSSSTTTTTTTTTTTTTTTTTTsAA A rfilename content_type uploaded_byc $ttj}t}t 5}|d||||||||fdddn #1swxYwYt ||t|S)Nz INSERT INTO kb_documents(id, org_id, agent_id, filename, content_type, status, chunk_count, uploaded_by, created_at, updated_at) VALUES (?,?,?,?,?, 'pending', 0, ?, ?, ?) )r rxryrrrJr*get_kb_document)rrrrrdoc_idrrNs rcreate_kb_documentrZs  F &&C    T  VXx{CQT U                   &(+++ 6 " ""sA((A,/A,rct5}|d|f}|rt|ndcdddS#1swxYwYdS)Nz'SELECT * FROM kb_documents WHERE id = ?r)rrNr+s rrrps   *TllDviPPYY[[)tCyyyT******************rc|d|pd}t|}tj}|r||dz tkr|dSt 5}|r+|d||f}n)|d|f}d|D}dddn #1swxYwY||ft|<|S)Nr r^rz SELECT * FROM kb_documents WHERE org_id = ? AND agent_id = ? ORDER BY created_at DESC z SELECT * FROM kb_documents WHERE org_id = ? ORDER BY created_at DESC c,g|]}t|SrrrCs rrFz%list_kb_documents..rr)r gettime monotonic _CACHE_TTL_SrrJrK)rr cache_keyhitrrNrdocss rlist_kb_documentsrvsK,,HN,,I //) $ $C .  C c!f ,,1v   'T  << " hjj D<<  hjj '&&&&''''''''''''''''("4[K  Ks#A#CCC status chunk_counterrorr6r8r:r;r<r=r>rr int | Nonerr6r8r:r;r<r=r>c dg} tg} ||||||||| | d } | D]4\}}|-| |d| |5| |t5}|dd| d| dddn #1swxYwYt dS)Nupdated_at = ?rz = ?zUPDATE kb_documents SET , WHERE id = ?)ritemsappendrrJrzr r)rrrrr6r8r:r;r<r=r>fieldsparamsmappingrQvalrNs rupdate_kb_documentrsU**FF"$( *(*(  GMMOOS ? MMS,,, ' ' ' MM#    MM&   ZT P &0A0APPPRXYYYZZZZZZZZZZZZZZZs .CC  C ct5}|d|fdddn #1swxYwYtdS)Nz%DELETE FROM kb_documents WHERE id = ?)rrJr r)rrNs rdelete_kb_documentrs   IT rirrri.UPDATE agents SET is_main = 0 WHERE org_id = ?z INSERT INTO agents(id, org_id, name, description, avatar_path, kb_mode, welcome_message, is_main, created_by, created_at, updated_at) VALUES (?,?,?,?,?,?,?,?,?,?,?) r^zHi, I'm z5. Ask me anything or feed me documents to learn from.rr)r rxryrrrJr{ get_agent) rrorprqrrrsrrArrrNs rr~r~sE4:<<  H &&C888    T  V LLIF9 U U U    "))++3tbadjjllaaa#!                    . X  sA?CCCc xt|}td|Dd}|ro|dpddkr.t |ddt |dp|}|Std |Dd}|rot5}|d |f|d |dfdddn #1swxYwYt |dSt|dd |d ddS)z7Guarantee Alex exists as the main chatbot for this org.c3DK|]}|d|VdS)rANrrDas rrdz$ensure_main_agent..s377qaeeI&6&67777777rNror^rWr-rk)roc3tK|]3}|dpddk/|V4dS)ror^rWN)rrbrs rrdz$ensure_main_agent..sGNNqquuV}}':&A&A&C&Cv&M&M&M&M&M&MNNrrrGrlrirmTrn) list_agentsnextrr{rb update_agentrrrJr~)rrqr@rVrWrNs rrrs  F 77F777 > >D  HHV   " ) ) + + 1 1 3 3v = = d& 1 1 1 1T$Z((0DD NNFNNNPT U UD %     LLIF9 U U U LLKd                    d$$$  @G   s5DD  D c4dt|DS)z1Non-main chatbots (legacy multi-agent leftovers).c<g|]}|d|S)rArrs rrFz%list_extra_agents..$s) C C C!!%% 2B2B CA C C Cr)rrs rlist_extra_agentsr"s C C{6** C C CCrc|t|}tj}|r||dz tkr|dSt 5}|d|f}|rt|nd}dddn #1swxYwY|r ||ft|<|S)Nrrz!SELECT * FROM agents WHERE id = ?) r rrrrrrJrLr/)rrrrNr+agents rrr's   8 $ $C .  C c!f ,,1v   +Tll> LLUUWW *S d+++++++++++++++ ."%u X Ls=B##B'*B'ct5}|d|f}d|DcdddS#1swxYwYdS)Na SELECT a.*, (SELECT COUNT(DISTINCT d.filename) FROM kb_documents d WHERE d.agent_id = a.id AND d.status = 'ready') AS ready_docs, (SELECT COALESCE(SUM(chunk_count),0) FROM kb_documents d WHERE d.agent_id = a.id AND d.status = 'ready') AS chunk_count FROM agents a WHERE a.org_id = ? ORDER BY a.is_main DESC, a.created_at ASC c,g|]}t|SrrrCs rrFzlist_agents..ArrrrrNrs rrr4s    'T|| I    (** '&&&& ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' 'rdict[str, dict[str, int]]c t5}|d|f}dddn #1swxYwYi}|D]}t|}t |dpd}|s9||ddddddd}t |dpd}t|d pd} t|d pd} ||vrd||<t||pd| z||<|d xx| z cc<|d kr|d xx| z cc<|S) zDMap agent_id -> {ready, failed, processing, pending, total, chunks}.a SELECT agent_id, status, COUNT(*) AS n, COALESCE(SUM(chunk_count), 0) AS chunks FROM kb_documents WHERE org_id = ? GROUP BY agent_id, status Nrr^rreadyfailed processingpendingtotalchunksrrnrrr)rrJrKr/r r setdefaultr) rrNroutr+daidbucketrrrs rdocument_status_countsrDs    T|| I    (**                &(C'' II!%% ##)r**   1A!a[\ ] ]  QUU8__1 22 c a QUU8__)**   F6NVZZ//41559vw1 W   8    &    Js*AA  A daysrlist[dict[str, int | str]]cddlm}mtdt t |d}||dz z }t5}| d||f }dddn #1swxYwYd|Dfd t|DS) z+Daily document uploads for the last N days.r)date timedeltarrz SELECT SUBSTR(created_at, 1, 10) AS day, COUNT(*) AS n FROM kb_documents WHERE org_id = ? AND SUBSTR(created_at, 1, 10) >= ? GROUP BY SUBSTR(created_at, 1, 10) ORDER BY day ASC Nci|]v}t|d$tt|dtt|dpdwS)dayrr)r/rr rrCs r z)document_daily_counts..|sd b b b!tTUww{{[`OaOa bc$q''%.!!3tAww{{3'7'7'<1#=#= b b brc g|]Y}|z|zddZS)rr)rcount)rr)rDiby_daystartrs rrFz)document_daily_counts..}s    ))++++66886::uW`W`fgWhWhWhOhNsNsNuNuwxCyCyzz   r) rrrmaxminrtodayrrrJrKrange) rrrcutoffrNrrrrs @@@rdocument_daily_countsrjsM(((((((( q#c$ii$$ % %D JJLL99$(333 3E __  F    T|| V     (**                c bt b b bF      t   s=+B44B8;B8cddlm}t|}t|}||}||}g}d}d}d} d} d} |D]} t | d} || ddddddd}t|dpdt|dpdz}t|dpd}t|d pd}t|d p| d pd}|| ddddd }t|d t|dz}|rtd|d z|z dnd}||z }||z }| |z } | t|dpdz } | |z } | | | dpdt| d| dpd| d| d| drd| dnd|||t|dpd|dt|d t|dt|dt|d|dd t|d t|dz}|rtd|d z|z dnd}| |d!"}t|d!#}tt|t!d$|D| ||| | t|d t|dt|dt|d|d% || |d&t|d t|dd'||d(|Dd)|Dd*|d+S),z/Aggregate workspace stats for the dashboard UI.r)databaserr-rrrrrrr)updownrwith_correctionrrgY@rNrro AssistantrArrri created_at updated_atrz /saas/agents/z/avatar)rrrrrr)rrrrsatisfaction_pct) r-rorArrrr avatar_urlrfeedbackr)rrrc3DK|]}|ddVdS)rArNrrs rrdz&build_org_dashboard..s3 K Kq!%% :J:J K K K K K K Kr) r@ custom_agents docs_total docs_ready docs_faileddocs_processingr feedback_up feedback_downfeedback_totalfeedback_correctionsr)rrr)rrcVg|]&}|d|dd|ddd'S)rorrr)rorrrrCs rrFz'build_org_dashboard..sI!!!6ai.AAfIV]L^__!!!rcVg|]&}|d|dd|ddd'S)rorrr)rorrrrCs rrFz'build_org_dashboard..sP###  fIJ--jM&1###r)knowledge_statusfeedback_splitfeedback_timeline docs_timelineagents_by_chunksagents_by_feedback)rsummarychartsr@)apprrrfeedback_counts_by_agentfeedback_rating_countsr rrroundrrfeedback_daily_countsrrlensum)rcore_dbr@ doc_counts fb_by_agentorg_fb agent_rows total_ready total_failedtotal_processing total_docs total_chunksrrrrrrrfbrated satisfaction org_ratedorg_satisfactionr(r)s rbuild_org_dashboardrDs''''''  F'//J226::K  + +6 + : :F')JKLJL , , !D'll~~ 1A!a[\ ] ]  ,//4155DHHY?eeM>R>R\:c::::X\"$", '!2!2!7a88$ bh--6 OO G--'*2.?+@'A'A(4       4F4L!!Cv$7$77IGPZuefTl2Y>CCCVZ55V!5LL)&q999Mff&kk K KF K K KKK$%'/"vd|,, 00!&/22$'/@(A$B$B 0   %.&!! &,''F6N++"3*!!#!!!## $ ###!  2S* * *r)rorprrrsrct|}|sdSdg}tg}|<|d|||<|d|||=|dvrt d|d|||<|d|||*|d||||t 5} | d d |d |dddn #1swxYwYt|t|S) Nrzname = ?zdescription = ?>rirrzinvalid kb_modez kb_mode = ?zwelcome_message = ?zavatar_path = ?zUPDATE agents SET rr) rrrr{rrrJrzr) rrorprrrsrrrrrNs rrrs  h  E t FF  j!!! djjll### '((( k''))*** < < <.// / m$$$ g" +,,, o++--... '((( k""" MM(   TT J$))F*;*;JJJFSSSTTTTTTTTTTTTTTT8$$$ X  s..F((F,/F,ct5}|d|f|d|fdddn #1swxYwYt|tdS)Nz+DELETE FROM kb_documents WHERE agent_id = ?zDELETE FROM agents WHERE id = ?)rrJrr r)rrNs r delete_agentrGs   ET BXKPPP 6 DDDEEEEEEEEEEEEEEE8$$$s/A  AA key_prefixkey_hashc Xttj}t}t 5}|d|||||||f|d|f}t|cdddS#1swxYwYdS)Nz INSERT INTO api_keys(id, org_id, name, key_prefix, key_hash, created_by, created_at, revoked) VALUES (?,?,?,?,?,?,?,0) z#SELECT * FROM api_keys WHERE id = ?)r rxryrrrJrLr/) rrorHrIrqkey_idrrNr+s rcreate_api_keyrL"s  F &&C    T  VT:xS I    ll@6)LLUUWWCyy                  sABB#&B#ct5}|d|f}d|DcdddS#1swxYwYdS)Nz SELECT id, org_id, name, key_prefix, created_at, last_used, revoked FROM api_keys WHERE org_id = ? ORDER BY created_at DESC c,g|]}t|SrrrCs rrFz!list_api_keys..Arrrrs r list_api_keysrO8s   'T|| I   (** '&&&&''''''''''''''''''rr(ct5}|d|f}|rt|ndcdddS#1swxYwYdS)Nz;SELECT * FROM api_keys WHERE key_prefix = ? AND revoked = 0r)r(rNr+s rget_api_key_by_prefixrQDs   *Tll I I   (**  )tCyyyT ******************rrKct5}|d||f}|jdkcdddS#1swxYwYdS)Nz;UPDATE api_keys SET revoked = 1 WHERE id = ? AND org_id = ?r)rrJrowcount)rKrrNcurs rrevoke_api_keyrUMs    Tll I V   |a                   s#?AAct5}|dt|fddddS#1swxYwYdS)Nz.UPDATE api_keys SET last_used = ? WHERE id = ?)rrJr)rKrNs r touch_api_keyrWVs   YT EPVGWXXXYYYYYYYYYYYYYYYYYYs%AAA)r r r)rrr r)NN)rrrrr r)r+rr r )r r) r4r rZr r3r r[r r r\)r4r r r)rtr rZr r3rr r) r4r rr r3r r[r r r\)rtr r r)rr r r)rtr rr r r)rtr r r)rr rrr r r) rr rr rrrrrrr r\)rr r r)rr rrr r)rr rrrrrrr6rr8rr:rr;rr<rr=rr>rr r)rr r r)rr rrr r)rr ror rprrqrrrr rsrrrrArr r\)rr rqrr r\)rr r r)rr r r)rr r r)rr rrr r)rr r r\)rr rorrprrrrrsrrrr r)rr r r) rr ror rHr rIr rqrr r\)r(r r r)rKr rr r r)rKr r r)4__doc__ __future__rr|rrxrrtypingr app.databaserrr __annotations__r rrrr*r0rYrrrrrrrrrrrrrrrr~rrrrrrrDrrGrLrOrQrUrWrrrr]sMQQQ"""""" ''''''''666666669; ::::=? ???? 2222     2????KKKK\1111h****W[       ##% ((((((6**** **** **** ' ' ' 'TTTT ######,**** D"#!%!"&!%"&!%$$$$$$N +++++:#!"&"&&&&&&R<DDDD     ' ' ' ' ####L782nnnnh""&"######L, ' ' ' '****    YYYYYYr