<mxfile host="app.diagrams.net" agent="architecture-deliverables" version="24.0.0">
  <diagram name="Core Data Model" id="18-data-model">
    <mxGraphModel dx="1400" dy="900" background="#ffffff" grid="1" gridSize="10" guides="1" tooltips="1" connect="1" arrows="1" fold="1" page="1" pageScale="1" pageWidth="1700" pageHeight="1200" math="0" shadow="0">
      <root>
        <mxCell id="0" />
        <mxCell id="1" parent="0" />
        <mxCell id="n.contact" value="&lt;b&gt;contact_link&lt;/b&gt;&lt;br&gt;&lt;font style=&quot;font-size:10px;color:#5b6b7d&quot;&gt;account_id  FK -&gt; account&lt;br&gt;contact_hash  PK&lt;br&gt;is_blocked&lt;br&gt;discovered_at&lt;/font&gt;" style="rounded=1;arcSize=4;whiteSpace=wrap;html=1;fillColor=#d5e8d4;strokeColor=#82b366;fontColor=#1e3a1e;fontSize=12;fontFamily=Segoe UI;verticalAlign=top;align=left;spacingLeft=12;spacingTop=7;spacingRight=10;" vertex="1" parent="1">
          <mxGeometry x="40" y="80" width="380" height="100" as="geometry" />
        </mxCell>
        <mxCell id="n.account" value="&lt;b&gt;account&lt;/b&gt;&lt;br&gt;&lt;font style=&quot;font-size:10px;color:#5b6b7d&quot;&gt;account_id  PK&lt;br&gt;phone_e164  UNIQUE&lt;br&gt;display_name&lt;br&gt;about_status&lt;br&gt;photo_blob_id&lt;br&gt;created_at&lt;/font&gt;" style="rounded=1;arcSize=4;whiteSpace=wrap;html=1;fillColor=#d5e8d4;strokeColor=#82b366;fontColor=#1e3a1e;fontSize=12;fontFamily=Segoe UI;verticalAlign=top;align=left;spacingLeft=12;spacingTop=7;spacingRight=10;" vertex="1" parent="1">
          <mxGeometry x="460" y="80" width="380" height="130" as="geometry" />
        </mxCell>
        <mxCell id="n.device" value="&lt;b&gt;device&lt;/b&gt;&lt;br&gt;&lt;font style=&quot;font-size:10px;color:#5b6b7d&quot;&gt;device_id  PK&lt;br&gt;account_id  FK -&gt; account&lt;br&gt;platform&lt;br&gt;is_primary&lt;br&gt;registration_id&lt;br&gt;last_seen_at&lt;/font&gt;" style="rounded=1;arcSize=4;whiteSpace=wrap;html=1;fillColor=#d5e8d4;strokeColor=#82b366;fontColor=#1e3a1e;fontSize=12;fontFamily=Segoe UI;verticalAlign=top;align=left;spacingLeft=12;spacingTop=7;spacingRight=10;" vertex="1" parent="1">
          <mxGeometry x="870" y="80" width="380" height="130" as="geometry" />
        </mxCell>
        <mxCell id="n.devkey" value="&lt;b&gt;device_key&lt;/b&gt;&lt;br&gt;&lt;font style=&quot;font-size:10px;color:#5b6b7d&quot;&gt;key_id  PK&lt;br&gt;device_id  FK -&gt; device&lt;br&gt;key_type  identity | signed | onetime&lt;br&gt;public_key&lt;br&gt;consumed_at&lt;/font&gt;" style="rounded=1;arcSize=4;whiteSpace=wrap;html=1;fillColor=#d5e8d4;strokeColor=#82b366;fontColor=#1e3a1e;fontSize=12;fontFamily=Segoe UI;verticalAlign=top;align=left;spacingLeft=12;spacingTop=7;spacingRight=10;" vertex="1" parent="1">
          <mxGeometry x="1280" y="80" width="380" height="120" as="geometry" />
        </mxCell>
        <mxCell id="n.status" value="&lt;b&gt;status_post&lt;/b&gt;&lt;br&gt;&lt;font style=&quot;font-size:10px;color:#5b6b7d&quot;&gt;status_id  PK&lt;br&gt;account_id  FK -&gt; account&lt;br&gt;blob_id&lt;br&gt;audience_rule&lt;br&gt;expires_at&lt;/font&gt;" style="rounded=1;arcSize=4;whiteSpace=wrap;html=1;fillColor=#d5e8d4;strokeColor=#82b366;fontColor=#1e3a1e;fontSize=12;fontFamily=Segoe UI;verticalAlign=top;align=left;spacingLeft=12;spacingTop=7;spacingRight=10;" vertex="1" parent="1">
          <mxGeometry x="40" y="340" width="380" height="120" as="geometry" />
        </mxCell>
        <mxCell id="n.member" value="&lt;b&gt;membership&lt;/b&gt;&lt;br&gt;&lt;font style=&quot;font-size:10px;color:#5b6b7d&quot;&gt;conversation_id  FK&lt;br&gt;account_id  FK -&gt; account&lt;br&gt;role  member | admin | owner&lt;br&gt;joined_at&lt;/font&gt;" style="rounded=1;arcSize=4;whiteSpace=wrap;html=1;fillColor=#d5e8d4;strokeColor=#82b366;fontColor=#1e3a1e;fontSize=12;fontFamily=Segoe UI;verticalAlign=top;align=left;spacingLeft=12;spacingTop=7;spacingRight=10;" vertex="1" parent="1">
          <mxGeometry x="460" y="340" width="380" height="100" as="geometry" />
        </mxCell>
        <mxCell id="n.inbox" value="&lt;b&gt;device_inbox&lt;/b&gt;&lt;br&gt;&lt;font style=&quot;font-size:10px;color:#5b6b7d&quot;&gt;device_id  FK -&gt; device&lt;br&gt;message_id  FK -&gt; message_envelope&lt;br&gt;ciphertext  BLOB&lt;br&gt;state&lt;br&gt;expires_at&lt;/font&gt;" style="rounded=1;arcSize=4;whiteSpace=wrap;html=1;fillColor=#d5e8d4;strokeColor=#82b366;fontColor=#1e3a1e;fontSize=12;fontFamily=Segoe UI;verticalAlign=top;align=left;spacingLeft=12;spacingTop=7;spacingRight=10;" vertex="1" parent="1">
          <mxGeometry x="870" y="340" width="380" height="120" as="geometry" />
        </mxCell>
        <mxCell id="n.receipt" value="&lt;b&gt;receipt&lt;/b&gt;&lt;br&gt;&lt;font style=&quot;font-size:10px;color:#5b6b7d&quot;&gt;message_id  FK&lt;br&gt;device_id  FK&lt;br&gt;state  delivered | read | played&lt;br&gt;recorded_at&lt;/font&gt;" style="rounded=1;arcSize=4;whiteSpace=wrap;html=1;fillColor=#d5e8d4;strokeColor=#82b366;fontColor=#1e3a1e;fontSize=12;fontFamily=Segoe UI;verticalAlign=top;align=left;spacingLeft=12;spacingTop=7;spacingRight=10;" vertex="1" parent="1">
          <mxGeometry x="1280" y="340" width="380" height="100" as="geometry" />
        </mxCell>
        <mxCell id="n.call" value="&lt;b&gt;call_record&lt;/b&gt;&lt;br&gt;&lt;font style=&quot;font-size:10px;color:#5b6b7d&quot;&gt;call_id  PK&lt;br&gt;conversation_id  FK&lt;br&gt;kind  voice | video | group&lt;br&gt;started_at&lt;br&gt;duration_s&lt;/font&gt;" style="rounded=1;arcSize=4;whiteSpace=wrap;html=1;fillColor=#d5e8d4;strokeColor=#82b366;fontColor=#1e3a1e;fontSize=12;fontFamily=Segoe UI;verticalAlign=top;align=left;spacingLeft=12;spacingTop=7;spacingRight=10;" vertex="1" parent="1">
          <mxGeometry x="40" y="600" width="380" height="120" as="geometry" />
        </mxCell>
        <mxCell id="n.conv" value="&lt;b&gt;conversation&lt;/b&gt;&lt;br&gt;&lt;font style=&quot;font-size:10px;color:#5b6b7d&quot;&gt;conversation_id  PK&lt;br&gt;kind  direct | group | broadcast&lt;br&gt;created_by  FK -&gt; account&lt;br&gt;created_at&lt;/font&gt;" style="rounded=1;arcSize=4;whiteSpace=wrap;html=1;fillColor=#d5e8d4;strokeColor=#82b366;fontColor=#1e3a1e;fontSize=12;fontFamily=Segoe UI;verticalAlign=top;align=left;spacingLeft=12;spacingTop=7;spacingRight=10;" vertex="1" parent="1">
          <mxGeometry x="460" y="600" width="380" height="100" as="geometry" />
        </mxCell>
        <mxCell id="n.msg" value="&lt;b&gt;message_envelope&lt;/b&gt;&lt;br&gt;&lt;font style=&quot;font-size:10px;color:#5b6b7d&quot;&gt;message_id  PK  server&lt;br&gt;client_msg_id  UNIQUE per sender&lt;br&gt;conversation_id  FK&lt;br&gt;sender_device_id  FK&lt;br&gt;created_at&lt;br&gt;expires_at&lt;/font&gt;" style="rounded=1;arcSize=4;whiteSpace=wrap;html=1;fillColor=#d5e8d4;strokeColor=#82b366;fontColor=#1e3a1e;fontSize=12;fontFamily=Segoe UI;verticalAlign=top;align=left;spacingLeft=12;spacingTop=7;spacingRight=10;" vertex="1" parent="1">
          <mxGeometry x="870" y="600" width="380" height="130" as="geometry" />
        </mxCell>
        <mxCell id="n.blob" value="&lt;b&gt;media_blob&lt;/b&gt;&lt;br&gt;&lt;font style=&quot;font-size:10px;color:#5b6b7d&quot;&gt;blob_id  PK&lt;br&gt;sha256_ciphertext&lt;br&gt;size_bytes&lt;br&gt;mime_type&lt;br&gt;expires_at&lt;/font&gt;" style="rounded=1;arcSize=4;whiteSpace=wrap;html=1;fillColor=#d5e8d4;strokeColor=#82b366;fontColor=#1e3a1e;fontSize=12;fontFamily=Segoe UI;verticalAlign=top;align=left;spacingLeft=12;spacingTop=7;spacingRight=10;" vertex="1" parent="1">
          <mxGeometry x="1280" y="600" width="380" height="120" as="geometry" />
        </mxCell>
        <mxCell id="n.gmeta" value="&lt;b&gt;group_metadata&lt;/b&gt;&lt;br&gt;&lt;font style=&quot;font-size:10px;color:#5b6b7d&quot;&gt;conversation_id  PK  FK&lt;br&gt;subject&lt;br&gt;description&lt;br&gt;icon_blob_id&lt;br&gt;only_admins_post&lt;/font&gt;" style="rounded=1;arcSize=4;whiteSpace=wrap;html=1;fillColor=#d5e8d4;strokeColor=#82b366;fontColor=#1e3a1e;fontSize=12;fontFamily=Segoe UI;verticalAlign=top;align=left;spacingLeft=12;spacingTop=7;spacingRight=10;" vertex="1" parent="1">
          <mxGeometry x="460" y="860" width="380" height="120" as="geometry" />
        </mxCell>
        <mxCell id="_r1" value="1 : N" style="edgeStyle=orthogonalEdgeStyle;rounded=1;html=1;endArrow=ERmany;endFill=0;startArrow=ERone;startFill=0;strokeWidth=1.4;strokeColor=#5a6570;fontSize=10;fontColor=#42536a;labelBackgroundColor=#ffffff;fontFamily=Segoe UI;exitX=0;exitY=0.5;exitDx=0;exitDy=0;entryX=1;entryY=0.5;entryDx=0;entryDy=0;" edge="1" parent="1" source="n.account" target="n.contact">
          <mxGeometry relative="1" as="geometry" />
        </mxCell>
        <mxCell id="_r2" value="1 : N" style="edgeStyle=orthogonalEdgeStyle;rounded=1;html=1;endArrow=ERmany;endFill=0;startArrow=ERone;startFill=0;strokeWidth=1.4;strokeColor=#5a6570;fontSize=10;fontColor=#42536a;labelBackgroundColor=#ffffff;fontFamily=Segoe UI;exitX=1;exitY=0.5;exitDx=0;exitDy=0;entryX=0;entryY=0.5;entryDx=0;entryDy=0;" edge="1" parent="1" source="n.account" target="n.device">
          <mxGeometry relative="1" as="geometry" />
        </mxCell>
        <mxCell id="_r3" value="1 : N" style="edgeStyle=orthogonalEdgeStyle;rounded=1;html=1;endArrow=ERmany;endFill=0;startArrow=ERone;startFill=0;strokeWidth=1.4;strokeColor=#5a6570;fontSize=10;fontColor=#42536a;labelBackgroundColor=#ffffff;fontFamily=Segoe UI;exitX=1;exitY=0.5;exitDx=0;exitDy=0;entryX=0;entryY=0.5;entryDx=0;entryDy=0;" edge="1" parent="1" source="n.device" target="n.devkey">
          <mxGeometry relative="1" as="geometry" />
        </mxCell>
        <mxCell id="_r4" value="1 : N" style="edgeStyle=orthogonalEdgeStyle;rounded=1;html=1;endArrow=ERmany;endFill=0;startArrow=ERone;startFill=0;strokeWidth=1.4;strokeColor=#5a6570;fontSize=10;fontColor=#42536a;labelBackgroundColor=#ffffff;fontFamily=Segoe UI;exitX=0.25;exitY=1;exitDx=0;exitDy=0;entryX=0.5;entryY=0;entryDx=0;entryDy=0;" edge="1" parent="1" source="n.account" target="n.status">
          <mxGeometry relative="1" as="geometry" />
        </mxCell>
        <mxCell id="_r5" value="1 : N" style="edgeStyle=orthogonalEdgeStyle;rounded=1;html=1;endArrow=ERmany;endFill=0;startArrow=ERone;startFill=0;strokeWidth=1.4;strokeColor=#5a6570;fontSize=10;fontColor=#42536a;labelBackgroundColor=#ffffff;fontFamily=Segoe UI;exitX=0.75;exitY=1;exitDx=0;exitDy=0;entryX=0.5;entryY=0;entryDx=0;entryDy=0;" edge="1" parent="1" source="n.account" target="n.member">
          <mxGeometry relative="1" as="geometry" />
        </mxCell>
        <mxCell id="_r6" value="1 : N" style="edgeStyle=orthogonalEdgeStyle;rounded=1;html=1;endArrow=ERmany;endFill=0;startArrow=ERone;startFill=0;strokeWidth=1.4;strokeColor=#5a6570;fontSize=10;fontColor=#42536a;labelBackgroundColor=#ffffff;fontFamily=Segoe UI;exitX=0.5;exitY=0;exitDx=0;exitDy=0;entryX=0.5;entryY=1;entryDx=0;entryDy=0;" edge="1" parent="1" source="n.conv" target="n.member">
          <mxGeometry relative="1" as="geometry" />
        </mxCell>
        <mxCell id="_r7" value="1 : N" style="edgeStyle=orthogonalEdgeStyle;rounded=1;html=1;endArrow=ERmany;endFill=0;startArrow=ERone;startFill=0;strokeWidth=1.4;strokeColor=#5a6570;fontSize=10;fontColor=#42536a;labelBackgroundColor=#ffffff;fontFamily=Segoe UI;exitX=1;exitY=0.5;exitDx=0;exitDy=0;entryX=0;entryY=0.5;entryDx=0;entryDy=0;" edge="1" parent="1" source="n.conv" target="n.msg">
          <mxGeometry relative="1" as="geometry" />
        </mxCell>
        <mxCell id="_r8" value="1 : N" style="edgeStyle=orthogonalEdgeStyle;rounded=1;html=1;endArrow=ERmany;endFill=0;startArrow=ERone;startFill=0;strokeWidth=1.4;strokeColor=#5a6570;fontSize=10;fontColor=#42536a;labelBackgroundColor=#ffffff;fontFamily=Segoe UI;exitX=0;exitY=0.5;exitDx=0;exitDy=0;entryX=1;entryY=0.5;entryDx=0;entryDy=0;" edge="1" parent="1" source="n.conv" target="n.call">
          <mxGeometry relative="1" as="geometry" />
        </mxCell>
        <mxCell id="_r9" value="1 : 1" style="edgeStyle=orthogonalEdgeStyle;rounded=1;html=1;endArrow=ERmany;endFill=0;startArrow=ERone;startFill=0;strokeWidth=1.4;strokeColor=#8c8c8c;dashed=1;fontSize=10;fontColor=#42536a;labelBackgroundColor=#ffffff;fontFamily=Segoe UI;exitX=0.5;exitY=1;exitDx=0;exitDy=0;entryX=0.5;entryY=0;entryDx=0;entryDy=0;" edge="1" parent="1" source="n.conv" target="n.gmeta">
          <mxGeometry relative="1" as="geometry" />
        </mxCell>
        <mxCell id="_r10" value="1 : N" style="edgeStyle=orthogonalEdgeStyle;rounded=1;html=1;endArrow=ERmany;endFill=0;startArrow=ERone;startFill=0;strokeWidth=1.4;strokeColor=#5a6570;fontSize=10;fontColor=#42536a;labelBackgroundColor=#ffffff;fontFamily=Segoe UI;exitX=0.5;exitY=0;exitDx=0;exitDy=0;entryX=0.5;entryY=1;entryDx=0;entryDy=0;" edge="1" parent="1" source="n.msg" target="n.inbox">
          <mxGeometry relative="1" as="geometry" />
        </mxCell>
        <mxCell id="_r11" value="1 : N" style="edgeStyle=orthogonalEdgeStyle;rounded=1;html=1;endArrow=ERmany;endFill=0;startArrow=ERone;startFill=0;strokeWidth=1.4;strokeColor=#5a6570;fontSize=10;fontColor=#42536a;labelBackgroundColor=#ffffff;fontFamily=Segoe UI;exitX=0.5;exitY=1;exitDx=0;exitDy=0;entryX=0.5;entryY=0;entryDx=0;entryDy=0;" edge="1" parent="1" source="n.device" target="n.inbox">
          <mxGeometry relative="1" as="geometry" />
        </mxCell>
        <mxCell id="_r12" value="1 : N" style="edgeStyle=orthogonalEdgeStyle;rounded=1;html=1;endArrow=ERmany;endFill=0;startArrow=ERone;startFill=0;strokeWidth=1.4;strokeColor=#5a6570;fontSize=10;fontColor=#42536a;labelBackgroundColor=#ffffff;fontFamily=Segoe UI;exitX=1;exitY=0.5;exitDx=0;exitDy=0;entryX=0;entryY=0.5;entryDx=0;entryDy=0;" edge="1" parent="1" source="n.inbox" target="n.receipt">
          <mxGeometry relative="1" as="geometry" />
        </mxCell>
        <mxCell id="_r13" value="0 : 1" style="edgeStyle=orthogonalEdgeStyle;rounded=1;html=1;endArrow=ERmany;endFill=0;startArrow=ERone;startFill=0;strokeWidth=1.4;strokeColor=#8c8c8c;dashed=1;fontSize=10;fontColor=#42536a;labelBackgroundColor=#ffffff;fontFamily=Segoe UI;exitX=1;exitY=0.5;exitDx=0;exitDy=0;entryX=0;entryY=0.5;entryDx=0;entryDy=0;" edge="1" parent="1" source="n.msg" target="n.blob">
          <mxGeometry relative="1" as="geometry" />
        </mxCell>
        <mxCell id="title" value="Core Data Model" style="text;html=1;strokeColor=none;fillColor=none;align=left;verticalAlign=middle;whiteSpace=wrap;fontSize=20;fontStyle=1;fontColor=#16202c;fontFamily=Segoe UI;" vertex="1" parent="1">
          <mxGeometry x="40" y="20" width="1610" height="30" as="geometry" />
        </mxCell>
        <mxCell id="rule" value="" style="line;strokeWidth=1;html=1;strokeColor=#c9d3de;" vertex="1" parent="1">
          <mxGeometry x="40" y="50" width="1610" height="10" as="geometry" />
        </mxCell>
        <mxCell id="note" value="message_envelope has no body column. Content exists only as ciphertext in device_inbox, until acknowledged." style="text;html=1;strokeColor=none;fillColor=none;align=left;verticalAlign=middle;whiteSpace=wrap;fontSize=10;fontColor=#8a97a5;fontFamily=Segoe UI;" vertex="1" parent="1">
          <mxGeometry x="40" y="1130" width="1610" height="20" as="geometry" />
        </mxCell>
        <mxCell id="foot-rule" value="" style="line;strokeWidth=1;html=1;strokeColor=#e3e9ef;" vertex="1" parent="1">
          <mxGeometry x="40" y="1150" width="1610" height="10" as="geometry" />
        </mxCell>
        <mxCell id="foot" value="v 1.0   ·   owner Platform Architecture   ·   date 2026-08" style="text;html=1;strokeColor=none;fillColor=none;align=left;verticalAlign=middle;whiteSpace=wrap;fontSize=9;fontColor=#9aa7b4;fontFamily=Segoe UI;" vertex="1" parent="1">
          <mxGeometry x="40" y="1170" width="1610" height="10" as="geometry" />
        </mxCell>
      </root>
    </mxGraphModel>
  </diagram>
</mxfile>
