# New Dynamo Tables after D1 Phaseout

This document is a snapshot of the current table and index schemas for the three new tables introduced as part of the D1 phaseout. It is accurate as of 6/5/2023. It is distilled from the JSON table descriptions in https://github.com/web3-storage/w3infra/blob/main/upload-api/tables/index.js - that document should be consulted in the future to get the most accurate and up-to-date schemas.

I'd love thoughts and comments on this design, especially ideas for queries that we will need to be able to run soon - I'd like to avoid adding new indices too often - ancipating them as well as we can now is likely to save us time and energy in the future.

## Tables

We introduce the following DynamoDB tables in https://github.com/web3-storage/w3infra/pull/194

Some of the listed indices and queries anticipate future work on metrics, blocking and general administrative tools and are not yet in use. Queries that are actively in use by our systems are distiguished below.

### Delegations

#### Fields

| Name | Type | Indices |
| -------- | -------- | -------- |
| cause | string | |
| link | string | primary |
| audience | string | audience |
| issuer | string | |
| expiration | number | |
| insertedAt | string | |
| updatedAt | string | |

#### Indices

| Name | Key | Fields | Comment |
| -------- | -------- | -------- | ------- |
| primary | link | * | delegation CID (`link`) used as partition key, no sort key |
| audience | audience | link | find all `link`s (ie, delegation CIDs) with a particular audience |

#### Queries

| Index | In Use? | Description |
|------- |------- |--------- |
| audience | yes | find all `link`s (ie, delegation CIDs) with a particular audience|

### Subscriptions

#### Fields

| Name | Type | Indices |
| -------- | -------- | -------- |
| subscription | string | primary |
| provider | string | primary, provider |
| cause | string | |
| customer | string | customer |
| insertedAt | string | |
| updatedAt | string | |

#### Indices

| Name | Key | Fields | Comment |
| -------- | -------- | -------- | ------- |
| primary | subscription:provider | * | subscription used as partition key, provider used as sort key. providers should design subscription key value to give them appropriate uniqueness semantics |
| customer | customer:provider | cause, subscription | |
| provider | provider | customer | |

#### Queries

| Index | In Use? | Description |
|------- |---------|--------- |
| customer | yes | find subscriptions for a given customer and provider|
| provider | no | find customers of a given provider |
| primary | no | find the customer of a given subscription |
| customer | no | find subscriptions of a given customer |

### Consumers

#### Fields

| Name | Type | Indices |
| -------- | -------- | -------- |
| subscription | string | primary |
| provider | string | primary, provider |
| cause | string | |
| consumer | string | consumer |
| insertedAt | string | |
| updatedAt | string | |

#### Indices

| Name | Key | Fields | Comment |
| -------- | -------- | -------- | ------- |
| primary | subscription:provider | * | subscription used as partition key, provider used as sort key. providers should design subscription key value to give them appropriate uniqueness semantics |
| consumer | consumer | provider, subscription | |
| provider | provider | consumer | |

#### Queries

| Index | In Use? | Description |
|------- |---------|--------- |
| consumer | yes | find all providers for a given consumer |
| provider | no | find all consumers for a given provider |
| consumer | no | find all subscriptions for a given consumer |

## Appendix

### Table specifications in JavaScript

const delegationTableProps = {
  fields: {
    cause: 'string',      // `baf...x`(CID of the invocation)
    link: 'string',       // `baf...x` (CID of the delegation)
    audience: 'string',   // `did:web:service`
    issuer: 'string',     // `did:key:agent`
    expiration: 'number', // `9256939505` (unix timestamp in seconds)
    insertedAt: 'string', // `2022-12-24T...`
    updatedAt: 'string',  // `2022-12-24T...`
  },
  primaryIndex: { partitionKey: 'link' },
  globalIndexes: {
    audience: { partitionKey: 'audience', projection: ['link'] }
  }
}

// queries: 
// 1. find all `link`s (ie, delegation CIDs) with a particular audience

const subscriptionTableProps = {
  fields: {
    cause: 'string',        // `baf...x` (CID of invocation that created this subscription)
    provider: 'string',     // `did:web:service` (DID of the provider, e.g. a storage provider)
    customer: 'string',     // `did:mailto:agent` (DID of the user account)
    subscription: 'string', // string (arbitrary string associated with this subscription)
    insertedAt: 'string',   // `2022-12-24T...`
    updatedAt: 'string',    // `2022-12-24T...`
  },
  primaryIndex: { partitionKey: 'subscription', sortKey: 'provider' },
  globalIndexes: {
    customer: { partitionKey: 'customer', sortKey: 'provider', projection: ['cause', 'subscription'] },
    // TODO: Should we keep this index? Partitioning by provider is basically useless and might not be much faster than a table scan.
    provider: { partitionKey: 'provider', projection: ['customer'] }
  }
}

// queries:
// 1. find subscriptions for a given customer and provider
// 2. (PROVISIONAL) find customers of a given provider
// 3. (PROVISIONAL) find the customer of a given subscription
// 4. (PROVISIONAL) find subscriptions of a given customer

const consumerTableProps = {
  fields: {
    cause: 'string',        // `baf...x` (CID of invocation that created this consumer record)
    consumer: 'string',     // `did:key:space` (DID of the actor that is consuming the provider, e.g. a space DID)
    provider: 'string',     // `did:web:service` (DID of the provider, e.g. a storage provider)
    subscription: 'string', // string (arbitrary string associated with this subscription)
    insertedAt: 'string',   // `2022-12-24T...`
    updatedAt: 'string',    // `2022-12-24T...`
  },
  primaryIndex: { partitionKey: 'subscription', sortKey: 'provider' },
  globalIndexes: {
    consumer: { partitionKey: 'consumer', projection: ['provider', 'subscription']},
    // Should we keep this index? Partitioning by provider is basically useless and might not be much faster than a table scan.
    provider: { partitionKey: 'provider', projection: ['consumer'] }
  }
}

// queries
// 1. find all `provider`s for a given `consumer`
// 2. (PROVISIONAL) find all `consumer`s for a given provider 
// 3. (PROVISIONAL) find all `subscription`s for a given `consumer`