====== pears.IQXNetPasskeys ======
<WRAP center round info>
Generated schema reference. Regenerate this page from the SQL unload; keep hand-maintained business notes in the narrative namespace.
</WRAP>

===== Description =====
Stores WebAuthn/FIDO2 passkey credentials registered by users for passwordless authentication. Each row represents a single authenticator device linked to a user. The "Counter" is incremented on every authentication assertion and checked server-side to prevent replay attacks. "BackedUp" indicates whether the credential is a synced/multi-device passkey (eligible for cloud backup) as opposed to a hardware-bound single-device key.

===== Columns =====
^ Column ^ Type ^ Null ^ Default ^ Comment ^
| **IQXNetPasskeysID** | char(100) | NOT NULL |  | PK — base64URL-encoded credential ID returned by the authenticator at registration |
| IQXNetUserID | char(20) | NULL |  | FK → IQXNetUser |
| WebAuthnUserID | long varchar | NOT NULL |  | base64 user handle generated at registration, required by the WebAuthn spec |
| CredPublicKey | binary(500) | NULL |  | BINARY(500) — the public key from the authenticator, used to verify future assertions |
| DeviceType | char(32) | NULL |  | single-device or multi-device |
| Transports | char(100) | NULL |  | Comma-separated list of supported transports (e.g. internal,hybrid) |
| CreatedAt | timestamp | NULL |  | When the passkey was registered |
| LastUsed | timestamp | NULL |  | When it was last used to authenticate |
| OSName | char(50) | NULL |  | OS of the device that registered the key |
| Name | char(50) | NULL |  | Friendly name given to the passkey |
| Counter | integer | NULL |  | Signature counter — increments on each use (replay attack protection) |
| BackedUp | integer | NULL |  | 1 if the passkey is backed up (synced via cloud) |

===== Primary Key =====
  * IQXNetPasskeysID

===== Foreign Keys =====
^ Constraint ^ Columns ^ References ^ Delete/update action ^
| IQXNetUser | IQXNetUserID | [[database:tables:pears_iqxnetuser|pears.IQXNetUser (IQXNetUserID)]] | ON DELETE CASCADE |

===== Referenced By =====
  * No incoming foreign keys found.

===== Indexes =====
^ Name ^ Type ^ Columns ^ Detail ^
| IQXNetPasskeysWebAuthnUserID | Unique index | WebAuthnUserID |  |

===== Triggers =====
  * No triggers found.

===== Original SQL =====
<code sql>
-- IQX database structure split by table
-- Source: IQXDatabaseStructure - with comments.sql
-- Table: "pears"."IQXNetPasskeys"
-- Table comment: Stores WebAuthn/FIDO2 passkey credentials registered by users for passwordless authentication. Each row represents a single authenticator device linked to a user. The "Counter" is incremented on every authentication assertion and checked server-side to prevent replay attacks. "BackedUp" indicates whether the credential is a synced/multi-device passkey (eligible for cloud backup) as opposed to a hardware-bound single-device key.
-- Statement count: 16

CREATE TABLE "pears"."IQXNetPasskeys" (
    "IQXNetPasskeysID"               char(100) NOT NULL
   ,"IQXNetUserID"                   char(20) NULL
   ,"WebAuthnUserID"                 long varchar NOT NULL
   ,"CredPublicKey"                  binary(500) NULL
   ,"DeviceType"                     char(32) NULL
   ,"Transports"                     char(100) NULL
   ,"CreatedAt"                      timestamp NULL
   ,"LastUsed"                       timestamp NULL
   ,"OSName"                         char(50) NULL
   ,"Name"                           char(50) NULL
   ,"Counter"                        integer NULL
   ,"BackedUp"                       integer NULL
   ,PRIMARY KEY ("IQXNetPasskeysID" ASC) 
)
go


COMMENT ON COLUMN "pears"."IQXNetPasskeys"."IQXNetPasskeysID" IS 
	'PK — base64URL-encoded credential ID returned by the authenticator at registration'
go


COMMENT ON COLUMN "pears"."IQXNetPasskeys"."IQXNetUserID" IS 
	'FK → IQXNetUser'
go


COMMENT ON COLUMN "pears"."IQXNetPasskeys"."WebAuthnUserID" IS 
	'base64 user handle generated at registration, required by the WebAuthn spec'
go


COMMENT ON COLUMN "pears"."IQXNetPasskeys"."CredPublicKey" IS 
	'BINARY(500) — the public key from the authenticator, used to verify future assertions'
go


COMMENT ON COLUMN "pears"."IQXNetPasskeys"."DeviceType" IS 
	'single-device or multi-device'
go


COMMENT ON COLUMN "pears"."IQXNetPasskeys"."Transports" IS 
	'Comma-separated list of supported transports (e.g. internal,hybrid)'
go


COMMENT ON COLUMN "pears"."IQXNetPasskeys"."CreatedAt" IS 
	'When the passkey was registered'
go


COMMENT ON COLUMN "pears"."IQXNetPasskeys"."LastUsed" IS 
	'When it was last used to authenticate'
go


COMMENT ON COLUMN "pears"."IQXNetPasskeys"."OSName" IS 
	'OS of the device that registered the key'
go


COMMENT ON COLUMN "pears"."IQXNetPasskeys"."Name" IS 
	'Friendly name given to the passkey'
go


COMMENT ON COLUMN "pears"."IQXNetPasskeys"."Counter" IS 
	'Signature counter — increments on each use (replay attack protection)'
go


COMMENT ON COLUMN "pears"."IQXNetPasskeys"."BackedUp" IS 
	'1 if the passkey is backed up (synced via cloud)'
go


COMMENT ON TABLE "pears"."IQXNetPasskeys" IS 
	'Stores WebAuthn/FIDO2 passkey credentials registered by users for passwordless authentication. Each row represents a single authenticator device linked to a user. The "Counter" is incremented on every authentication assertion and checked server-side to prevent replay attacks. "BackedUp" indicates whether the credential is a synced/multi-device passkey (eligible for cloud backup) as opposed to a hardware-bound single-device key.'
go


ALTER TABLE "pears"."IQXNetPasskeys"
    ADD FOREIGN KEY "IQXNetUser" ("IQXNetUserID" ASC)
    REFERENCES "pears"."IQXNetUser" ("IQXNetUserID")
    ON DELETE CASCADE
go


CREATE UNIQUE INDEX "IQXNetPasskeysWebAuthnUserID" ON "pears"."IQXNetPasskeys"
    ( "WebAuthnUserID" )
go
</code>