@@ -1682,6 +1682,198 @@ const subagentRecordsSelectById = Effect.gen(function* () {
16821682 assert . deepEqual ( yield * read ( [ ] ) , [ ] ) ;
16831683} ) ;
16841684
1685+ const recordsAt = ( second : number ) => DateTime . makeUnsafe ( Date . UTC ( 2026 , 0 , 1 , 0 , 0 , second ) ) ;
1686+ const recordsThreadCreated = ( threadId : ThreadId ) =>
1687+ ( {
1688+ id : EventId . make ( `event:${ threadId } :created` ) ,
1689+ type : "thread.created" ,
1690+ threadId,
1691+ providerInstanceId,
1692+ occurredAt : recordsAt ( 0 ) ,
1693+ payload : {
1694+ createdBy : "user" ,
1695+ creationSource : "web" ,
1696+ id : threadId ,
1697+ projectId : ProjectId . make ( "project:records" ) ,
1698+ title : threadId ,
1699+ providerInstanceId,
1700+ modelSelection,
1701+ runtimeMode : "full-access" ,
1702+ interactionMode : "default" ,
1703+ branch : null ,
1704+ worktreePath : null ,
1705+ activeProviderThreadId : null ,
1706+ lineage : { parentThreadId : null , relationshipToParent : null , rootThreadId : threadId } ,
1707+ forkedFrom : null ,
1708+ createdAt : recordsAt ( 0 ) ,
1709+ updatedAt : recordsAt ( 0 ) ,
1710+ archivedAt : null ,
1711+ settledOverride : null ,
1712+ settledAt : null ,
1713+ lastVisitedAt : null ,
1714+ deletedAt : null ,
1715+ } ,
1716+ } ) as never ;
1717+
1718+ const queryPlan = Effect . fn ( "queryPlan" ) ( function * ( statement : string | undefined ) {
1719+ assert . isDefined ( statement ) ;
1720+ const sql = yield * SqlClient . SqlClient ;
1721+ const rows = yield * sql . unsafe < { readonly detail : string } > ( `EXPLAIN QUERY PLAN ${ statement } ` ) ;
1722+ return rows . map ( ( row ) => row . detail ) ;
1723+ } ) ;
1724+
1725+ // Each message's first item orders it, not the stored message order (the assistant reply is stored
1726+ // first); the message-ordinal statement reads those ordinals through migration 80's index.
1727+ const messageOrdinalsReadThroughTheirIndex = Effect . gen ( function * ( ) {
1728+ const store = yield * ProjectionStore . ProjectionStoreV2 ;
1729+ const sql = yield * SqlClient . SqlClient ;
1730+ const threadId = ThreadId . make ( "thread:message-ordinals" ) ;
1731+ yield * store . apply ( recordsThreadCreated ( threadId ) ) ;
1732+ const conversation = ( name : string , firstOrdinal : number ) => {
1733+ const runId = RunId . make ( `run:${ name } ` ) ;
1734+ const nodeId = NodeId . make ( `node:${ name } :root` ) ;
1735+ const user = MessageId . make ( `message:${ name } :user` ) ;
1736+ const assistant = MessageId . make ( `message:${ name } :assistant` ) ;
1737+ const item = ( index : number ) => ( {
1738+ id : TurnItemId . make ( `item:${ name } :${ index } ` ) ,
1739+ threadId,
1740+ runId,
1741+ nodeId,
1742+ providerThreadId : null ,
1743+ providerTurnId : null ,
1744+ nativeItemRef : null ,
1745+ parentItemId : null ,
1746+ ordinal : firstOrdinal + index ,
1747+ status : "completed" ,
1748+ title : null ,
1749+ startedAt : recordsAt ( 2 ) ,
1750+ completedAt : recordsAt ( 2 ) ,
1751+ updatedAt : recordsAt ( 2 ) ,
1752+ } ) ;
1753+ const message = ( id : MessageId , role : "user" | "assistant" , second : number ) => ( {
1754+ id : EventId . make ( `event:${ id } ` ) ,
1755+ type : "message.updated" ,
1756+ threadId,
1757+ occurredAt : recordsAt ( second ) ,
1758+ payload : {
1759+ createdBy : role === "user" ? "user" : "agent" ,
1760+ creationSource : role === "user" ? "web" : "provider" ,
1761+ id,
1762+ threadId,
1763+ runId,
1764+ nodeId,
1765+ role,
1766+ text : `${ name } ${ role } ` ,
1767+ attachments : [ ] ,
1768+ streaming : false ,
1769+ createdAt : recordsAt ( second ) ,
1770+ updatedAt : recordsAt ( second ) ,
1771+ } ,
1772+ } ) ;
1773+ const turnItem = ( index : number , payload : object ) => ( {
1774+ id : EventId . make ( `event:item:${ name } :${ index } ` ) ,
1775+ type : "turn-item.updated" ,
1776+ threadId,
1777+ occurredAt : recordsAt ( 2 ) ,
1778+ payload : { ...item ( index ) , ...payload } ,
1779+ } ) ;
1780+ const events = [
1781+ message ( assistant , "assistant" , 1 ) ,
1782+ message ( user , "user" , 2 ) ,
1783+ turnItem ( 0 , {
1784+ type : "user_message" ,
1785+ createdBy : "user" ,
1786+ creationSource : "web" ,
1787+ messageId : user ,
1788+ inputIntent : "turn_start" ,
1789+ text : `${ name } user` ,
1790+ attachments : [ ] ,
1791+ } ) ,
1792+ // A message streamed over two items keeps its first ordinal.
1793+ ...[ 1 , 2 ] . map ( ( index ) =>
1794+ turnItem ( index , {
1795+ type : "assistant_message" ,
1796+ messageId : assistant ,
1797+ text : `${ name } assistant ${ index } ` ,
1798+ streaming : false ,
1799+ } ) ,
1800+ ) ,
1801+ turnItem ( 3 , {
1802+ type : "dynamic_tool" ,
1803+ toolName : "tool" ,
1804+ input : { } ,
1805+ output : { messageId : user } ,
1806+ } ) ,
1807+ ] ;
1808+ return { events, user, assistant } ;
1809+ } ;
1810+ const first = conversation ( "first" , 1 ) ;
1811+ const second = conversation ( "second" , 10 ) ;
1812+ for ( const event of [ ...first . events , ...second . events ] ) yield * store . apply ( event as never ) ;
1813+
1814+ const { statements, tracer } = traceSqlStatements ( ) ;
1815+ const indexed = yield * store
1816+ . getThreadRecords ( threadId , [ "messages" ] )
1817+ . pipe ( Effect . withTracer ( tracer ) ) ;
1818+ assert . deepEqual (
1819+ yield * queryPlan ( statements . find ( ( statement ) => statement . includes ( "MIN(ordinal) AS ordinal" ) ) ) ,
1820+ [
1821+ "SEARCH orchestration_v2_projection_turn_items USING INDEX orchestration_v2_projection_turn_items_message_ordinal_idx (thread_id=?)" ,
1822+ ] ,
1823+ ) ;
1824+ assert . deepEqual (
1825+ indexed . messages . map ( ( message ) => message . id ) ,
1826+ [ first . user , first . assistant , second . user , second . assistant ] ,
1827+ ) ;
1828+ // A narrow read (ids, runs or one role) recovers only its own messages' ordinals, through the
1829+ // same index; the statement it sends, rerun with its bindings, returns just those rows.
1830+ const narrowReads = [
1831+ {
1832+ filter : { messageIds : [ second . assistant , first . user ] } ,
1833+ loaded : [ first . user , second . assistant ] ,
1834+ } ,
1835+ { filter : { messageRunIds : [ RunId . make ( "run:first" ) ] } , loaded : [ first . user , first . assistant ] } ,
1836+ { filter : { messageRoles : [ "user" as const ] } , loaded : [ first . user , second . user ] } ,
1837+ ] ;
1838+ for ( const { filter, loaded } of narrowReads ) {
1839+ const narrow = traceSqlStatements ( ) ;
1840+ const narrowed = yield * store
1841+ . getThreadRecords ( threadId , [ "messages" ] , filter )
1842+ . pipe ( Effect . withTracer ( narrow . tracer ) ) ;
1843+ assert . deepEqual (
1844+ narrowed . messages . map ( ( message ) => message . id ) ,
1845+ loaded ,
1846+ ) ;
1847+ const bounded = narrow . statements . find ( ( statement ) =>
1848+ statement . includes ( "MIN(ordinal) AS ordinal" ) ,
1849+ ) ;
1850+ assert . include ( bounded , "IN (SELECT value FROM json_each(" ) ;
1851+ assert . deepEqual ( yield * queryPlan ( bounded ) , [
1852+ "SEARCH orchestration_v2_projection_turn_items USING INDEX orchestration_v2_projection_turn_items_message_ordinal_idx (thread_id=? AND <expr>=?)" ,
1853+ "LIST SUBQUERY 1" ,
1854+ "SCAN json_each VIRTUAL TABLE INDEX 1:" ,
1855+ ] ) ;
1856+ const rows = yield * sql . unsafe < { readonly message_id : string } > ( bounded ! , [
1857+ threadId ,
1858+ JSON . stringify ( loaded ) ,
1859+ ] ) ;
1860+ assert . sameMembers (
1861+ rows . map ( ( row ) => row . message_id ) ,
1862+ loaded ,
1863+ ) ;
1864+ }
1865+ yield * sql `DROP INDEX orchestration_v2_projection_turn_items_message_ordinal_idx` ;
1866+ assert . deepEqual (
1867+ ( yield * store . getThreadRecords ( threadId , [ "messages" ] ) ) . messages ,
1868+ indexed . messages ,
1869+ ) ;
1870+ } ) ;
1871+
1872+ // It drops the index, so it gets its own database.
1873+ it . effect ( "reads message ordinals through their index and orders messages as without it" , ( ) =>
1874+ messageOrdinalsReadThroughTheirIndex . pipe ( Effect . provide ( layerTest ) ) ,
1875+ ) ;
1876+
16851877const assertProjectScopedShells = Effect . fn ( "assertProjectScopedShells" ) ( function * (
16861878 suffix : string ,
16871879) {
0 commit comments